Using scope_identity() with ASP.NET controls

Question

I've built a form for entering some data into a table in my database, now I want to INSERT that data, and get back the ID (which is an autonumber) of the record that has just been added. I've got the INSERT part working just fine, and I've tacked "SELECT SCOPE_IDENTITY();" onto the end of that, but now I need to retrieve the returned number.

At the moment all of this is done with an SqlDataSource (with the "INSERT" statement being put together and fired by some C# in the codebehind file, which takes data from a form on the page).

Is there a quick way to grab that number and put it in a variable?

Answer 1

Hi, I believe this code might help:

 protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        //Read the value of the @Identity OUTPUT parameter
        string sID = e.Command.Parameters["@Identity"].Value.ToString();

        //Display new ID
        Label1.Text = sID;
    }

That was from this blog post

Answer 2

SQLDataSource uses ExecuteNonQuery internally which means that you can't retrieve the value directly. A workaround is to pass an output parameter to your SqlataSource's insert parameters list, and then set the value returned by SCOPE_IDENTITY in that variable. The value of the variable can be retrieved from in the Inserted event.

That is,

1. Add an output parameter called Identity to your SqlDatasource's Insert Parameters declaratively
2. Replace "SELECT SCOPE_IDENTITY();" at the end of the insert statement with "SET @Identity = SCOPE_IDENTITY();"

3. Now the value of the @Identity parameter can be retrieved in the "Inserted" event as follows:

   protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e) {

   //Read the value of the @Identity OUTPUT parameter
   int lastID = e.Command.Parameters["@Identity"].Value;
   ...  
   

   }