CARA: Mengambil nilai identitas saat memasukkan data ke dalam Access Database dengan menggunakan Visual Basic.NET

Tersambung ke Access Database

1. Mulai Microsoft Visual Studio.NET 2002.
2. Pada Berkas menu, titikBaru, lalu klik Project.
3. Di bawah Jenis proyek, klik Visual Dasar proyek. Di bawah Pola acu KlikAplikasi konsol. Secara default, Module1.vb adalah dibuat.
4. Nama proyek MyJetApplication kemudian klik Oke.
5. Mengganti kode yang ada dengan kode berikut:

   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. Mengubah nama sumber Data dalam rangkaian sambungan untuk Arahkan ke Database akses Anda.

Perangkap identitas kolom nilai

1. Tambahkan kode berikut sebelum Utama metode untuk membuat kedua OleDbCommand objek untuk PILIH @@ IDENTITAS permintaan:

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

2. Menambahkan kode berikut untuk Utama metode untuk membuat contoh yang baru OleDbCommand kelas:

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

3. Menambahkan kode berikut untuk Utama metode untuk penanganan RowUpdated acara:

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

4. Menambahkan kode berikut untuk Utama metode untuk meng-update data. The RowUpdated acara dibangkitkan setelah panggilan Pemutakhiran metode.

         ' Update the Data
         oleDa.Update(dtTest)
   

5. Menambahkan kode berikut untuk Utama metode untuk menjatuhkan AutoIncrementTest tabel dan melepaskan sumber daya:

         ' 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. Tambahkan berikut RowUpdated event handler kode untuk 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. Pada Debug menu, klikMulai untuk menjalankan aplikasi. Nilai-nilai kolom identitas ditampilkan di jendela Output.

Daftar kode lengkap

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

Kembali ke atas | Berikan Masukan