Retrieve Access AutoNumber Value After Insert-VBForums
Results 1 to 4 of 4

Thread: Retrieve Access AutoNumber Value After Insert

Threaded View

  1. #1

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,080

    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.
    Attached Files Attached Files
    Last edited by jmcilhinney; Sep 1st, 2011 at 09:46 PM.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.