ItProjects For You

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.

Getting started with the solution in Visual Studio.NET

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.

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();
}
}

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:

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();
}
}

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

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;
}
}

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.

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

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:

protected void SqlDataSource1_Inserted
(object sender,SqlDataSourceStatusEventArgs e)
{
int newid = (int)e.Command.Parameters["@NewId"].Value;
}