This step-by-step article describes how to retrieve the identity value when you add a record into a SQL Server table with an identity field.

Requirements

This sample uses the Northwind database in SQL Server and retrieves the identity values only for DataTables with no child tables. It can also be used with MSDE; however, the Northwind database is not included with MSDE. For information about how to use this procedure with MSDE, see the Troubleshooting section of this article.


A SQL Server identity field is an auto number field that you can define an incremental value for. For this reason, you cannot insert or update a value in this field as long as Identity_ insert is off, which is the default for a SQL Server identity field.

Sample

1. Start Visual Studio .NET, and then create a new Visual Basic Windows Application project:
   1. On the File menu, point to New, and then click Project.
   2. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Windows Application under Templates.
2. Drag a Button onto the form from the Windows Forms toolbox.
3. At the top of the code window, add the following line:

   Imports System.Data.SqlClient

4. Paste the following code onto the Button's click event:

   Dim ds As New DataSet()
   
   Dim cnNorthwind As New SqlConnection("server=(local);integrated security=sspi;database=Northwind")
   Dim cmSelect As New SqlCommand("select employeeid,firstname,lastname from employees", cnNorthwind)
   
   Dim stInsert As String
   stInsert = "insert into employees (firstname,lastname) values(@Firstname,@Lastname);select employeeid,firstname,lastname from employees where employeeID = @@identity"
   Dim cmInsert As New SqlCommand()
   
   With cmInsert
      .CommandText = stInsert
      .CommandType = CommandType.Text
      .Connection = cnNorthwind
      .Parameters.Add(New SqlParameter("@firstname", Data.SqlDbType.VarChar, 25, "firstname"))
      .Parameters.Add(New SqlParameter("@lastname", Data.SqlDbType.VarChar, 25, "Lastname"))
   End With
   
   Dim daNorthwind As New SqlDataAdapter()
   With daNorthwind
      .SelectCommand = cmSelect
      .InsertCommand = cmInsert
   End With
   
   daNorthwind.Fill(ds, "employees")
   Dim dr As DataRow
   dr = ds.Tables("employees").NewRow
   dr(1) = "John"
   dr(2) = "Doe"
   ds.Tables("employees").Rows.Add(dr)
           
   daNorthwind.Update(ds, "employees")
   ds.AcceptChanges()
   Dim i As Int16
   For i = 0 To ds.Tables("Employees").Rows.Count - 1
       With ds.Tables("Employees")
          Debug.WriteLine("EmployeeID: " & .Rows(i)(0).ToString)
          Debug.WriteLine("Employee Firstname: " & .Rows(i)(1).ToString)
          Debug.WriteLine("Employee LastName: " & .Rows(i)(2).ToString)
       End With
   Next i
   					

5. Change the connection string to reflect your SQL Server or MSDE information.
   
6. Run the application.
   
7. Click the Button.
   
   The information for the identity field of the newly inserted record is displayed.

Troubleshooting

Before you use this procedure, with MSDE you must use Data Transformation Services (DTS) to import the Northwind database for SQL Server or Microsoft Access.

For additional information about returning the identity value for child records, click the article numbers below to view the articles in the Microsoft Knowledge Base: For additional information about how to use DTS, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about converting a Microsoft Access database to SQL Server, click the following article number to view the article in the Microsoft Knowledge Base: For additional information about how to retrieve identity columns in Visual Basic 6.0, click the following article number to view the article in the Microsoft Knowledge Base: