How do I grab the return value from my stored procedure in 2.0?  
Author Message
Joe in FLA





PostPosted: .NET Framework Data Access and Storage, How do I grab the return value from my stored procedure in 2.0? Top

I can't figure out how to grab my return value from the my stored procedure. Obviously, I don't want to set the default value of the return value to zero, but find out if the return value is zero to know that it completed successfully.Below is my code:

SqlDataSource1.ConnectionString = constring

'Info from array to be sent to the stored procedure

Me.SqlDataSource1.UpdateParameters("ClientUno").DefaultValue = ClientUno

Me.SqlDataSource1.UpdateParameters("MatterUno").DefaultValue = MatterUno

Me.SqlDataSource1.UpdateParameters("AuthUno").DefaultValue = AuthUno

Me.SqlDataSource1.UpdateParameters("BillFormatTime").DefaultValue = WordValues(0)

Me.SqlDataSource1.UpdateParameters("BillFormatNarr").DefaultValue = WordValues(1)

Me.SqlDataSource1.UpdateParameters("BillFormatDate").DefaultValue = WordValues(2)

......

If Me.SqlDataSource1.UpdateParameters("Return_Value").DefaultValue = 0 Then

Response.Write("OK")

Else

Response.Write("No Good")

End If



.NET Development17  
 
 
DMan1





PostPosted: .NET Framework Data Access and Storage, How do I grab the return value from my stored procedure in 2.0? Top

Set the parameter direction to return value and then get the value after the command has been executed....

snippet...

Dim p As Data.SqlClient.SqlParameter

p.Direction = ParameterDirection.ReturnValue

Dim MySQLCMD As New SqlClient.SqlCommand

MySQLCMD.ExecuteNonQuery()

Dim MyReturnValue As String = CStr(p.Value)



 
 
Joe in FLA





PostPosted: .NET Framework Data Access and Storage, How do I grab the return value from my stored procedure in 2.0? Top

Yeah that is sort of the direction I took now. Running my stored procedure in Query Analzyer, I was getting a returned error. This process now fails, so it's working. Thanks. Here is what I did:

Dim sqlcon As New SqlConnection(constring)

Dim sqlcmd As New SqlCommand("usp_gfo_update_from_upload", sqlcon)

Dim retval As New SqlParameter( , Data.SqlDbType.Int)

retval.Direction = Data.ParameterDirection.ReturnValue

sqlcon.Open()

sqlcmd.Parameters.Add(retval)

'Info from array to be sent to the stored procedure

sqlcmd.Parameters.AddWithValue( , ClientUno)

sqlcmd.Parameters.AddWithValue( , MatterUno)

sqlcmd.Parameters.AddWithValue( , AuthUno)

sqlcmd.Parameters.AddWithValue( , WordValues(0))

sqlcmd.Parameters.AddWithValue( , WordValues(1))

sqlcmd.Parameters.AddWithValue( , WordValues(2))

......

sqlcmd.ExecuteNonQuery()

If retval.Value = 0 Then

Response.Write("OK")

Else

Response.Write("No Good")

End If