jump to navigation

SqlDataSource: Getting @@Identity after Insert August 20, 2007

Posted by codinglifestyle in ASP.NET, C#.
Tags: , ,
trackback

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.

Advertisements

Comments»

1. jew - August 23, 2007

Thank you. Great Post!

2. Dave Marklle - August 26, 2007

Danger will Robinson!

Take a look at SCOPE_IDENTITY() instead. You almost never want to get @@IDENTITY from the database — if there were to be a trigger that fired on that connection which itself had an identity column, BOOM, your value would be wrong.

3. Andrea - September 17, 2007

You are my hero! Do you know how many sites I have looked at in attempt to accomplish this??? There are SO many and they get so complicated. I knew there had to be an easier way. Thanks so much for figuring it out and posting it!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: