[Resolved] Read RETURN val from Stored Procedure

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:

VB Code:

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?

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

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

Regards
Jorge

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

VB Code:

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:

VB Code:

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)