Click here to Skip to main content
14,303,913 members
Rate this:
Please Sign up or sign in to vote.
I have created a sql stored procedure which returns one value. It runs
successfully from query analyser.

I have created Store procedure given below
CREATE PROCEDURE Realto
@MM		varchar(50),
@YY		varchar(50),
@amt     int  output
  	
AS
Declare  @alto int
Declare  @realto  int

if(select   sum(intAltoAmt) from tblJoiningPayment where strMonth<=  @MM  and strYear<=  @YY)=0
  Begin
    Set @alto=0
  end
else
  Begin             
    select   @alto=  sum(intAltoAmt) from tblJoiningPayment where strMonth<= @MM  and strYear<=  @YY
  end

if(select sum(intPayAmount) from tblAltoPayment where strMonth<=  @MM  and strYear<=  @YY)=0
  Begin
    Set @realto=0
  end
else                
  Begin		
    select   @realto=  sum(intPayAmount) from tblAltoPayment where strMonth<= @MM  and strYear<=  @YY
  End
Set @amt =@alto- @realto
GO

VB.net code
Dim cmd1 As SqlCommand = New SqlCommand("ReAlto", Con)
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.Add("@MM", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & MM & "'"
cmd1.Parameters.Add("@YY", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & YY & "'"
cmd1.Parameters.Add("@amt", SqlDbType.Int)
cmd1.Parameters("@amt").Direction = ParameterDirection.Output)
Con.Open()
cmd1.ExecuteNonQuery()
Con.Close()

Label38.Text = cmd1.Parameters("@amt").Value.ToString()


Am I right or not? Because this is returning zero value.
But I run in query analyzer it returns some value. What's the problem?
Please help me.

Thanks
Mahendra Kumar Das

I want to run it from my vb.net code
Posted
Updated 13-Dec-10 23:37pm
v5
Comments
Toniyo Jackson 14-Dec-10 5:23am
   
Always put your code inside code block.
JF2015 14-Dec-10 5:37am
   
Edited to improve code readability.
senguptaamlan 14-Dec-10 8:08am
   
if you got the solution from any of the provided solutions please mark that as answered
Rate this:
Please Sign up or sign in to vote.

Solution 3

Thank you for your question.

Try
Dim cmd1 As SqlCommand = New SqlCommand("ReAlto", Con)
            cmd1.CommandType = CommandType.StoredProcedure
            cmd1.Parameters.Add("@MM", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & MM & "'"
            cmd1.Parameters.Add("@YY", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & YY & "'"
            cmd1.Parameters.Add("@amt", SqlDbType.Int)
            cmd1.Parameters("@amt").Direction = ParameterDirection.Output)
            cmd1.Parameters("@amt").Direction = ParameterDirection.Output
            Con.Open()
            cmd1.ExecuteNonQuery()
Label38.Text = cmd1.Parameters("@amt").Value.ToString()

    Catch ex As Exception
           // your code here
            Exit Sub
        Finally
            Con.Close()
        End Try


Thanks,
Mamun
   
v4
Comments
Member 10240008 17-Sep-13 6:44am
   
wsw
Rate this:
Please Sign up or sign in to vote.

Solution 1

You need to close the connection after getting the output value.
Try this. I think this will help you.
Dim cmd1 As SqlCommand = New SqlCommand("ReAlto", Con)
            cmd1.CommandType = CommandType.StoredProcedure
            cmd1.Parameters.Add("@MM", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & MM & "'"
            cmd1.Parameters.Add("@YY", SqlDbType.VarChar, 50, ParameterDirection.Input).Value = "'" & YY & "'"
            cmd1.Parameters.Add("@amt", SqlDbType.Int)
            cmd1.Parameters("@amt").Direction = ParameterDirection.Output)
            Con.Open()
            cmd1.ExecuteNonQuery()
            
Label38.Text = cmd1.Parameters("@amt").Value.ToString()

Con.Close()
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try closing the connection AFTER retrieving the output parameter.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100