[Resolved] Read RETURN val from Stored Procedure

[jcfowl]

Normally I use a DataReader to read values from a stored procedure but it is not working in this situation. On a stored procedure that INSERTS into a tabel I have a RETURN statement that returns the @@IDENTITY (identity value for the inserted row, primary key). When I use the code below:

dr = cmd.ExecuteReader
MsgBox(dr.GetValue(0))

I get an error that says there is nothing to read. Anybody know how to read this value?

[jcfowl]

The SQL code below works in SQL Query Analyzer. The stored procedure below Inserts the values passed and RETURNS the @@IDENTITY. How can I get the return value in .Net?


DECLARE @retval as bigint
exec @retval = spColonyContacts_INS 0,0,'',0,0,'','','',''
print @retval

[Asgorath]

In your situation i would use a .executescalar and modify your stored procedure to include a select statement before your return

Regards
Jorge

[jcfowl]

Got it, thanks to this link:
http://msdn.microsoft.com/library/de...l/gazoutas.asp

cmd.Parameters.Add("@ReturnVal", SqlDbType.BigInt).Direction = ParameterDirection.ReturnValue

I added the parameter to my command object and you don't even need the datareader. Here is all the code to get the return value of the stored procedure:

Dim cmd As New SqlClient.SqlCommand
Dim conn As New SqlClient.SqlConnection(modGlobal.g_ConnString)
 
cmd.CommandType = CommandType.StoredProcedure
'Add all parameters needed for insert statement
cmd.Parameters.Add("@ReturnVal", SqlDbType.BigInt).Direction = ParameterDirection.ReturnValue
cmd.Connection = conn
If conn.State <> ConnectionState.Open Then conn.Open()
cmd.ExecuteNonQuery()
MsgBox(cmd.Parameters("@ReturnVal").Value)