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 theScope_Identity() function at the end of my stored procedure to accomplish this task:

...Last few lines of Stored Procedure...

@.ANNEXCITYCOUNCILACTION,
@.CRA
)

RETURN SCOPE_IDENTITY()

GO

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:

Sub SendEmail()

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

myCommand.Parameters.Add(parameterGID)

Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()

Dim GID As Integer = CInt(parameterGID.Value)
GID.ToString()

...E-mail code is below this, but is working, so not included ...

End Sub

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.

You have two problems from what I can see. The first is indicated in the error. Run myConnection.Open() before you run a command against the database. Second is that you're running ExecuteReader(), but you don't seem to be using the data reader. If you're using it below, please ignore. If you aren't, you need to be running ExecuteNonQuery() instead.|||

Hi Chris,

Thanks for the help...I modifed the code, and added:

Email Sub changes:
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Stored Procedure changes:
@.CRA
)

SELECT
@.GID = @.@.Identity
GO

The results of the Stored Procedure are now:

(1 row(s) affected)
(0 row(s) returned)
@.GID = 127
@.RETURN_VALUE = 0
Finished running [dbo].[usp_InsertProject].

I now have an output parameter with the correct value I would like to set as a variable in my code.

However, the InsertCommand is being executed from a FormView, and is using a SqlDataSource to access the DB. When I put myConnection.Open() into the Sub it gives me the following error:

Error executing 'InsertCommand' in SqlDataSource 'SqlDataSource1'. Ensure the command accepts the following parameters: @.CREATEDBY, ...

The Sub is being run on the InsertButton_Click event. Is there a way to reference the open connection that the SqlDataSource object already has open? This way I can grab the @.GID value...

|||

If you want to use a sqldatasource for the insert, that's fine. Not knowing exactly how your database is set up, and what you can/can not change, it seems to me that what you want is:

Create a stored procedure that takes all your values, and returns a last inserted id. If you can not change the current stored procedure, create a new one that takes the same parameters, calls the original stored procedure then issues the T-SQL command RETURN SCOPE_IDENTITY(). If you do not already have a return value parameter set up for sqldatasource1's insert command, add one now (Or use the wizard to add all the parameters automatically, and it'll create @.RETURN_VALUE for you).

Inside the sqldatasource1_Inserted event (It must be done here), add code that looks like:

dim MyID as integer=e.command.parameters("@.RETURN_VALUE")

SendEmail(MyID)

Then remove all your myConnection/myCommand code, and change your SendEmail procedure to accept the id as a parameter.

|||

I got it to work by using the following code:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

Dim myID As Integer

myID = e.Command.Parameters("@.GID").Value

myID.ToString()

Dim Email As New Net.Mail.MailMessage()

E-mail code below this...

The key to getting it to work was putting it in the SqlDataSource1_Inserted event. This allowed me to get the output parameter.

Thanks for your help Motley...You pointed me in the right direction, and helped me out tremendously!