微科社区,轻松开发从此开始! 请登陆 免费注册

微科社区

当前位置:首页 > 数据库 > Access >

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

时间:2017-02-20 04:01  浏览:努力统计中...
原由 有些数据需要插入Access,但是因为Access里面的key是自增的。所以如果后期还想再删除或者怎么样操作,没有key来定位该条记录。 怎么样能在插入的同时,把自增的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
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线------