none
Values for autoincrement fields before update RRS feed

  • Question

  • I have a number of linked disconnected datatables that are linked on their respective indexed autoincrement fields.  Without updating the underlying tables, is there any way of extracting what the values of the autoincrement fields are going to be?  I thought that I could add the autoincrementseed to the autoincrementstep to get the next value and then finally update the seed.   Unfortunately, I have found that sometimes that approach fails. 

    In adodb this wouldn't be as much of problem as the datatable updates automatically with each new record.  Should I continuously call update to make sure that the indexes in memory are the same as in the underlying datatable?  Sometimes I would prefer not to immediately update the underlying table.  

    Any ideas or approached that would help solve this problem?

    Thanks,

    Craig


    Salt Lake
    Wednesday, June 16, 2010 9:43 PM

Answers

  • > Without updating the underlying tables, is there any way of extracting what the values of the autoincrement fields are going to be?

    No.  When the database has an identity field, you must actually INSERT the data before these values are known for sure.  The main issue is concurrency with other users.  (Another issue is that there could be identity values used up for rows that no longer exist, so selecting the MAX value from the database and adding one won't always work right even if there are no concurrent users.)

    However, there is a standard way of using DataSet that lets you get back the actual assigned IDENTITY values right after you perform the adapter Update.  There are also ways to use cascading foreign keys in the DataSet itself so that you can add related rows into the DataSet and work with them all prior to actually INSERTing any of the related rows into the database.  You can use the temporary values to satisfy the foreign keys, and everything will get changed to the actual identity values during the adapter Update.  See http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d for more information if this is unfamiliar to you.

     

    • Marked as answer by CraigWMiller Thursday, June 17, 2010 2:10 PM
    Thursday, June 17, 2010 12:21 AM