SqlDataSource: Getting @@Identity after Insert

Yesterday I was developing a simple form using a SqlDataSource to INSERT or UPDATE when saving the form.  The form consisted of a few controls and a button.  In the case of a new item, on clicking the save button I am executing SqlDataSource1.Insert().  For an existing item, I’m using an “ID” parameter on the query string which in turn is used by the UPDATE command.  So I need the ID of the row I’ve just inserted to allow the user to update the form.  I should mention my table has a column called “ID” of type int which is specified as the auto-incrementing identity column. 

 

I put together the form in less than 30 minutes and then spent twice that time trying to find the solution you are reading now.  I was sorely tempted to ditch the DataSource as this is straightforward when you’re executing your SQL commands directly in the code-behind.  But I was curious and persevered until I found the answer:

 

Firstly, let me describe how to set up the DataSource to do the insert.  The InsertCommandType is “Text”.  For the InsertQuery click the … button to open the Command and Parameter dialog.  Use the query builder, if you like, to create your insert statement.  After the insert statement append “SET @Identity = @@Identity;” so our insert command looks something like this:

 

INSERT INTO TestTable (TestCol) VALUES (@TestValue); SET @Identity = @@Identity;

 

The statement above has 2 parameters.  One is TestValue we’re setting in to TestCol.  The other is Identity which will be the ID of the column we insert.  Click Add Parameter and type “TestValue”, Parameter source is control, and ControlID is a textbox.  Add another parameter called “Identity”.  This time click Show Advanced Properties, set Direction to “Output” and Type to “int” (leaving Parameter source as “None”). 

 

Now the trick is catching the DataSource at the right time so that our output parameter will have a value.  Don’t mistake the DataSource.InputParameters as the place to look as this merely describes the parameters we’ve configured above.  To actually see the parameters after execution you need to catch the OnInserted event.  Here we have SqlDataSourceStatusEventArgs which contains the command and its parameters after the command has executed.

 

    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;

    }

 

So with the ID known I can now redirect the user to “page.aspx?ID=” + sID such that they may update the form.

Advertisement