Retrieve Access AutoNumber Value After Insert

VB 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 C# 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.

N.B. - This solution was generated from the original VB version by Instant C# by Tangible Software Solutions.

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.

I've added a second project to post #1 that uses a typed DataSet instead of manually created ADO.NET objects.

I come from the 'old school' in a time without Windows and for a long time now I'm trying to do something with de object oriented programming stuff.
In the past I have played with Borland's C++Builder 5 but I think that this is to old for using it with Windows XP or Windows 7.


I have the book 'Visual Studio C# 2010 Programming and PC interfacing" by John Allwork and was trying to make a connection with an Access database. Why Access, my wife is good with Access... It worked until I had the parent child problem.
Now the solution and example provided by you is very much appreciated. I had found a lot of solutions on the internet but with your example it was rather easy to compare it with my program. I think I have made all the changes in my litte program but I am struggling with one thing.

There is an xxxDataSetDesigner.cs, in mine there I find next object

public virtual object GetLastAutoNumber() {

so it is an public virtual object.

The problem is that the next line of code is giving an error on the .Value item.

var lastAutoNumber = this.tblTypeTableAdapter.GetLastAutoNumber().Value


In your xxx.cs file you find the next line of code;

public virtual global::System.Nullable<int> GetLastAutoNumber() {

also the code for this item is different. The problem is that these line of codes are auto generated, so you can change it but when you build you project the changes are gone.

Now I have searched for differences between my project and your example but I can't find the solution for this problem.
So do you know why your GetLastAutoNumber is different from mine ?

And, is there a really good book for really learning programming with C# (or perhaps C++)

Thankx.

Staf

Problem solved. The example is working fine now.