vb.net sql最后插入的ID

发布于 2024-10-07 14:19:22 字数 714 浏览 3 评论 0原文

我正在将 VB.NET 与 Access 数据库一起使用，我插入值，但随后我需要获取最后插入的 ID（自动编号）并将其插入到相关表中。

我已经尝试过 @@IDENTITY 和 MAX(column) 但 @@IDENTITY 返回零并且 MAX 不太可靠（有时插入数据很慢，因此在插入数据之前获取 ID）。

Dim insertSql = datalayer.getDataTable((String.Format("INSERT INTO users (username) VALUES ({0})", username)))

Dim newID = datalayer.getDataTable((String.Format("SELECT @@IDENTITY FROM users")))


Dim con As OleDbConnection = getConnection()
con.Open()
Dim sqlCommand As OleDbCommand = New OleDbCommand(String.Format(insertSql), con)
sqlCommand.ExecuteNonQuery()

这是在两个函数中完成的，因此上面的代码可能看起来很混乱，但这只是从两个函数中获取的。两条语句均被执行，但我仅以执行一条语句为例。

有没有 @@IDENTITY 和 MAX 的替代方案，因为我似乎不明白 @@IDENTITY 出了什么问题？

感谢您的任何建议:)。

原文

i'm using VB.NET with an Access Database, I insert values but then I need to get the last inserted ID (auto number) and insert that into a related table.

I have tried @@IDENTITY and MAX(column) but @@IDENTITY returns zero and MAX isn't very dependable (sometimes slow to insert data, so get the ID before the inserted one).

Dim insertSql = datalayer.getDataTable((String.Format("INSERT INTO users (username) VALUES ({0})", username)))

Dim newID = datalayer.getDataTable((String.Format("SELECT @@IDENTITY FROM users")))


Dim con As OleDbConnection = getConnection()
con.Open()
Dim sqlCommand As OleDbCommand = New OleDbCommand(String.Format(insertSql), con)
sqlCommand.ExecuteNonQuery()

This is done in two functions so the above code might look confusing, but thats just taken from the two functions. The two statements are executed, but I have just shown one being executed as an example.

Is there and alternative to @@IDENTITY and MAX, as I carn't seem to see how am going wrong with @@IDENTITY?

Thanks for any advice :).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忘羡 2024-10-14 14:19:22

与插入操作在同一连接（和事务）上执行 SELECT @@IDENTITY 相比，这绝对是至关重要的。如果您的 getDataTable() 方法为每个调用创建一个新连接，那么这就是它不起作用的原因。

更新

另一种更好的方法是在一个

sql = "INSERT INTO...;SELECT @@IDENTITY..." 
Dim id = sqlCommand.ExecuteScalar(sql)

再次更新

中执行两个语句似乎您无法对 MS Access 数据库执行这样的多个函数，在一个操作中运行多个 SQL 语句。

Its is absolutely crucial than SELECT @@IDENTITY is executed on the same connection (and transaction) than the insert. If your getDataTable() method creates a new connection for each call, then that is why its not working.

Update

Another approach which is preferable is to execute the two statements in one

sql = "INSERT INTO...;SELECT @@IDENTITY..." 
Dim id = sqlCommand.ExecuteScalar(sql)

Update again

It seems like you can't execute multiple functions like this against a MS Access database, Running multiple SQL statements in the one operation.

回复收藏 0 原文

享受孤独 2024-10-14 14:19:22

您可以简单地使用以下代码。
我假设您使用了 SQLDataSource 并且事件是：

Protected Sub SqlDataSource1_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New OleDbCommand(query, CType(e.Command.Connection,   OleDbConnection))
Dim newid As Integer = cmd.ExecuteScalar()
l1.Text = newid
End Sub

You can simply use the following code.
I assume that you used SQLDataSource and the event is :

Protected Sub SqlDataSource1_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New OleDbCommand(query, CType(e.Command.Connection,   OleDbConnection))
Dim newid As Integer = cmd.ExecuteScalar()
l1.Text = newid
End Sub

回复收藏 0 原文

~没有更多了~