ÏÐÀÊÒÈ×ÅÑÊÎÅ ðóêîâîäñòâî: Èçâëå÷ü çíà÷åíèå èäåíòèôèêàòîðà ïðè âñòàâêå çàïèñè â áàçó äàííûõ Access Ñ ïîìîùüþ Visual Basic.NET

Ïîäêëþ÷åíèå ê áàçå äàííûõ Access

1. Çàïóñòèòå Microsoft Visual Studio.NET 2002.
2. Íà Ôàéë Âûáåðèòå ïóíêòÍîâûé, à çàòåì íàæìèòå êíîïêó Ïðîåêò.
3.  ãðóïïå Òèïû ïðîåêòîâ, íàæìèòå êíîïêó Visual Îñíîâíûå ïðîåêòû.  ãðóïïå Øàáëîíû ðàçäåë, íàæìèòå êíîïêóÊîíñîëüíîå ïðèëîæåíèå. Ïî óìîë÷àíèþ èñïîëüçóåòñÿ ìîäóëü Module1.vb ñîçäàí.
4. Èìÿ ïðîåêòà MyJetApplication è âûáåðèòå êîìàíäó ÎÊ.
5. Çàìåíèòå ñóùåñòâóþùèé êîä ñëåäóþùèì êîäîì:

   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. Èçìåíèòå èìÿ èñòî÷íèêà äàííûõ â ñòðîêó ïîäêëþ÷åíèÿ Óêàæèòå áàçó äàííûõ Access.

Çíà÷åíèå ñòîëáöà èäåíòèôèêàòîðîâ ïðè òðåïïèíãå

1. Äîáàâüòå ñëåäóþùèé êîä ïåðåä Îñíîâíîé ìåòîä äëÿ ñîçäàíèÿ âòîðîãî Îáúåêò OleDbCommand îáúåêò äëÿ SELECT @@ IDENTITY çàïðîñ:

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

2. Äîáàâüòå ñëåäóþùèé êîä äëÿ Îñíîâíîé ìåòîä ñîçäàíèÿ íîâîãî ýêçåìïëÿðà Îáúåêò OleDbCommand êëàññ:

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

3. Äîáàâüòå ñëåäóþùèé êîä äëÿ Îñíîâíîé ìåòîä äëÿ îáðàáîòêè RowUpdated ñîáûòèå:

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

4. Äîáàâüòå ñëåäóþùèé êîä äëÿ Îñíîâíîé ìåòîä äëÿ îáíîâëåíèÿ äàííûõ. Â RowUpdated ñîáûòèå âûçûâàåòñÿ ïîñëå âûçîâà ìåòîäà Îáíîâëåíèå ìåòîä.

         ' Update the Data
         oleDa.Update(dtTest)
   

5. Äîáàâüòå ñëåäóþùèé êîä äëÿ Îñíîâíîé ìåòîä ïåðåòàñêèâàíèÿ AutoIncrementTest Òàáëèöà è îñâîáîäèòü ðåñóðñû:

         ' 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. Äîáàâüòå ñëåäóþùèå ñòðîêè RowUpdated êîä îáðàáîò÷èêà ñîáûòèé ê 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. Íà Îòëàäêà ìåíþ, íàæìèòå êíîïêóÍà÷àëî äëÿ çàïóñêà ïðèëîæåíèÿ. Çíà÷åíèÿ ñòîëáöà èäåíòèôèêàòîðîâ îòîáðàæàåòñÿ â îêíå âûâîäà.

Ïîëíûé ïðèìåð êîäà

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

Ïåðåéòè ê íà÷àëó ñòðàíèöû | Îòïðàâèòü îòçûâ