Can I get database auto-increment values within a data adapter's RowUpdated event? RRS feed

  • Question

  • The context for the following is ADO.NET 2.0, OLE DB, and Microsoft Access 2003 (and Visual Studio 2005 with C# 2.0, if that matters).

    I have parent and child tables in an Access database. Let's call them Order and OrderDetail. I want to insert an Order row and related OrderDetail rows within a transaction I can roll back in case an error occurs with any of these inserts. The database generates an autonumber for the Order primary key column. Somehow, I need to determine what autonumber is assigned to the Order row and use this for the foreign key within the OrderDetail rows. I tried to use the RowUpdated event of the Order data adapter to do this. My plan was to do the following within the handler:

    1. Get the autonumber assigned to the Order row.
    2. Assign this number to e.Row["OrderID"], the PK column of the Order row, so that it can be written back to the Order data table in my data set.
    3. Add new rows to the OrderDetail data table in my data set, using the retrieved autonumber as the foreign key.
    4. Call Update on the OrderDetail data adapter to add these to the database (using the same transaction and connection used for the Order insert).

    Within the RowUpdated handler I executed a command with command text "SELECT @@IDENTITY;" (the way to access the assigned autonumber from a Microsoft Access database). Executing this command always returns the integer 0. Must the RowUpdated event for an inserted row finish before the command can be used to retrieve the autonumber? If so, how do I do a data adapter update for several orders while capturing the autonumber for each order and using it as the foreign key for new OrderDetail rows?

    If you have David Sceppa's book, Programming Microsoft ADO.NET 2.0 Core Reference, 2005 edition, take a look at pages 522 to 524 and 532 to 534. This is what I'm trying to do.

    After rereading the book, I decided to add new Order and OrderDetail rows within the data set first, then retrieve the autonumbers for the new Order rows, let the data set propogate these to the OrderDetail rows, and then call Update on the Order and OrderDetail data adapters. After doing that, my problem still boils down to the fact that executing the command to retrieve autonumbers within the RowUpdated handler always returns the integer 0. I've tried this both with and without a transaction. Does "SELECT @@IDENTITY;" have to be executed as a kind of stored procedure for it to work? I have been careful to set the command to the same connection and the same transaction used by the Order data adapter. What gives?

    • Edited by frosts0 Wednesday, May 20, 2009 11:19 PM
    Wednesday, May 20, 2009 9:08 PM


  • Because the book example uses an SQL Server database and a stored procedure with an output parameter, it calls ExecuteNonQuery on the command. Since I'm using Access (not by choice), I cannot create output parameters. So, I have to call ExecuteScalar instead of ExecuteNonQuery.

    • Marked as answer by frosts0 Thursday, May 21, 2009 1:03 AM
    • Edited by frosts0 Thursday, May 21, 2009 1:11 AM The first sentence was ungrammatical.
    Thursday, May 21, 2009 1:02 AM