using stored proc with output parameter in ado.net with c# not displaying any value RSS

• • Reply
  

  srm2009

  Member

  89 Points

  184 Posts

  using stored proc with output parameter in ado.net with c# not displaying any value

  Dec 19, 2011 05:24 AM|LINK

  I am working with winforms and created proc with output parameter which is displaying result as zero for all data

  coming from student balance table.balance is coming 0.Stored proc in sql is running perfect.in ado.net code

  value is displayed correctly or it will give error.

  My procedure and code is as below:

  alter proc [CalculateBalancebyDate]
  (@studentid as int,@examdate as varchar(20), @fees numeric(18,0) output)
  as
  begin
  
  DECLARE cursor1 CURSOR  FOR
  SELECT distinct(Examid) FROM  dbo.CashRecptTbl
  where stdid=@studentid  and  CONVERT(VARCHAR(20),ExamDate,105)
  =CONVERT(VARCHAR(20),@examdate,105)
  
      DECLARE @ExamID varchar(10)
      --Declare @fees as numeric(18,0)
      set @fees=0
      print @fees
      declare @feespaid as numeric(18,0)
      SELECT @fees=Promotionfees FROM CashRecptTbl
       where stdid=@studentid and PromotionFees <>0 and  CONVERT(VARCHAR(20),Examdate,105)
  = CONVERT(VARCHAR(20),@examdate,105)
      if (@fees>0)
          begin
              print'Got Total From Cash'
              set @fees=@fees
              print @fees
          end
      else
          begin
              print'Got Total from Cheque'
              SELECT @fees=promotionfees FROM ChequeRecptTbl
              where stdid=@studentid and promotionfees<>0 and  CONVERT(VARCHAR(20),examdate,105)
  = CONVERT(VARCHAR(20),@examdate,105)
  
              print @fees
          end
      
      
      declare @cashvalues as numeric(18,0)
      declare @chequevalues as numeric(18,0)
                      OPEN cursor1;
                      Fetch next from cursor1 into @ExamID
                      
                      WHILE (@@FETCH_STATUS = 0)
                      BEGIN
                      print @ExamID
                          set @cashvalues=0
                          set @chequevalues=0
                          select @cashvalues = sum(totalfees) from CashRecptTbl
                           where ExamID=@ExamID and stdid=@studentid and  CONVERT(VARCHAR(20),ExamDate ,105)
  = CONVERT(VARCHAR(20),@examdate,105)
  
                          if (@cashvalues>0)
                              begin
                                  print'cash value greater than'
                                  set @cashvalues=@cashvalues
                                  print @cashvalues
                              end
                          else
                              begin
                                  print'cash value null'
                                  set @cashvalues=0
                                  print @cashvalues
                              end
                          select @chequevalues = sum(totalfees) from ChequeRecptTbl
                           where ExamID=@ExamID and stdid=@studentid and  CONVERT(VARCHAR(20),ExamDate ,105)
  =CONVERT(VARCHAR(20),@examdate,105)
                          
                          
                          if (@chequevalues>0)
                              begin
                                  print'cheque value greter then'
                                  set @chequevalues=@chequevalues
                                  print @chequevalues
                              end
                          else
                              begin
                                  print'cheque value null'
                                  set @chequevalues=0
                                  print @chequevalues
                              end
                          
                          set @fees= @fees-(@cashvalues+@chequevalues)
                          
                              FETCH next FROM cursor1 INTO  @ExamID
                              
                      END
                      select @fees
                          CLOSE cursor1
                          DEALLOCATE cursor1
                          
                          end
                          GO
                          ---execute proc
                          declare @bal numeric(18,0)
                          exec CalculateBalancebyDate 8,'19-12-2011',@fees=@bal output
                  PRINT 'balance is = ' + CAST(@bal AS varCHAR(20))
  
  output is balance is 2000 in sql

  ---code in winform for ado.net to call above stored proc

  
      ConnectionStringSettings mysettings = ConfigurationManager.ConnectionStrings["DataConnectionString1"];
              SqlConnection mycon = new SqlConnection(mysettings.ConnectionString);
              SqlCommand mycomm = mycon.CreateCommand();
              mycomm.CommandType = CommandType.StoredProcedure;
  
              mycomm.CommandText = "dbo.CalculateBalancebyDate";
  
              SqlParameter stdparam = new SqlParameter();
              stdparam.ParameterName = "@studentid";
              stdparam.Value = Convert.ToInt32(Receiptview.studid);
              stdparam.Direction = ParameterDirection.Input;
              mycomm.Parameters.Add(stdparam);
  
              SqlParameter examdtparam = new SqlParameter();
              examdtparam.ParameterName = "@examdate";
              examdtparam.Value = PmntRvwDatepicker.Value.ToString ();--datetimepicker has date as mm/dd/yyy in sql it is stored as --yyyy/mm/dd
              examdtparam.Direction = ParameterDirection.Input;
              mycomm.Parameters.Add(examdtparam);
  
              //fees coming 0 for all, error
              SqlParameter balanceparam = new SqlParameter();
              balanceparam.ParameterName = "@fees";
              balanceparam.DbType = DbType.Int32;
              balanceparam.Direction = ParameterDirection.Output;
              mycomm.Parameters.Add(balanceparam);
  //error in this line :object not set to instance of an object          
   int stroutput =(int) balanceparam.Value ;
  
              SqlDataAdapter adap = new SqlDataAdapter();
              DataSet ds = new DataSet();
              adap.SelectCommand = mycomm;
              adap.Fill(ds, "CashRecptTbl");
             // balnceamtlbl.Text = "Balance: " + balanceparam.Value;
              balnceamtlbl.Text = "Balance: " + stroutput .ToString ();

  Is the date format is not correct or any other issue can not understand.

• • Reply
  

  Shatrughna.k...

  Participant

  778 Points

  174 Posts

  Re: using stored proc with output parameter in ado.net with c# not displaying any value

  Dec 19, 2011 05:37 AM|LINK

  Try this:
  
  

  After adap.Fill(ds, "CashRecptTbl");
  Use below line

  int Val = (Int)adap.SelectCommand.Parameters["balanceparam"].Value

  • Edited by Shatrughna.kumar on Dec 19, 2011 05:37 AM
• • Reply
  

  Chen Yu - MS...

  All-Star

  21581 Points

  2493 Posts

  Microsoft

  Re: using stored proc with output parameter in ado.net with c# not displaying any value

  Dec 22, 2011 08:00 AM|LINK

  srm2009

   int stroutput =(int) balanceparam.Value ;
  

  Hi srm2009,

  From this sentence, you want to get the balanceparam's value to stroutput before executing the stored procedure, is it correct? Balanceparam is an output parameter, you need to execute the stored porcedure and then it will return a values.

  You could follow this article to modify your code : http://msdn.microsoft.com/en-us/library/59x02y99(v=VS.100).aspx

  Thanks.

   

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Marked as answer by Chen Yu - MSFT on Dec 28, 2011 09:32 AM