Stone Coast Web Design's Weblog

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 method. 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, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

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

SQL Server and Scope_Identity()

While Sql Server also supports @@Identity(), the recommended method for obtaining identity values on this platform is Scope_Identity(),

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().

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

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.

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 stored 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