我对如何读取存储的proc OUTPUT参数有问题 [英] I have a problem with how to read a stored proc OUTPUT parameter

问题描述

我有一个表paymentmaster.我做了一个存储过程，我在其中检查了paymentmaster表是否有返回月余额字段浮点值的数据，否则返回0.monthlybalnce将保存在C#代码的变量中.提供带有存储过程的完整C#源代码.
C#代码

I have a table paymentmaster.I have made a stored proc in which I have check if paymentmaster table have data it returns float value of monthly balance field otherwise return 0.monthlybalnce will be hold in a variable of C# code.I am giving my full C# source code with stored proc.
C# Code

try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("monthlyFees", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.CommandText = "MonthlyFees";

                SqlParameter inparm = cmd.Parameters.Add("@account", SqlDbType.VarChar);
                inparm.Direction = ParameterDirection.Input;
                inparm.Value = cmbAccount.Text;
             

                SqlParameter inparm1 = cmd.Parameters.Add("@class", SqlDbType.VarChar);
                inparm.Direction = ParameterDirection.Input;
                inparm.Value = cmbClass.Text;

                SqlParameter inparm2 = cmd.Parameters.Add("@session", SqlDbType.VarChar);
                inparm.Direction = ParameterDirection.Input;
                inparm.Value = txtSession.Text; 

                SqlParameter ouparm = cmd.Parameters.Add("@a", SqlDbType.Float);
                ouparm.Direction = ParameterDirection.Output;

                //SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int);
               // retval.Direction = ParameterDirection.ReturnValue;
                SqlDataReader rdr = cmd.ExecuteReader();

                rdr.Read();
                

                    MonthlyFees = float.Parse(cmd.Parameters["@a"].Value.ToString());
                
                   
                

                //Console.WriteLine("The output parameter value is {0}", cmd.Parameters["@ordercount"].Value);

                // Console.WriteLine("The return value is {0}", cmd.Parameters["return_value"].Value);
            }
            catch (SqlException ex)
            {
                
            }
            finally
            {
                 
                conn.Close();
            }
            return MonthlyFees;

存储过程

Stored Proc

CREATE procedure [dbo].[MonthlyFees]
@a float  OUTPUT,
@account varchar(10)='''',
@session varchar(50)='''',
@class varchar(50)=''''
As         
if exists(select *from paymentmaster) 
 SET @a=     (select  top 1  monthlyBalance  
      from paymentmaster where accountnumber =@account and class =@class
      and session=@session order by id desc )
       

       else
    
         set @a=0.0
GO

此C#代码将产生错误

``输入字符串的格式不正确.''

这个问题的所有答案都没有帮助我，错误是相同的.我仍然有问题.请暂停！

This C# code will generate error

''Input string was not in a correct format.''

All the answers of this question didn''t help me the error is same.I have still problem.Please halp!

推荐答案

您应该始终使用TryParse.您应该使用调试器检查返回的值以查看问题所在.您应该始终告诉我们代码中的哪一行会产生错误.

You should always use TryParse. You should use the debugger to check the value that''s been returned to see what the problem is. You should always tell us what line in your code creates the error.

没有什么比发生错误情况更复杂的了，如果您没有编写任何行来从存储过程中获取值，那么会发生什么呢?您试图获取值的情况是什么??????????

有可能吗?

现在尝试执行以下操作，您将获得理想的结果.

第一件事-将输入参数初始化为空，例如@account varchar(10)=null

第二件事-U从存储过程中不返回任何内容.您只是在设置值.

第三件事-我们可以通过3种不同的方式从存储过程中返回值...
1.使用Select语句
例如

There is nothing like error situation, if you have not written any line to get the values from stored procedure, then in what circumstances you are trying to fetch the value??????????

Is is possible??

Now try following things and u will get desired result.

1st thing - Initialize the input parameters to null e.g. @account varchar(10)=null

2nd thing - U are are returning nothing from the stored procedure. You are just setting the values.

3rd thing - We can return values from stored procedure in 3 different ways...
1. Using Select statement
e.g.

select @a=     (select  top 1  monthlyBalance  
      from paymentmaster where accountnumber =@account and class =@class
      and session=@session order by id desc )
       
       else
    
         select @a=0.0

2.使用输出参数，例如-

2. Using output parameters e.g.-

CREATE procedure [dbo].[MonthlyFees]
@a float  OUTPUT,
@account varchar(10)=null,
@session varchar(50)=null,
@class varchar(50)=null,
@ouputvalue INT OUTPUT

As         
if exists(select *from paymentmaster) 
 select @ouputvalue =     (select  top 1  monthlyBalance  
      from paymentmaster where accountnumber =@account and class =@class
      and session=@session order by id desc )
       

       else
    
         select @ouputvalue =0.0

3.使用return
例如

3. using return
e.g.

set @a=     (select  top 1  monthlyBalance  
      from paymentmaster where accountnumber =@account and class =@class
      and session=@session order by id desc )
return @a
       else
    
         return 0.0

从中使用任何一种方法，您将获得该值..但是，对于每种方法，c#的监听代码都略有不同

快乐的编码....

Use any one method from this and you will get the value.. but listen there is little bit different c# code for c# every method

happy coding....

如果要使用datareader，必须先关闭它，然后再获取输出值，并使用rdr.GetString(0)来获取其值.
要从命令对象中检索输出参数，您必须使用ExecuteNonQuery而不是ExecuteReader.为此，
替换
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
与
cmd.ExecuteNonQuery();

它将起作用.

If you want to use datareader,it must be closed before getting the output value and use rdr.GetString(0) to get its value.
To retrive output parameter from command object you have to use ExecuteNonQuery not ExecuteReader. To do so,
Replace
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
with
cmd.ExecuteNonQuery();

It will work.

这篇关于我对如何读取存储的proc OUTPUT参数有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！