How to retrieving Identity or Autoincrement Values with ADO.Net
A lot of time we are faced with a situation where we needs to add a new record
into the database where the primary key column is an autogenrated identity
column. After the record has been added, the question becomes how do I get the
value of the autogenerated column.
There are couple of techniques that you can choose from.
The most favored solution is to use stored procedure to insert data and return
the autogenerated column value in OUT parameter. Microsoft has a nice sample in
Retrieving Identity or Autonumber Values. This article also shows the
use of the technique for Microsoft Access database.
The second technique will be the one that a lot of use are used to using in
classic ASP programming. In this technique we make use of the fact that the
autogenerated identity value is returned in
So you can construct a batch
SQL query in which first part inserts
the record and second query gets the value of
How do we do it?
The whole idea is to build the batch SQL query. In the source code of example
project we have create a function that is used to insert a user record in
table of our database. Following is a general representation of the query that
gets executed to insert the record and return identity column value.
INSERT INTO MyTable (fld1, fld2) VALUES (val1, val2);SELECT @@IDENTITY
After the query has been executed, the identity value is returned in
DataReader has only one column and you can get the
value out by calling
GetValue method on it.
conn = new SqlConnection(strConnString);
// Open connection
// Build sql query.
string strSelectQuery = "SELECT @@IDENTITY";
string strInsertQuery = "INSERT INTO siteusers (u_user_name, u_user_password) VALUES('";
strInsertQuery += strUser;
strInsertQuery += "','";
strInsertQuery += strPwd;
strInsertQuery += "')";
SqlDataReader myReader = myCommand.ExecuteReader();
object obValue = myReader.GetValue(0);
iUserID = Convert.ToInt32(obValue.ToString());
Watch out for?
This technique will only work if you are adding one record at a time in the
If you have comments or questions, please feel free to