please quick reply!
i have a problem that when a record is in the database table then it returns the monthlyBalance float value otherwise return 0.0 float value. I am using stored proc for this.please help

C# Code

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;

                SqlDataReader rdr = cmd.ExecuteReader();
                rdr.Read();
                MonthlyFees = float.Parse(cmd.Parameters["@a"].Value.ToString());              
            }
            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

Edited 6 Years Ago by __avd: Added [code] tags. Encase your code in: [code] and [/code] tags.

Do not initialize input parameters within stored-procedure definition.

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

You should have to read your code carefully,

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

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

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

SqlParameter ouparm = cmd.Parameters.Add("@a", SqlDbType.Float);
ouparm.Direction = ParameterDirection.Output;
// You can use ExecureNonQuery method
SqlDataReader rdr = cmd.ExecuteReader();

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

Edited 6 Years Ago by __avd: n/a

This article has been dead for over six months. Start a new discussion instead.