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 the documentation 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 @@IDENTITY variable. So you can construct a batch SQL query in which first part inserts the record and second query gets the value of @@IDENTITY variable.

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 siteusers 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 object. This DataReader has only one column and you can get the value out by calling GetValue method on it.

conn = new SqlConnection(strConnString);
// Open connection
conn.Open();
// 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();
try 
{
	while (myReader.Read()) 
	{
		object obValue = myReader.GetValue(0);
		iUserID = Convert.ToInt32(obValue.ToString());
		Trace.WriteLine(obValue.ToString());
	}
}
finally 
{
	myReader.Close();
}										
										

Watch out for?

This technique will only work if you are adding one record at a time in the query.

If you have comments or questions, please feel free to contact us.