Changing Primary Key Columns in a hierarchical dataset RRS feed

  • Question

  • Hi,

    I have one set of tables in my SQL db that I need to allow the user to change the Primary Key on (it's a Clerk ID for what it's worth).

    I've got the set of related tables up in a hierachical dataset, and the change to the primary key in the parent is being properly cascaded to the child tables (in the DataSet).

    I'm updating the db via a sproc using the original key values in my Where clause.

    My problem is, the Update to my parent table is cascading the Primary Key change to the child tables in the database. By the time my code calls .Update on the child table(s), the Original primary key record no longer exists (it's already changed to the new value). So, I get 0 records updated and the TableAdapter throws a DBConcurrency exception up at me because it was expecting 1 record to be updated.

    Aside from re-architecting my tables, and not allowing changes to Primary Key columns; What's the recommended approach to handling this type of scenario? I've thought of just calling AcceptChanges on my child tables when the primary key has changed (assuming it has cascaded to the parent), but what if the user has made other changes to the child table... I'm at a loss as to how to handle this.

    Any help would be greatly appreciated,



    Friday, January 20, 2006 6:03 AM


  •  Dooh!!!

    You know, sometimes you've just got to let the world know you're an idiot before you can figure things out for yourself.

     I just need to change the Update sproc on my child tables to not perform the Where on my original key values!!

    But, I'll leave it up here just incase anyone else is having a brain fart at the moment and running into the same thing (since all my Google's only showed how to handle AutoIncrement key column cascades).

    Friday, January 20, 2006 6:35 AM