AutoIncrement settings should be applied before filling the DataTable…

I ran into a problem recently, where duplicate IDs were being generated by my offline ADO.NET code… and I had no real idea why this was happening. I had set up the primary key of my DataTable to have the following propery values:

  • AutoIncrement = true
  • AutoIncrementSeed = -1
  • AutoIncrementStep = -1

This is supposed to result in offline IDs being assigned as -1,-2,-3 and therefore having no possibility of conflict with any real IDs used in the database. This has worked great for me on many occasions, so it was a bit of a surprise when I finally tracked down my problem to the IDs that were being assigned to my new rows. I put a break point right after calling NewRow on the DataTable and the newly created Row had a PK value of 141. Another new row and it would have a value of 140, and so on… it seems the AutoIncrementStep was working, but the seed value was wonky.

I asked around and was told to make sure that I was setting the AutoIncrement properties before filling the table, which it turns out I wasn’t doing. What I had for code was basically like this:

  1. If table doesn’t exist in DataSet, set a flag to true indicating that this is the first call to the data load
  2. Fill the table
  3. Check the flag and setup the table, including setting the AutoIncrement properties

With the new guidance I had received, I changed the routine to:

  1. If table doesn’t exist in DataSet, set a flag to true indicating that this is the first call to the data load
  2. Fill the table’s schema (FillSchema), to get the columns and the PK
  3. Check the flag and setup the table, including setting the AutoIncrement properties
  4. Fill the table

Of course, all this could be made simpler/cleaner if I built up the schema ‘manually’ before loading the table’s data but I’m way too lazy for that.

AutoIncrement settings should be applied before filling the DataTable…

5 Responses

  1. tHIS IS WHAT WE CALL, KICKING IN AN ALREADY OPEN DOOR!

    Anonymous April 18, 2005 at 10:11 am #
  2. wait for update

    bob May 2, 2005 at 3:21 pm #
  3. thanks to the original poster! I encountered this exact same problem and was tearing my hair out. I don’t think anything about this was mentioned in MS’s documentation! ADO.NET also fails to update AllowDBNull property of table columns, not to mention its lack of facility to mirror database foreign key definitions into datasets – one must write own code to create DataRelation objects. ADO.NET also is unable to set the .Unique column property using existing database unique key indexes (for Access at least, this is the only way to define a column’s unique attribute). I’m currently taking notes for my upcoming book “.NET Annoyances” – it’s going to be a very thick volume!

    tandy February 8, 2006 at 7:55 am #
  4. Great article, one needs to search a long time to find a solution for these difficulties

    Thanks!

    Anonymous1 December 6, 2006 at 12:38 pm #
  5. Wow! Thank you for this information! I have been pulling my hair out and trying to “tweak” Microsoft’s code by using Reflection to change the internal “AutoIncrementCurrent” hidden property. I hate how MS won’t fix glaring bugs in their code because they want things to be “backward compatible” but yet they can introduce completely NEW functionality which actually DOES break all existing code!

    Tim July 18, 2011 at 9:44 am #

Leave a Reply