HOW TO: Retrieve an Identity Value from a Newly Inserted Record from SQL Server by Using Visual Basic .NET (320141)
The information in this article applies to:
- Microsoft ADO.NET (included with the .NET Framework 1.1)
- Microsoft ADO.NET (included with the .NET Framework) 1.0
- Microsoft Visual Basic .NET (2003)
- Microsoft Visual Basic .NET (2002)
- Microsoft SQL Server 7.0
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 2000 64 bit (all editions)
- Microsoft Data Engine (MSDE) 1.0
This article was previously published under Q320141 For a Microsoft Visual C# .NET version of this
article, see
320897. IN THIS TASKSUMMARY 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.
back to the top
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.
back to the top
Sample- Start Visual Studio .NET, and then create a new Visual
Basic Windows Application project:
- On the File menu, point to New, and then click Project.
- In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Windows Application under Templates.
- Drag a Button onto the form from the Windows Forms
toolbox.
- At the top of the code window, add the following line:
Imports System.Data.SqlClient - 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
- Change the connection string to reflect your SQL Server or
MSDE information.
- Run the application.
- Click the Button.
The information for the
identity field of the newly inserted record is displayed.
back to the top
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.
back to the top
REFERENCESFor additional information about returning the identity
value for child records, click the article numbers below to view the articles
in the Microsoft Knowledge Base: 320301 HOWTO: Update Parent-Child Data with Identity column from a Windows Forms application via a Web Service
For additional information about how to use DTS,
click the article number below to view the article in the Microsoft Knowledge
Base: 242377 How to Use Data Transformation Services (DTS)
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:
237980
HOW TO: Convert an Access Database to SQL Server
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: 170147
HOWTO: Retrieve Identity Column After Insert Using RDO
back to the top
Modification Type: | Minor | Last Reviewed: | 12/26/2003 |
---|
Keywords: | kbHOWTOmaster KB320141 kbAudDeveloper |
---|
|