vb.net sql last inserted ID

sql vb.net ms-access lastinsertid
Elliott picture Elliott · Dec 13, 2010 · Viewed 16.1k times · Source

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

Answer

Pauli Østerø picture Pauli Østerø · Dec 13, 2010

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.

Related questions

SQL INSERT INTO statement for MS Access

I am trying to learn how to use MS Access with my VB.net program. I am practicing learning how to use the INSERT INTO statement but I keep getting an error. Here is my code: Imports System.Data.OleDb …

Read More
How do I create a parameterized SQL query? Why Should I?

I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input. How do you do this? Do you get this automatically when using stored procedures? So my …

Read More
Two foreign keys referencing the same primary key

Is this okay to have two foreign keys in one table referencing one primary key of other table? EmployeeID is a primary key in the employee table and appearing as a foreign key twice in the timesheet table. There will …

Read More