Scope_Identity with @Return_Value
I am currently writing an application that inserts data into a MS SQL Server 2000 DB. I am developing the application in Microsoft Visual Web Developer 2005 EE. So far everything has come together nicely. However, I have run into a situation that I can't seem to figure out.
I wrote a stored procedure to insert data into the DB from a Web Form. The insert works nicely; however, one of the requirements I have is that I would like the DB to return the last row inserted into to my table. I am using the Scope_Identity() function at the end of my stored procedure to accomplish this task:
...Last few lines of Stored Procedure...
I have conducted several tests on the SP, and received the following output from the DB when it is run:
(1 row(s) affected)
(0 row(s) returned)
@GID = <NULL>
@RETURN_VALUE = 116
Finished running [dbo].[usp_InsertProject].
I would like to take the result of the @RETURN_VALUE (116), and create a variable that I can use to embed into an e-mail. The code for the e-mail generation is working however, the value that comes through for the @RETURN_VALUE is always 0. I have tried several different things to get this to work, but so far no luck.
Here is the application code I am using to create the e-mail:
Dim mySqlDataSource1 = SqlDataSource1.ConnectionString.ToString
Dim myConnection As New Data.SqlClient.SqlConnection(mySqlDataSource1)
Dim myCommand As New Data.SqlClient.SqlCommand("usp_InsertProject", myConnection)
myCommand.CommandType = Data.CommandType.StoredProcedure
Dim parameterGID As New Data.SqlClient.SqlParameter("@RETURN_VALUE", Data.SqlDbType.Int)
parameterGID.Direction = Data.ParameterDirection.ReturnValue
Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()
Dim GID As Integer = CInt(parameterGID.Value)
...E-mail code is below this, but is working, so not included ...
I would like to insert the GID variable into the e-mail, but for some reason it won't work. The following error occurs when the InsertCommand is invoked:
ExecuteReader requires an open and available Connection. The connection's current state is closed.:(