Retrieve Access AutoNumber Value After Insert

[jmcilhinney]

C# version here.

People are often asking how to retrieve the value from an AutoNumber column in an Access database after a record has been inserted. This is particularly important when working with related data, where you need to get the ID of a parent record to add to a child record. While doing this with an ADO.NET provider that accepts multiple SQL statements per batch, e.g. SqlClient, is simple, I've previously said that this cannot be done with Access and the Jet OLE DB provider without simply requerying the database. I've recently discovered that this is not the case, so I decided to create a demo project to illustrate how it can be done.

The key is the RowUpdated event of the OleDbDataAdapter. It is raised each time the adapter sends a row to the database to be saved, whether that be for an insert, update or delete. We can handle this event and detect an insert, then query the database to get the last generated ID. Because the the event is raised immediately after a row is saved, we can get the ID for multiple inserted rows and each will be correct at the time.

The attached solution was created in VS 2010, so it will require that or VB 2010 Express to open. If you don't already have either and don't want to install either, you can still at least look at the code in an older version. The required MDB file is included.

Pay most attention to the way the PK columns of each DataTable are configured to generate local IDs and how the RowUpdated events are used to retrieve the database IDs to replace them.

EDIT: I have added a second project that does basically the same thing but with a typed DataSet. I had to configure the relation in the DataSet designer to enforce a foreign key constraint and cascade updates. I also added the GetLastAutoNumber query to each table adapter. I originally tried using an extra table adapter with a single GetLastAutoNumber query so as not to duplicate. That didn't work though, because the insert was done inside a transaction and the query then could not see the new ID. By using the same table adapter for the insert and the query, the same connection and the same transaction is used.

[NickThissen]

This seems useful! I haven't tried to use it in my own code yet, but it seems to be what I need. I am usually manually running the SELECT @@IDENTITY command after an insert, but for one application I am using the dataset designer and it does not allow me to create that command (it's not recognized as a valid SQL statement I guess). I even tried getting to the underlying OleDbConnection and running the command on that but it always returns 0 for some reason. I hope using the RowUpdated event it will give me the ID instead

[jmcilhinney]

Here's another version of the solution reworked to use a Data Source and typed DataSet. A lot of the code has gone because most of the configuration is now done in the DataSet designer. Note that I had to edit the relation in the designer to enforce a foreign key constraint and cascade updates. I also was not able to use a custom table adapter to retrieve the IDs. I still had to create my own command because it had to use the same transaction, and therefore the same connection, as the adapter that inserted the row.

Attachment removed. See edit in post #1.

[NickThissen]

Looks good. Correct me if I'm wrong, you are not using the 'QueriesTableAdapter', are you? I see you were able to enter the SELECT @@IDENTITY query though, I never managed that. It does warn that it can't parse the SQL but it still lets you finish, it didn't do that for me... But I wasn't using a separate table adapter for it (I tried to get the query in the same table adapter, one for every table), perhaps that was the problem...

[jmcilhinney]

Looks good. Correct me if I'm wrong, you are not using the 'QueriesTableAdapter', are you? I see you were able to enter the SELECT @@IDENTITY query though, I never managed that. It does warn that it can't parse the SQL but it still lets you finish, it didn't do that for me... But I wasn't using a separate table adapter for it (I tried to get the query in the same table adapter, one for every table), perhaps that was the problem...

I created that QueriesTableAdapter with a view to using it to retrieve the last ID. As you say, it did warn that the SQL couldn't be parsed but it did allow me to finish and the query did, in theory, work. The problem was, updating with the adapters used a transaction and the QueriesTableAdapter would create its own connection. The query would thus execute outside the transaction and therefore was unable to see the ID generated inside. I went back to a manual command but I neglected to remove that adapter.

Maybe a query per table adapter would have executed within the same transaction, although doesn't seem right to use the same query in multiple adapters. I might have a bit closer look on the weekend. I just threw that together when I finished work so it might not be optimal.

[jmcilhinney]

Nick, I tried again and this time I added the same query to both table adapters. It still warned me that the SQL could not be parsed but it let me finish and it works. I'm now using the table adapter in the RowUpdated event handler. Because it's the same table adapter that did the insert, the query is executed inside the same transaction so the new ID is available. See post #1 for a new project.

[wes4dbt]

jmc,

I download and installed the Type Dataset demo. It works great. I was able to add a new form and use your dataset to mimic what how your form performed.

I ran into problems when I tried to add a new datasource and modify it to work like yours.

I cut and pasted the "GetLastautonumber" functions. It was giving me an invalid index error in "parentAdapter_RowUpdated" when it call "GetLastAutoNumber" . I added this code to each tableadapter,

            Me._commandCollection(1) = New Global.System.Data.OleDb.OleDbCommand()
            Me._commandCollection(1).Connection = Me.Connection
            Me._commandCollection(1).CommandText = "SELECT @@IDENTITY"
            Me._commandCollection(1).CommandType = Global.System.Data.CommandType.Text

and increased the _commandcollection array, like it was in your dataset,

Me._commandCollection = New Global.System.Data.OleDb.OleDbCommand(1) {}

This solved the index error but then I got a casting error on this line,

DirectCast(e.Row, ParentChildDataSet.ParentRow).ParentID = lastAutoNumber

Am I on the right track with my approach or have I missing something important. I made the mistake of modifying the relationship in the dataset designer and it removed all the code I had added.

So I just want to no if I was headed in the right direction and what other code need to be added to the dataset designer file before I start over again.

thanks

[jmcilhinney]

If you're using a typed DataSet then you shouldn't be adding any code to the table adapters manually. I added the extra query to each table adapter in the DataSet designer.

[wes4dbt]

jmc,
Thanks, I was way off. Do I add the query like "SELECT @@IDENTITY", is that all it takes? I'll work on that tomorrow.

Thanks again

[jmcilhinney]

jmc,
Thanks, I was way off. Do I add the query like "SELECT @@IDENTITY", is that all it takes? I'll work on that tomorrow.

Thanks again

Yep. Right-click on the table adapter and select Add Query or the like and then follow the prompts. You may well be told that the SQL code couldn't be parsed but press on anyway.

[maneeshmassey]

Hi jmcilhinney,

I am a newbie to VB.NET.

I have an Access 2007 database which has 5 tables related to one another in a Parent-Child relationship. I have an application, created using Visual Basic 2008 Express Edition in which I enter details of a pastor into text boxes and 2 ComboBoxes, one for selecting District and the other for selecting Block (sub-district) and a CheckedListBox called VillageChkListBox1 on a form.

The user enters details of a pastor into the TextBoxes and chooses a District from the District ComboBox which filters the Block ComboBox to display only Blocks (sub-districts) based on the selection from the District ComboBox. Based on the selection made in the Block ComboBox, the CheckedListBox is populated with the list of villages which the user can check mark for the particular pastor.

I use the BindingNavigator bar at the top of the form to save the details entered into the database. The user presses the, '+' sign to do all the adds and the floppy sign to do all the saves.

The new pastor details entered into the textboxes are saved in the Pastor table of the database and the villages selected from the CheckedListBox goes into a separate table called the PastorVillage table.

The PastorVillage table has PastVillID(Auto-number field), PastorID, VillID among other fields in the database table.

Beth Massi has shown how to retrieve the ParentID in a similar manner as yours, (http://blogs.msdn.com/b/bethmassi/ar...-database.aspx) BUT, this saves the Parent into a separate table and the Child into a separate table. However this is not what I want.

My question is, how can I retrieve the PastorID from the Pastor table and the checked villages from the CheckedListBox and DUMP BOTH of them by adding rows to the PastorVillage table in Access ??
Is this possible ???

Can you please help me out ?
Regards,
~~Maneesh

[jmcilhinney]

You're situation is exactly the same, except that there are two parent tables and one child table. There are two relationships so you simply do the same thing twice. Insert the two parent records and get the generated IDs for those two records and put them into the child record.

[maneeshmassey]

Can you please be more descriptive ??? It would be very helpful for a newbie like me if you explained what you exactly mean to say in a little bit more detail so that I am comfortable with catching every word and working towards the right direction to find the solution I am looking for.

Appreciate you very much !
Thanks.
~~Maneesh

[jmcilhinney]

All you need to do is follow the example that I have already provided. You're trying to make this difficult when it's not. I've shown you what to do already. If you have two parent IDs to retrieve then you simply do it twice. That's all.