Retrieving the Just-Inserted ID of an IDENTITY Column Using a SqlDataSource ControlBy Scott Mitchell
ASP.NET offers a variety of tools and mechanisms for working with database data, including a number of data source controls, such as the SqlDataSource, ObjectDataSource, and LinqDataSource, among others. The SqlDataSource is one of the most basic data source controls as it operates directly against a configured database. Using the SqlDataSource control, an ASP.NET developer can retrieve, insert, update, or delete data by simply setting a few properties. Little to no code is needed.
While the SqlDataSource makes it a walk in the park to implement the most common data access scenarios, a little extra effort is needed for more intricate
scenarios. One such data access pattern is retrieving the value of the just-inserted record's ID field, where the ID field is an
IDENTITY column is a numeric column in a SQL Server database table that has its value automatically assigned when a new record
is added to the table.
IDENTITY columns are sometimes referred to as auto-number columns, as well.) Being able to get the ID value of the
just-inserted record is helpful in cases where you need to insert a new record and then insert other records into related tables, or when you want to
let the user start working with the just-added record, which might entail taking them to a URL like
This article shows how to use the SqlDataSource control to insert a new record and retrieve the value of its ID field. In particular, we will look at
two examples: one that uses a stored procedure to insert the new record and another that uses an ad-hoc
INSERT statement. Read on to learn more!
IDENTITY Column Overview
Most database tables provide some mechanism to uniquely identify each record. There are a variety of approaches, but a very common one is the use of an
IDENTITYcolumn, which is also referred to as an auto-number. An
IDENTITYcolumn is one whose value is automatically assigned by the database system when a new record is added to the table. These values start at some seed (usually 1) and increment by some specified amount with each new record (usually incremented by 1). Therefore, if you add three new records to the table, the
IDENTITYcolumn values for those first three records will be 1, 2, and 3, respectively.
IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted record's
column. Perhaps after inserting a new record you want to automatically whisk the user to a details page where you need to pass along the
column value through the querystring, or maybe you want to add additional records in a child table and need the just-inserted parent record's
IDENTITY column value to properly link the child records to the parent. In either case, in Microsoft SQL Server you can use the
SCOPE_IDENTITY() function to get the
IDENTITY column value
of the just-insert record.
In order to pull back this information when using the SqlDataSource we need to do the following:
- Define the
INSERTstatement. This can be performed in a stored procedure or via an ad-hoc SQL statement.
- Return the just-inserted
IDENTITYvalue using the
SCOPE_IDENTITY()function and assigning the value to an output parameter.
- To access the resulting output parameter's value we need to create an event handler for the SqlDataSource's
Insertedevent. This event fires after the insert "action" has been performed. Once we have the
IDENTITYvalue of the just-inserted record we can use it as needed.
INSERTis performed by a stored procedure, and one that shows how to get the same results when using an ad-hoc SQL statement. Both examples use the Northwind database and insert records into the
|Uniquely Identifying Records Using GUIDs|
For more information on different ways to uniquely identify database records, be sure to read: Techniques for Uniquely Identifying Database Records.
Returning the Just-Inserted Record's ID Value Using an Ad-Hoc
Before we look at how to have the SqlDataSource control return the just-inserted record's ID value, let's first focus on how to insert a new record and worry about getting back the ID value later. The SqlDataSource includes three properties for specifying a command to run when inserting data:
InsertCommand- the text of the command to execute. Can be a stored procedure name or an ad-hoc query.
InsertCommandType- this property tells the SqlDataSource control whether the
InsertCommandis a stored procedure or an ad-hoc query.
InsertParameters- information about the parameters used in the
InsertCommand. Usually this collection includes a series of
<asp:Parameter>objects whose values are automatically populated by a data Web control like the DetailsView or FormView through two-way data binding.
INSERTquery to add a new record to the
Productstable might set the
InsertParametersproperties as follows:
The above ad-hoc
INSERT statement adds a new record to the
Products table specifying values for the
Discontinued columns. These parameters are specifying in the
<asp:Parameter> objects. The
InsertCommandType is not explicitly set; its default value is
means that the SqlDataSource control parses the
InsertCommand as an ad-hoc query.
To complete this example we would add a DetailsView to the page, bind it to the
ProductsDataSource SqlDataSource, configure the DetailsView
to support inserting, and customize the inserting interface as necessary. These steps have been performed in the demo, but I'm going to skip over them
here as these topics are discussed in detail in my Accessing and Updating Data in
ASP.NET article series. In particular, see the Inserting Data and
Customizing the Editing Interface installments. The screen shot below shows the
DetailsView in action.
At this point we have a page that enables the user to add a new product to the database. However, we do not know the value of the newly added product's
ProductID. To capture this information we need to update our
INSERT statement to use
ProductID and assign it to an output parameter. Update the SqlDataSource control's
InsertCommand property so that it includes
The INSERT statement is the same as before, but we've added
SELECT @NewProductID = SCOPE_IDENTITY(). This assigns the just-inserted record's
ID value to the output parameter
We now need to update the
InsertParameters collection to include this new parameter. Add the following declarative markup to the
Note the addition of
<asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />. This markup introduces a new parameter
to the collection named
NewProductID and indicates that its an output parameter of type
After inserting a product, the
@NewProductID output parameter contains the value of the just-inserted record's ID field. To get at this
information we need to create an event handler for the SqlDataSource's
Inserted event. The
Inserted event fires after the
insert "action" has been performed, in this case after the ad-hoc
INSERT statement has been sent to the database. (For more information
on the SqlDataSource control's events, see Examining the Data Source Control's Events.)
The demo includes a Label Web control that displays the ID of the just-inserted product, as shown by the screen shot below.
That's all there is to it!
Returning the Just-Inserted Record's ID Value Using an Stored Procedure to Insert the Record
The SqlDataSource control can use stored procedures when querying, inserting, updating, and deleting data from the database. If you use a stored procedure to insert a record and need to retrieve the just-inserted ID value from your ASP.NET page, you can have the stored procedure return the ID value via an output parameter. The demo available for download at the end of this article includes a stored procedure named
AddProductAndReturnNewProductIDValuethat inserts a record into the
Productstable and returns the just-inserted ID using an output parameter named
@NewProductID. The stored procedure's syntax follows:
As you can see, the stored procedure includes an output parameter named
@NewProductID and this value is assigned via the
SCOPE_IDENTITY() function just like in our ad-hoc example. (For more on using output parameters with a stored procedure, see
Retrieving Scalar Data from a Stored Procedure.
From the ASP.NET page, everything else is the same as with the ad-hoc example: you need to add an output parameter to the
collection, and the value of the output parameter is accessible from the SqlDataSource control's
Retrieving the ID value of a just-inserted record is quite useful in many data access scenarios. SQL Server includes the
SCOPE_IDENTITY()function for retrieving this information. To get this back to your ASP.NET page, you need to assign the value returned by the
SCOPE_IDENTITY()function to an output parameter. The output parameter's value can then be accessed in the SqlDataSource control's