Getting the autonumbers value in VB.NET 2008

[projecttoday]

I'm trying to read back the autonumber of the last record which was inserted by my program into an Access database using VB.NET 2008 Express.

This is the table adaptor save code:

Me.Validate()
Me.TblTablenameBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.DatabasenameDataSet)

I need to follow this up with code to get the autonumber primary key of the record that was created by the code. If another user creates a record at the same time, it should not return that value but rather the correct one. The SQL that is used to get the last autonumber is SELECT @@IDENTITY but how would you do this when you're using a table adaptor?

Robert

[ShiftySid]

Not sure if this works in Access, but here's a bit of code I use in SQL which works quite well:

cmd.Connection.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT MAX(ID) FROM Customers_table"
 
Dim intMaxID As Integer = CInt(cmd.ExecuteScalar)
MessageBox.Show("New record ID is " & CStr(intMaxID), "New record added", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
cmd.Connection.Close()

[projecttoday]

MAX(ID) will return the highest id. With mulitple users?

[jmcilhinney]

There really is no way to do what you want with Access. If you use SQL Server, or some other real RDBMS, then you can write multiple SQL statements in the one DbCommand but Access/Jet doesn't support that. As such you have to execute two separate DbCommands and there will always be a chance that someone else will have inserted another new record. This is one of the drawbacks of using Access, particularly in a multi-user environment.

[projecttoday]

I'm surprised to hear that. You could always use your own unique key.

[jmcilhinney]

I'm surprised to hear that. You could always use your own unique key.

You'd still have the same issue if the values were being created in the database.

If you want to stick with Access then I guess your options would be:

1. Create a stored procedure (or whatever Access calls them) and then, as far as I'm aware, you can have multiple SQL statements. You could then get the new ID immediately after generating it inside the sproc and then either return it or assign it to an output parameter. I've never used sprocs in Access but I assume that this should be possible.

2. Use GUIDs as your primary key and generate them in the application rather than in the database.

[projecttoday]

Read and lock the record in the counter table that contains the counter. Add 1 to the counter. This is your new key value. Update the counter record with the new key value. Then unlock the counter record. Make your new transaction record with this key value. Would work with any database.