none
Update Child Table Rows with Parent Identity Value in Typed DataSet RRS feed

  • Question

  • I have a strongly typed dataset shown below called dsQuoteData.  I assumed by calling the TableAdapterManager.UpdateAll(dsQuoteData) method would update all the child rows automatically with the newly inserted parent row identity keys by using the Hierarchical Update.

    For example, when I insert a new row in Quotes table the QuoteID is autogenerated to equal -1 in ADO.NET.  When I insert child rows into QuoteRevisions table, I use the -1 QuoteID.  But when I call TableAdapterManager.UpdateAll(dsQuoteData) I get this error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_QuoteRevisions_Quotes". The conflict occurred in database "QuoteGenerator", table "dbo.Quotes", column 'QuoteID'. The statement has been terminated."  The error is happening because my child rows in QuoteRevisions still have a QuoteID = -1.

    Does this mean I have to update the Quotes table first, get the new QuoteID identity value, then edit the child rows in QuoteRevisions with the new QuoteID, then update QuoteRevisions and so on?  Is there an easier way to do this?


    Ryan


    • Edited by Ryan0827 Sunday, November 3, 2013 3:01 AM
    • Moved by Carl Cai Monday, November 4, 2013 9:39 AM get better support
    Sunday, November 3, 2013 2:57 AM

Answers

All replies

  • Hi,

    Welcome to MSDN.

    I have moved this thread to ADO.NET DataSet forum for more dedicated support.

    Thanks for your understanding.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 4, 2013 9:38 AM
  • Hello,

    >>Does this mean I have to update the Quotes table first, get the new QuoteID identity value, then edit the child rows in QuoteRevisions with the new QuoteID, then update QuoteRevisions and so on?

    From my opinion, we have to do this.

    This is because we must perform each specific order related tables Insert, Update and Delete commands defined in the database to avoid violating foreign key constraints.

    For example, suppose we have an order entry application, we can use the program management of new and existing customers and orders. If we need to delete an existing customer record, we must first delete all the orders for that customer. If we want to add a new customer record (with order), we must first insert a new customer record, and then insert the customer's order, which is the table of the foreign key constraint requirements. As these examples show, we need to extract a specific subset of the data, and in the correct order to send updates (Insert, Update and Delete) to maintain referential integrity, to avoid violating foreign key constraints in the database.

    The best practice is to update the database according to the following order:

    1. Child Table: Delete
    2. Parent Table: Insert, Update and Delete
    3. Child Table: Insert and Update

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 5, 2013 3:04 AM
    Moderator
  • > I have a strongly typed dataset shown below called dsQuoteData.  I assumed by calling the TableAdapterManager.UpdateAll(dsQuoteData) method would update all the child rows automatically with the newly inserted parent row identity keys by using theHierarchical Update <http://msdn.microsoft.com/en-us/library/bb384468.aspx>.

    What you need to do is set the .UpdateOrder property of the TableAdapterManager. Here's a link explaining it:

    http://msdn.microsoft.com/en-us/library/vstudio/bb384509(v=vs.100).aspx


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 10, 2013 2:42 AM