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

最新推荐文章于 2024-03-04 14:51:58 发布

转载最新推荐文章于 2024-03-04 14:51:58 发布 · 809 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/huangxue/archive/2011/12/27/2303127.html

文章标签：

#数据库

本文讨论了如何在VB.NET中插入数据到Access数据库并获取自增Key的方法，通过自定义事件处理实现了关键信息的跟踪。

原由

有些数据需要插入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