Getting the identity of the most recently added record

The built-in functions @@Identity and Scope_Identity() are designed to retrieve the most recently added record's autoincrement identity value from Access and Sql Server respectively. Here are some usage examples.
Access and @@Identity

The Jet 4.0 provider supports @@Identity, which means that developers no longer need to use Select Max(ID) or some other contrived method of obtaining new ID values. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd using ExecuteNonQuery() to perfom the Insert, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

 

//C#

string query = "Insert Into Categories (CategoryName) Values (?)";

string query2 = "Select @@Identity";

int ID;

string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb";

using (OleDbConnection conn = new OleDbConnection(connect))

{

  using (OleDbCommand cmd = new OleDbCommand(query, conn))

  {

    cmd.Parameters.AddWithValue("", Category.Text);

    conn.Open();

    cmd.ExecuteNonQuery();

    cmd.CommandText = query2;

    ID = (int)cmd.ExecuteScalar();

  }

}

 

'VB

Dim query As String = "Insert Into Categories (CategoryName) Values (?)"

Dim query2 As String = "Select @@Identity"

Dim ID As Integer

Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb"

Using conn As New OleDbConnection(connect)

  Using cmd As New OleDbCommand(query, conn)

    cmd.Parameters.AddWithValue("", Category.Text)

    conn.Open()

    cmd.ExecuteNonQuery()

    cmd.CommandText = query2

    ID = cmd.ExecuteScalar()

  End Using

End Using

 

A quick word about the absence of conn.Close() in all these examples: in all the snippets, the Connection object is instantiated within a 'using' block. At the end of the 'using' block, Dispose() is automatically called on objects created in the beginning of the block. If you do not employ 'using' blocks, make sure you explicitly call conn.Close() as soon as you are done with the connection.

Sql Server and Scope_Identity()

While Sql Server also supports @@Identity, the recommended method for obtaining identity values on this platform is Scope_Identity(), which retrieves the last identity value created in the current scope. 'Scope' is a single module, which can be a stored procedure, batch, function or trigger. This can be used in a number of ways. Sql Server supports batch statements which would have you append Select Scope_Identity() to the end of the Insert statement, optionally separating the two statements with a semicolon, and just using ExecuteScalar() against the batch command to return the single value:

 

//C#

string query = "Insert Into Categories (CategoryName) Values (@CategoryName);" +

"Select Scope_Identity()";

int ID;

string connect = @"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" +

    "Database=Northwind;Trusted_Connection=Yes;";

using (SqlConnection conn = new SqlConnection(connect))

{

  using (SqlCommand cmd = new SqlCommand(query, conn))

  {

    cmd.Parameters.AddWithValue("@CategoryName", Category.Text);

    conn.Open();

    ID = (int)cmd.ExecuteScalar();

  }

}

 

'VB

Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _

    "Select Scope_Identity()"

Dim ID As Integer

Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _

    "Database=Northwind;Trusted_Connection=Yes;"

Using conn As New SqlConnection(connect)

  Using cmd As New SqlCommand(query, conn)

    cmd.Parameters.AddWithValue("@CategoryName", Category.Text)

    conn.Open()

    ID = cmd.ExecuteScalar()

  End Using

End Using

 

Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().

 

//C#

string query = "AddCategory";

int ID;

string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";

using (SqlConnection conn = new SqlConnection(connect))

{

  using (SqlCommand cmd = new SqlCommand(query, conn))

  {

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@Category", Category.Text);

    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID");

    cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;

    conn.Open();

    cmd.ExecuteNonQuery();

    ID = (int)cmd.Parameters["@CategoryID"].Value;

  }

}

 

'VB

Dim query As String = "AddCategory"

Dim ID As Integer

Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"

Using conn As New SqlConnection(connect)

  Using cmd As New SqlCommand(query, conn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@Category", Category.Text)

    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")

    cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output

    conn.Open()

    cmd.ExecuteNonQuery()

    ID = cmd.Parameters("@CategoryID").Value

  End Using

End Using

 

And the procedure...

 

CREATE PROCEDURE AddCategory

  -- Add the parameters for the stored procedure here

  @Category NVARCHAR(15),

  @CategoryID INT OUTPUT

AS

BEGIN

  SET NOCOUNT ON;

 

  -- Insert statements for procedure here

  INSERT INTO Categories (CategoryName) VALUES (@Category)

  SET @CategoryID = SCOPE_IDENTITY()

END

 

Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.

 

//C#

string query = "AddCategory";

int ID;

string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";

using (SqlConnection conn = new SqlConnection(connect))

{

  using (SqlCommand cmd = new SqlCommand(query, conn))

  {

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@Category", Category.Text);

    conn.Open();

    ID = (int)cmd.ExecuteScalar();

  }

}

 

'VB

Dim query As String = "AddCategory"

Dim ID As Integer

Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"

Using conn As New SqlConnection(connect)

  Using cmd As New SqlCommand(query, conn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@Category", Category.Text)

    conn.Open()

    ID = cmd.ExecuteScalar()

  End Using

End Using

 

The (slightly) smaller procedure:

 

CREATE PROCEDURE AddCategory

  -- Add the parameters for the stored procedure here

  @Category NVARCHAR(15)

AS

BEGIN

  SET NOCOUNT ON;

 

  -- Insert statements for procedure here

  INSERT INTO Categories (CategoryName) VALUES (@Category)

  SELECT SCOPE_IDENTITY()

END

 

So which method should you use with SQL Server? ExecuteNonQuery and an output parameter? Or no parameter and ExecuteScalar? The answer to that is whichever way you prefer. There is no real performance difference between the methods. My personal preference is for a stored procedure and ExecuteScalar. It requires the least amount of code :)

DataSource controls

Sql Server

And finally, for those that like to use the SqlDataSource, here's how to use the Insert() method to retrieve Scope_Identity() from SQL Server.

The SqlDataSource control internally performs an ExecuteNonQuery() when it is called on to perform an Insert, so by just appending SELECT SCOPE_IDENTITY() to the InsertCommand and trying to retrieve the return value will not work. This is because ExecuteNonQuery() returns either the number of rows affected by an Update, Insert or Delete operation, or -1 for all other operations, which will overwrite any resultset produced from the appended Select statement. The way to work around this is to use a parameter value. This can be achieved declaratively using the following steps:

  1. First, add the following to the InsertCommand: "SET @NewId = Scope_Identity()", making sure it is separated from the first part of the command by a semicolon
  2. Select the SqlDataSource control in Design view
  3. Hit F4 to bring up its properties
  4. Click "InsertQuery"
  5. Click the elipses (...)
  6. In the Command and Parameter Editor, click "Add Parameter"
  7. Give the new parameter a name (such as NewId) and click "Show advanced properties"
  8. Change the Type to Int32 and the Direction to Output
  9. Click OK to seal the deal.

Now if you switch to Source view, you will see that an additional parameter has been added to the InsertParameters collection:

 

<asp:Parameter Direction="Output" Name="NewId" Type="Int32" />

 

To retrieve the value, we need to use the SqlDataSource.Inserted event. Back in Design view, bring up the properties of the SqlDataSource again, if they are not still showing, and click the lightning bolt to open up the events list. Double click the Inserted event, which will create an event handler in code-behind, and associate the SqlDataSource with the event handler. To retrieve the value from the event handler is straightforward:

 

//C#

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

{

  int newid = (int)e.Command.Parameters["@NewId"].Value;

}

 

'VB

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object,

    ByVal e As SqlDataSourceStatusEventArgs)

      Handles SqlDataSource1.Inserted

  Dim newid As Integer = e.Command.Parameters("@NewId").Value

End Sub

 

Access

The above approach won't work with Access, whether you use a SqlDataSource control or an AccessDataSource control. This is because Access is unable to accept batch commands. An alternative that will work with Access and the DataSource controls is to reuse the control's Connection object against a new Command object to retrieve @@Identity. In order for this apporach to work, you must reference the System.Data.OleDb namespace in your code-behind with a using or Imports statement as per your language of choice:

 

//C#

protected void AccessDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)

{

  string query = "SELECT @@IDENTITY";

  OleDbCommand cmd = new OleDbCommand(query, (OleDbConnection)e.Command.Connection);

  int newid = (int)cmd.ExecuteScalar();

}

 

 

'VB

Protected Sub AccessDataSource1_Inserted(ByVal sender As Object,

  ByVal e As SqlDataSourceStatusEventArgs)

    Handles AccessDataSource1.Inserted

  Dim query As String = "SELECT @@IDENTITY"

  Dim cmd As New OleDbCommand(query, CType(e.Command.Connection, OleDbConnection))

  Dim newid As Integer = cmd.ExecuteScalar()

End Sub

 

Scope_Identity() for Sql Server will not work with the above, because the change in command object changes the scope from the original command.

 

Date Posted: Tuesday, June 5, 2007 10:13 PM
Last Updated: Friday, October 10, 2014 2:26 PM
Posted by: Mikesdotnetting
Total Views to date: 287597

39 Comments

Monday, December 29, 2008 9:14 PM - Alan

This is great. It's the most comprehensive collection of methods I've seen anywhere. Particularly because you cover SQL Server AND Access, without knocking Access. Great work!

Monday, January 19, 2009 4:48 AM - Graham

Excellent. Thank you. This is the solution I have needed for some time.

Saturday, February 7, 2009 11:37 AM - Hemalatha

Thank u.This wat i searched exactly for.Using both VB and C# is a great thing..

Saturday, March 28, 2009 7:57 PM - Marcel

The best info i have ever seen

Wednesday, April 1, 2009 12:38 AM - Arvind

It help me a lot. Thanks!

Saturday, April 18, 2009 1:56 AM - Adrian

Hola, muy buen código y excelente explicación, me fue de mucha utilidad gracias

Monday, May 11, 2009 9:42 PM - kc

im impressed, and i do not even know how to use this information.
somebody gave me an access file (v2003) and they removed the primary key.
can i use your information to figure out the creation date of each record?
(it is a simple, flat access file only providing text similar to a spreadsheet).
thank you for re mediating for me

Tuesday, May 12, 2009 8:43 PM - Mike

@kc

Unfortunately, nothing in this article will help with recovering the creation date of a record in Access. Unless it is recorded as a field in the table, I doubt it can be done. Having said that, I know very little about Access as an application.

Monday, June 1, 2009 4:14 PM - Lance

Hi

When reading the your article you posted i thought you might help.

I am using formview with a sql connection, I need to verify on insert that the user doesn't duplicate any records. I am trying to get this code to work ... but i am messing it up.

Protected Sub FormView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertEventArgs) Handles FormView1.ItemInserting
Dim CrapData As TextBox = DirectCast(FormView1.FindControl("CrapDataTextBox"), TextBox)
Dim strConnString As String = ConfigurationManager.ConnectionStrings("AffiliateTrackingConnectionString").ConnectionString

Dim oSqlConnection As New SqlConnection(strConnString), strQuery As String, oRs As SqlDataReader
' SQL Query
strQuery = "SELECT Count(CrapData) as TheCount FROM [emailRules] where CrapData=@CrapData"

'Response.Write(strQuery)
oRs = Data.DBConnections.getDbConnection(strQuery, strConnString)
'Database Objects - Initialization

Dim cmd As New SqlCommand(strSQL, oConn)
cmd.Parameters.AddWithValue("@CrapData", CrapData.Text)

Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.AddWithValue("", CrapData.Text)
Dim Result As Integer = CInt(cmd.ExecuteScalar())

If Not oRs Is Nothing Then
If Result > 0 Then
e.Cancel = True
Dim Duplicate As Label = DirectCast(FormView1.FindControl("lblDuplicate"), Label)
Duplicate.Text = "That spam rule is already in use"
End If
End If
End Sub

Monday, June 1, 2009 8:37 PM - Mike

@Lance

I suggest you post a question to the forums at www.asp.net. That's the best place for general ASP.NET questions. When you do, explain what you mean by "messing it up". Include any error messages you get.

As a rule, I don't answer questions on my site unless they are directly related to some aspect of the article that they are posted under. Also, it can take hours or days before I moderate a comment and publish it here. You will probably find that's true of anyone else's personal web site too.

Friday, June 5, 2009 8:22 PM - Tina

Hi Mike, Pl help this beginner i.e. me!!. I have tried to implement the last VB soln for an access db. What should be "AccessDataSource1 " after the handles statement. I have created a strongly typed data set using the table adapter wizard and have created a function AddCo in a .vb BLL. It's in this function that I need to get the ID.
How do I call the AccessDataSource1_Inserted sub?
Thanks in advance

Monday, July 20, 2009 1:56 AM - Thiago

Thanks a lot for the information, it is great. I am facing the following problem though. I am using the OleDbCommand sample, but it is not inserting the row and im not getting any errors. When I try only the insert it insert the row without any problem. But when I try to get the IDENTITY by using the SELECT part, I get the ID and everything, but when I go and check the database the row was not inserted. Any ideas or solution? Thanks a lot.

Monday, July 20, 2009 7:26 AM - Mike

@Thiago

Are you sure you are checking the same database that your connection string points to? If you are getting a new ID value returned, then the record is definitely being inserted somewhere.

Friday, July 31, 2009 8:27 AM - fahad

thanks man great i solve my issue by your help

Thursday, November 12, 2009 3:10 PM - Colin Jarvis

I would be grateful if you could provide a code example to the above using Accessdatasource1 to say the products table of the northwind database including the code behind.

The connection should not be sql

Then have a label on the page that displays the returned Product ID

Many thanks

Tuesday, December 1, 2009 7:26 PM - Josh

Great stuff. Covering the multiple options has got me thinking about using the SQLDataSource vs building connection string and query in the code behind. What's your feeling on this?

Thanks again for great examples.

Tuesday, December 1, 2009 9:09 PM - Mike

@Josh

I have a soft spot for the SqlDataSource. It's what got me hooked on ASP.NET in the first place. However, I never use them now. They are great for relatively simple sites, but once you take on more complex projects, you find that they have a numebr of shortcomings. It's well worth understanding your ADO.NET thoroughly.

Thursday, December 17, 2009 2:08 AM - Annu

i am undergraduate who is doing my final year project i need some help on certain things for example cmd.ExecuteScalar() what is it for, and where to use this and is there other stuff like this and where can i find them and how to use. thank you

Thursday, December 17, 2009 9:13 AM - Mike

@Annu,

Google. Or your tutor.

Friday, February 12, 2010 7:56 PM - Mikeyyy

sorry for the newbie questions in advance:

1. in your first c sharp example against sql server...

Do i need to close the connection?

2. if i have an asp page like Test1.aspx - which accesses a static class that calls your code (see #1)

Will each different user web session get the correct primary key id value after the insert?

again sorry for the simple questions!!

Friday, February 12, 2010 8:44 PM - Mike

@Mikeyy

I have used Using statements. When you do that, the resource that's instantiated within them is automatically closed and disposed of at the end of the block. As to the second question, the answer is yes. It's not session specific.

Saturday, February 13, 2010 3:57 AM - Mikeyyy

thanks!! i think have a man crush on you now, lol

no reply necessary :)

Tuesday, April 20, 2010 3:46 PM - harshit

thanks

Wednesday, April 28, 2010 11:47 AM - Karim

I really want to thank you for this, you saved me hours!

Wednesday, May 19, 2010 2:41 PM - Luca

Thank you!

Wednesday, June 30, 2010 9:32 AM - Neil

Hi

Thanks for the code, but i am having a problem :(

i have modded your code for accessing and inserting a Access DB useing C#

here is my code:

string query = "INSERT INTO theTimes (ID, User, LieuHours) VALUES (?, ?, ?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|time.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
cmd.Parameters.AddWithValue("", NameTxtBox.Text);
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = query2;
ID = (int)cmd.ExecuteScalar();
NameTxtBox.Text = "test";
}
}

and it boms out ever time it gets to the 'cmd.ExecuteNonQuery();'

any idea what i have done wrong. (i am very new to c#)

Cheers,
Neil

Monday, July 5, 2010 8:50 PM - Mike

@Neil

At a guess, because you don't provide the error message details you get when the site "boms out", I would remove the ID value from the SQL query on the assumption that it might be an Autonumber column, and you should not enter values into it.

If that doesn't help, you should post a question to the www.asp.net forums. You won't have to wait days for me to get round to managing this site.

Wednesday, July 14, 2010 3:35 AM - Luís Gustavo N Ramos

Perfect! Exactly I'm looking for. Thank you very much! I'll recommend this site for everyone I've know.

Tuesday, November 16, 2010 8:15 AM - Srinivas

Hi Mike,
Thanks for the Great Article.

I am using MS Access Database one of my important table is having Composite Primary Key.When I used the ExecuteScalar method, it is showing the wrong record number instead of currently inserted record auto number.

Can you please help me in this?

Monday, February 28, 2011 8:35 AM - yasir

good material

Tuesday, April 5, 2011 7:56 PM - MGR

Hey Man! GREAT Code... it works great too... I am having but one problem... everytime I execute the code, in inserts 2 records. Any ideas???

Cheers,MGR

Tuesday, July 26, 2011 9:32 PM - fulwaria interiors

very good collection of examples

Sunday, April 22, 2012 2:48 PM - Matt Wycherley

Brilliant, thanks - very clearly set out examples.

Cheers

Matt

Thursday, August 23, 2012 7:48 PM - Gray

Works perfectly!

Saturday, September 15, 2012 8:44 PM - JS

It worked!!!!
A very comprehensive analysis of the situation.
I have never commented before on this sort of thing - but I am really delighted to have learnt about the sort of thing tonight with so little pain!!

Monday, June 10, 2013 11:55 AM - NF

Thanks,
But I have a problem when following the steps while using the SqlDataSource, it's that it works only once and when trying to add second record the .Insert() method don't work and the the returned Identity value comes with DBNull.

any help please ???

Friday, January 17, 2014 1:42 AM - Kishore

super tutorial...thank you..

Monday, June 23, 2014 8:22 PM - Michal

Hi Mike, your tutorial is still on top of Google results when searching for 'how to get back auto-incremented id'.
It helped me after 6 years!
One question:
I prepare a tree structure of objects - records from a self-referring table - and then I want to commit all the records representing a new sub-tree to database in tree of already stored records (can be loaded partially).
In memory the tree is implemented as a map where keys are ids and values contain reference to parent as its id, like:
id = 1, {parent = 0, ...}
id = 2, {parent = 1, ...}
id = 3, {parent = 1, ...}
id = 4, {parent = 2, ...}
This is like the database table records and when a tree is loaded from database, actual ids are used.
However, for new items I have no actual db ids.
I do not want to store each new item in tree immediately (want to be able to cancel it before commit and never have it in db in such case).
So I assume I need to create :preliminary ids:
Several issues:
I need to have them unique so that actual ids of a sub-tree records loaded from db will not be in collision with :preliminary ids: - how to ensure this?
OK, I can make id pair of id and bool: stored/new record.
But when storing I have a hard time updating all the parent ids so that they match with the actual id got with Scope_Identity.
Is not there a standard technique to 'reserve' ids so that they are not used in a table and then they are either used in 'my' INSERTs or released?
Only way I see is to store the records with a 'preliminary' flag and then either reset to false - make records 'confirmed', but I would like to avoid storing data to database all the time while preparing them...
Thanks.

Monday, October 20, 2014 5:03 AM - Dharmendra

Great Article.
Thank you very much!
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...