HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET

For a Microsoft Visual C# .NET version of this article, see
816112 .

IN THIS TASK

Summary

This step-by-step article discusses how to retrieve the identity column value from an Access database.

Retrieving the Identity value from a Jet database is different from that of SQL Server, because a Jet database does not support multi-statement batch commands. The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field that is generated on your connection. To run the SELECT @@Identity query, it is recommended that you use another OleDbCommand object. This article describes how to use a second OleDbCommand to retrieve the Identity column value.

Note: This feature works only with Microsoft Jet OLEDB 4.0 databases. The earlier versions of Microsoft Jet OLEDB do not support this feature.

back to the top

More Information

Connect to the Access Database

To connect to the Access Database and create a table with the Identity column, follow these steps:
  1. Start Microsoft Visual Studio .NET 2002.
  2. On the File menu, point to
    New, and then click Project.
  3. Under Project Types, click Visual Basic Projects. Under Templates section, click
    Console Application. By default, Module1.vb is created.
  4. Name the project MyJetApplication and then click OK.
  5. Replace the existing code with the following code:
    Imports System
    Imports System.Data
    Imports System.Data.OleDb

    Module Module1

    Sub Main()

    ' Open Connection
    Dim cnJetDB As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourAccessDatabase")
    cnJetDB.Open()

    ' If the test table does not exist, create the Table.
    Dim strSQL As String
    strSQL = "CREATE TABLE AutoIncrementTest " & _
    "(ID int identity, Description varchar(40), " & _
    "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"

    ' Command for creating Table.
    Dim cmdJetDB As New OleDbCommand(strSQL, cnJetDB)
    cmdJetDB.ExecuteNonQuery()

    ' Create a DataAdaptor With Insert Command For inserting records
    Dim oleDa As New OleDbDataAdapter("Select * from AutoIncrementTest", cnJetDB)


    ' Command to Insert Records.
    Dim cmdInsert As New OleDbCommand()
    cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)"
    cmdInsert.Connection = cnJetDB
    cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description"))
    oleDa.InsertCommand = cmdInsert

    ' Create a DataTable
    Dim dtTest As New DataTable()
    oleDa.Fill(dtTest)

    Dim drTest As DataRow

    ' Add Rows to the Table
    drTest = dtTest.NewRow
    drTest("Description") = "This is a Test Row 1"
    dtTest.Rows.Add(drTest)

    drTest = dtTest.NewRow
    drTest("Description") = "This is a Test Row 2"
    dtTest.Rows.Add(drTest)

    End Sub

    End Module
  6. Modify the Data Source name in the connection string to point to your Access Database.


back to the top

Trap the Identity Column Value

To summarize the steps, in the RowUpdated event of the DataAdapter, you can trap the identity column value that is generated for a column of a Table in an Access Database. In the RowUpdated event, you will run the SELECT @@IDENTITY query by using another Command object, and then you will assign the value that is returned by the query to the Identity column. Lastly, call the AcceptChanges method of the DataRow object to accept the column value.

The trap the identity column value, follow these steps:
  1. Add the following code before the Main method to create a second OleDbCommand object for the SELECT @@IDENTITY query:
       ' Create OleDbCommand for SELECT @@IDENTITY statement
    Private cmdGetIdentity As OleDbCommand
  2. Append the following code to the Main method for creating a new instance of OleDbCommand class:
          ' Create another command to get IDENTITY value.
    cmdGetIdentity = New OleDbCommand()
    cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
    cmdGetIdentity.Connection = cnJetDB

  3. Append the following code to the Main method for handling the RowUpdated event:
          ' Delegate for handling RowUpdated event.
    AddHandler oleDa.RowUpdated, AddressOf HandleRowUpdated
  4. Append the following code to the Main method for updating the data. The RowUpdated event is raised after calling the Update method.
          ' Update the Data
    oleDa.Update(dtTest)
  5. Append the following code to the Main method to drop the AutoIncrementTest table and release the resources:
          ' Drop the table
    cmdJetDB.CommandText = "DROP TABLE AutoIncrementTest"
    cmdJetDB.ExecuteNonQuery()

    ' Release the resources.
    cmdGetIdentity.Dispose()
    cmdGetIdentity = Nothing
    cmdInsert.Dispose()
    cmdInsert = Nothing
    cmdJetDB.Dispose()
    cmdJetDB = Nothing
    cnJetDB.Close()
    cnJetDB.Dispose()
    cnJetDB = Nothing
  6. Add the following RowUpdated event handler code to Module1:
       ' Event handler for RowUpdated event.
    Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
    If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then
    ' Get the Identity column value
    e.Row("ID") = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())
    Debug.WriteLine(e.Row("ID"))
    e.Row.AcceptChanges()
    End If
    End Sub
  7. On the Debug menu, click
    Start to run the application. Identity column values are displayed in the Output window.
back to the top

Complete Code Listing

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1

' Create OleDbCommand for SELECT @@IDENTITY statement
Private cmdGetIdentity As OleDbCommand

Sub Main()

' Open Connection
Dim cnJetDB As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourAccessDatabase")
cnJetDB.Open()

' If the test table does not exist then create the Table
Dim strSQL As String
strSQL = "CREATE TABLE AutoIncrementTest " & _
"(ID int identity, Description varchar(40), " & _
"CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"

' Command for Creating Table
Dim cmdJetDB As New OleDbCommand(strSQL, cnJetDB)
cmdJetDB.ExecuteNonQuery()

' Create a DataAdaptor With Insert Command For inserting records
Dim oleDa As New OleDbDataAdapter("Select * from AutoIncrementTest", cnJetDB)


' Command to Insert Records
Dim cmdInsert As New OleDbCommand()
cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)"
cmdInsert.Connection = cnJetDB
cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description"))
oleDa.InsertCommand = cmdInsert

' Create a DataTable
Dim dtTest As New DataTable()
oleDa.Fill(dtTest)

Dim drTest As DataRow

' Add Rows to the Table
drTest = dtTest.NewRow
drTest("Description") = "This is a Test Row 1"
dtTest.Rows.Add(drTest)

drTest = dtTest.NewRow
drTest("Description") = "This is a Test Row 2"
dtTest.Rows.Add(drTest)

' Create another Command to get IDENTITY Value
cmdGetIdentity = New OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = cnJetDB

' Delegate for Handling RowUpdated event
AddHandler oleDa.RowUpdated, AddressOf HandleRowUpdated

' Update the Data
oleDa.Update(dtTest)

' Drop the table
cmdJetDB.CommandText = "DROP TABLE AutoIncrementTest"
cmdJetDB.ExecuteNonQuery()

' Release the Resources
cmdGetIdentity.Dispose()
cmdGetIdentity = Nothing
cmdInsert.Dispose()
cmdInsert = Nothing
cmdJetDB.Dispose()
cmdJetDB = Nothing
cnJetDB.Close()
cnJetDB.Dispose()
cnJetDB = Nothing
End Sub

' Event Handler for RowUpdated Event
Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then
' Get the Identity column value
e.Row("ID") = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())
Debug.WriteLine(e.Row("ID"))
e.Row.AcceptChanges()
End If
End Sub
End Module

References

For additional information about a related topic in Visual Basic 6.0, click the following article number to view the article in the Microsoft Knowledge Base:
232144 INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
For more information, visit the following MSDN Web site:back to the topBUG #: 2835 (Content Maintenance)
Propriedades

ID do Artigo: 815629 - Última Revisão: 23 de mar de 2009 - Revisão: 1

Comentários