Elliott Elliott - 2 months ago 37
SQL Question

vb.net sql last inserted ID

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()
Dim sqlCommand As OleDbCommand = New OleDbCommand(String.Format(insertSql), con)

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 :).


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.


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

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..