COMO: Recuperar o valor de identidade ao inserir registros no banco de dados Access usando o Visual Basic .NET

Se conectar ao banco de dados do Access

1. Inicie o Microsoft Visual Studio .NET 2002.
2. No menu arquivo , aponte para novo e, em seguida, clique em Project .
3. Em Project Types , clique em Projetos do Visual Basic . Na seção modelos , clique em Aplicativo de console . Por padrão, Module1.vb é criado.
4. Nome do projeto MyJetApplication e clique em OK .
5. Substitua o código existente com o código a seguir:

   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. Modifique o nome de fonte de dados na seqüência de conexão para apontar para o seu banco de dados Access.

Ajusta o registro o valor de coluna de identidade

1. Adicione o seguinte código antes do método Main para criar um segundo objeto OleDbCommand para a consulta SELECT @@ IDENTITY :

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

2. Acrescentar o código a seguir ao método Main para criar uma nova instância da classe OleDbCommand :

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

3. Acrescentar o código a seguir ao método Main para manipular o evento RowUpdated :

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

4. Acrescente o código a seguir ao método Main para atualizar os dados. O evento RowUpdated é gerado após chamar o método Update .

         ' Update the Data
         oleDa.Update(dtTest)
   

5. Acrescentar o código a seguir ao método Main para descartar a tabela AutoIncrementTest e liberar os recursos:

         ' 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. Adicione o seguinte código de manipulador de evento RowUpdated ao 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. No menu Debug , clique em Iniciar para executar o aplicativo. Valores de coluna de identidade são exibidos na janela Output.

Concluir a listagem de código

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

Voltar para o início | Submeter comentários