ACCESS插入数据同时返回自增ID

原由

有些数据需要插入Access，但是因为Access里面的key是自增的。所以如果后期还想再删除或者怎么样操作，没有key来定位该条记录。

怎么样能在插入的同时，把自增的key返回，就是这里需要探讨的。

先上代码

    Private cmdGetIdentity As OleDbCommand
    Private pid As String

    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
            pid = cmdGetIdentity.ExecuteScalar().ToString()

            e.Row.AcceptChanges()
        End If
    End Sub
 

    Protected Sub insertAccess()
        pid = String.Empty

        Dim cn As OleDbConnection = Nothing
        Dim dbTran As OleDbTransaction = Nothing

        Dim cmd As OleDbCommand = New OleDbCommand()
        cmdGetIdentity = New OleDbCommand()
        Try

            Dim cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=access.mdb;Jet OLEDB:Database Password=PSW"   
            cn = New OleDbConnection(cnstr)
            cn.Open()

            'Begin tran
            dbTran = cn.BeginTransaction()
            Dim oleDa As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM TB1", cn)
            oleDa.SelectCommand.Transaction = dbTran
            Dim dt As DataTable = New DataTable()
            oleDa.Fill(dt)


            cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
            cmdGetIdentity.Connection = cn
            cmdGetIdentity.Transaction = dbTran

            AddHandler oleDa.RowUpdated, AddressOf HandleRowUpdated
            Dim dr As DataRow
                 dr = dt.NewRow()
                Dim sqlstr As String
                sqlstr = " INSERT INTO TB1" & vbCrLf
                sqlstr &= " (ACCESS_FIELD1)" & vbCrLf
                sqlstr &= "VALUES" & vbCrLf
                sqlstr &= "(@FIELD)" & vbCrLf

                cmd = New OleDbCommand()

                cmd.CommandText = sqlstr
                cmd.Parameters.Add("@FIELD", OleDbType.VarChar).Value ="TEST"
              
                cmd.Connection = cn
                cmd.Transaction = dbTran

                dr("FILED") = "TEST"
                dt.Rows.Add(dr)
                cmd.Connection = cn
                cmd.Transaction = dbTran

                oleDa.InsertCommand = cmd
                oleDa.Update(dt)
                dt.Rows.Clear()


                'Resolve out business with pid

           


            objDbAccess.ExecuteSQL(updateSQL)
            dbTran.Commit()

        Catch ex As Exception
            If Not dbTran Is Nothing Then
                dbTran.Rollback()
            End If
                    Finally
            If Not cn Is Nothing Then
                cn.Close()
                cn.Dispose()
                cn = Nothing
            End If
            If Not cmd Is Nothing Then
                cmd.Dispose()
            End If
            If Not cmd Is Nothing Then
                cmdGetIdentity.Dispose()
            End If

        End Try

    End Sub

说明，主要是通过一个Adapter让Access里面的表和我们VB.NET里面的表关联。然后表.Update()就自动更新数据了。

然后通过添加一个行更新事件，在表更新时，把当前行的自增ID分配给pid。这样在注释的地方就可以使用pid这个字段去完成别的操作了。