My TypedDataSet has the wrong identity key values

When working with typed datasets that have parent/child relationships, and using SqlDataAdapters with SqlCommandBuilders to load and update data back to your data source, you may run into foreign key constraint issues.

Why?  Well, suppose you are populating a datagrid with your typed dataset.  You add a parent row, and then add a child to that row.  You haven't yet tried to push your updates again.  Since the datagrid isn't connected to the database, it autogenerates the ID values it uses to populate the rows. 

When you go to push your updates back to the database, it won't try to publish the ID values for the identity columns.  But the ID that is in the child row will be the datagrid generated value, and, unless you are damn lucky, the datagrid generated value won't match the value that is generated by the database on the insert.

So how to fix this?  Two things to do:

1) In the definition of your typed dataset, make sure you choose to cascade your relationships.

2) Use this code: SqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

That line of code tells the SqlDataAdapter that it needs to pull back the real ID value, and since cascade is turned on, it will cascade through the dataset and so through the datagrid.

posted on Thursday, November 17, 2005 3:13 PM Print
No comments posted yet.

Post Comment

Title *
Name *
Comment *  
Please add 4 and 2 and type the answer here: