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...

@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.:(

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.SqlDataSourceStatusEvent Args) 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.