Click here to Skip to main content
13,548,412 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 5 Sep 2011
Licenced CPOL

Updating Auto-Numbered Primary Keys in a DataTable (MSAccess Databases)

, 3 Oct 2011
Rate this:
Please Sign up or sign in to vote.
It is common to configure back-end database tables to contain a column with an auto-generated ID unique to each row. This tip describes how to sync the ID generated by the database back to the application layer.
It is common to configure back-end database tables to contain a column with an auto-generated ID unique to each row. When a row is added, the application layer just makes up an ID for each newly added row (-1, -2 etc.).

When the newly added row is added back to the database (think TableAdapter.Update) the database auto-generates the actual ID. But the application still skips blithely along with its made-up ID. When subsequent operations are performed on the database (like deleting the newly added row), this situation can result in a Concurrency exception. Yes, a Concurrency exception. Even with a single-user, single-threaded application. Threw me for a loop.

Apparently this is not such a big deal for SQL users, because you can bundle multiple commands into a single transaction. SO they just bundle a command that retrieves the ID from the database after the row has been added and update the DataTable accordingly. I don't know much about SQL, so please comment if you can shed more light on this mechanism.

For us poor schlubs stuck with Access, it seems that we can't bundle commands into a single transaction. So what's a guy/gal to do? An article[^]from the talented Beth Massi describes a solution for VB .NET, and my tip is closely modeled on her article. I did have to make some changes for C# though.

So here is a walkthrough:

  1. Include your database in your project. This will auto-generate a xxDataset.xsd (xx is your database name).
  2. Double-click xxDataset.xsd to bring up the Database Designer.
  3. Right click any table and choose View Code. This auto-generates a xxDataSet.cs file that EXTENDS the auto-generated code that Visual Studio created for you.
  4. Delete the contents of xxDataSet.cs and replace it with the following:
    namespace DatabaseTest.App_Data.{dbName}DataSetTableAdapters
        /// /// This class extends the one that is auto-generated by VS when including 
        /// the database file.
        public partial class {tableName}TableAdapter
            public void HookUpHandlers()
                this.Adapter.RowUpdated += 
                    new OleDbRowUpdatedEventHandler(Adapter_RowUpdated);
            public void Adapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
                // Every time a row is added populate the ID column with the auto-
                // generated value from the database.
                TableAdapterHelper.PopulateKey(e, Connection, Transaction);
        /// This helper class contains a generic method that updates the primary key 
        ///   column with the auto-generated on from the database.
        /// NOTE: Only operates on new (inserted) rows.
        /// NOTE: Does nothing is the table does not contain an auto-generated
        ///       primary key.
        public static class TableAdapterHelper
            public static void PopulateKey(
                OleDbRowUpdatedEventArgs e, 
                OleDbConnection connection, 
                OleDbTransaction transaction)
                // Only look for Add statements
                if (e.StatementType == StatementType.Insert)
                    // Primary key exists?
                    DataColumn[] pk = e.Row.Table.PrimaryKey;
                    if (pk != null && pk.Length > 0)
                        DataColumn primaryKeyColumn = pk[0];
                        // Get the auto-geerated primary key
                        OleDbCommand cmd = new OleDbCommand(
                            "SELECT @@IDENTITY", connection, transaction);
                        int id = (int)cmd.ExecuteScalar();
                        // IN the DataTable change the C# default key (-1, -2 etc.) 
                        // into the real key generated by the database
                        e.Row[primaryKeyColumn] = id;

    Then call HookUpHandlers() from wherever the tableadapter is initialized:
    MyTableAdapter _myTableAdapter = new MyTableAdapter();

So what does this do? Every time the TableAdapter is used to update a row of the database TableAdapterHelper.PopulateKey is called. If the update is an Insert (Add), then we need to worry about the database generating an ID different from that in the DataTable. We check if a primary key was defined for this table. If so, we execute a nifty little query (SELECT @@IDENTITY) that returns the unique ID last generated by the database. Then we update the DataTable with the correct ID. Easy as pie. Which is an odd expression, given that pie is one of the more difficult pastries to make. But that's OK - my goal is communication, not accuracy! Wait, that's not right. Maybe I'll write an article about this...

So anyway, why does this work? Because the code is stuck right inside the TableAdapter.Update RowUpdated callback - IN THE MIDDLE OF THE TRANSACTION. That's the key. The SELECT @@IDENTITY will only work if called when the transaction is still active, which is why this is so much easier with the command-bundling provided by SQL.

Other fun stuff - what if multiple rows are added before TableAdapter.Update is called? No problem. RowUpdated callback gets fired for each row, one at a time, and everything works fine.

What about multiple tables? This example code is only for one table, it's true. My application has quite a few more tables, and the code got somewhat ugly. I would appreciate any better ideas, but I just added one TableAdapter extension (partial class) per table. And then I called HookUpHandlers for each TableAdapter. Not very extensible, I know. Maybe something nifty with reflection?


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Adam Covitch
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionUpdating Auto Number code seem not to work in Visual studio 2013 Pin
JerryT6-Jun-16 10:50
memberJerryT6-Jun-16 10:50 
GeneralMake sure that the connection to the Access db is made thru ... Pin
C.Page14-Sep-11 2:22
memberC.Page14-Sep-11 2:22 
GeneralReason for my vote of 5 : ) Pin
Ilka Guigova5-Sep-11 15:51
memberIlka Guigova5-Sep-11 15:51 
GeneralInteresting. Pin
Walt Fair, Jr.5-Sep-11 8:11
subeditorWalt Fair, Jr.5-Sep-11 8:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180515.1 | Last Updated 3 Oct 2011
Article Copyright 2011 by Adam Covitch
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid