vb.net sql last inserted ID
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.
Comments
-
Elliott almost 2 years
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 :).
-
Elliott over 13 yearsHi, yes it doesnt create a new connection. If I just made one connection on form load to the database, would that work for insert, update etc too? Thanks
-
Will Marcouiller over 13 years+1 For providing a way with the
ExecuteScalar
method, wrapping the two SQL statements. That, I would have never thought of it. =) -
Fionnuala over 13 yearsI very much doubt you can execute two statements in one with Access.
-
Pauli Østerø over 13 years@Remou seems like your right stackoverflow.com/questions/770614/…
-
David-W-Fenton over 13 yearsSeems to me that this answer should have everything after the first paragraph edited out, since the rest of it is not applicable.
-
David-W-Fenton over 13 yearsOn another note, with the ACE that ships with A2010 now supporting table-level data macros that can behave like triggers, I worry that SELECT @@IDENTITY could return incorrect results in cases where the main insert triggers an insert in another table with an Autonumber field. What is needed is the equivalent of SQL Server's Scope_Identity(), but so far as I know, it doesn't exist in A2010/ACE. I've been asking about this in various places and nobody seems to know the answer.