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:
- 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
- Select the SqlDataSource control in Design view
- Hit F4 to bring up its properties
- Click "InsertQuery"
- Click the elipses (...)
- In the Command and Parameter Editor, click "Add Parameter"
- Give the new parameter a name (such as NewId) and click "Show advanced properties"
- Change the Type to Int32 and the Direction to Output
- 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.
Currently rated 4.61 by 76 people
Date Posted:
05 June 2007 22:13
Last Updated:
03 January 2010 08:40
Posted by:
Mikesdotnetting
Total Views to date:
182259
Unfortunately, something went wrong and your message or comments have not been submitted
successfully. I'll try to fix whatever the problem is as soon as I can.
Thanks for your comments. They have been successfully sent to me. It may take a while before I
review them for publishing, so please check back again.
Comments
29 December 2008 21:14 from 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!
19 January 2009 04:48 from Graham
Excellent. Thank you. This is the solution I have needed for some time.
07 February 2009 11:37 from Hemalatha
Thank u.This wat i searched exactly for.Using both VB and C# is a great thing..
28 March 2009 19:57 from Marcel
The best info i have ever seen
01 April 2009 00:38 from Arvind
It help me a lot. Thanks!
18 April 2009 01:56 from Adrian
Hola, muy buen código y excelente explicación, me fue de mucha utilidad gracias
11 May 2009 21:42 from 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
12 May 2009 20:43 from Mikesdotnetting
@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.
01 June 2009 16:14 from 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
01 June 2009 20:37 from Mikesdotnetting
@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.
05 June 2009 20:22 from 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
20 July 2009 01:56 from 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.
20 July 2009 07:26 from Mikesdotnetting
@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.
31 July 2009 08:27 from fahad
thanks man great i solve my issue by your help
12 November 2009 15:10 from 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
01 December 2009 19:26 from 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.
01 December 2009 21:09 from Mikesdotnetting
@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.
17 December 2009 02:08 from 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
17 December 2009 09:13 from Mikesdotnetting
@Annu,
Google. Or your tutor.
12 February 2010 19:56 from 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!!
12 February 2010 20:44 from Mikesdotnetting
@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.
13 February 2010 03:57 from Mikeyyy
thanks!! i think have a man crush on you now, lol
no reply necessary :)
20 April 2010 15:46 from harshit
thanks
28 April 2010 11:47 from Karim
I really want to thank you for this, you saved me hours!
19 May 2010 14:41 from Luca
Thank you!
30 June 2010 09:32 from 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:
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
05 July 2010 20:50 from Mikesdotnetting
@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.
14 July 2010 03:35 from 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.
16 November 2010 08:15 from 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?
28 February 2011 08:35 from yasir
good material
05 April 2011 19:56 from 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
26 July 2011 21:32 from fulwaria interiors
very good collection of examples
22 April 2012 14:48 from Matt Wycherley
Brilliant, thanks - very clearly set out examples.
Cheers
Matt
23 August 2012 19:48 from Gray
Works perfectly!
15 September 2012 20:44 from 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!!