none
Inserting into Master AND Detail table at the same time RRS feed

  • Question

  • I am trying to do something which I thought would be easy.  Perhaps I am missing something.

    On a single windows form, I have a master table and a detail table.  For sake of discussion, let's assume the MASTER is ORDERS and the DETAIL is ORDERDETAILS.

    I created a dataset and have linked these tables using a relationship.   The form displays everything fine for any pre-existing data.

    My issue is when I try to add a NEW ORDER with new ORDER DETAILS at the same time.  Since the OrderID field is auto-incremented, it defaults to -1 while I'm working on the record. 

    When I hit the SAVE button, the Table Adapters update both the MASTER (ORDER) and the DETAIL (ORDERDETAILS) tables.  In the master table the OrderID gets autoincrement and set properly.  The problem is that the OrderID value in the ORDERDETAILS table doesn't get updated to the appropriate MASTER OrderID value.

    How do I ensure that the details row OrderID values get updated once the MASTER table is saved?

     

    Thanks in Advance

    • Moved by Andrew.Wu Thursday, May 19, 2011 3:08 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Thursday, May 19, 2011 1:54 AM

Answers

  • Hello TPascal,

    Thanks for your post.

    This FAQ Item can answer your question. I quoted the content as below:

    The DataSet is able to take care of this for you.  It's just a little non-obvious.

    First, for the auto increment fields, in the DataSet designer we need to set the AutoIncrementSeed and AutoIncrementStep both to -1(negative 1).  This will keep it clear which IDs are temporary and which ones are actually in the database.

    Second, make sure that we have defined both a DataRelation with ForeignKeyConstraint line between parent Customers(IDCust) and Orders(IDCust) in the designer.  We set the UpdateRule on this object to Rule.Cascade.  This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

    In order for this to work correctly, the INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate. For example, INSERT should contain an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

    INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);

    SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

     

    http://msdn.microsoft.com/en-us/library/2hh60x2k.aspx (Foreign Key Constraint Dialog Box)

    http://msdn.microsoft.com/en-us/library/cs5ze1dx.aspx (Rule Enumeration)

     

     

    Related Threads:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

    I hope this can help you,

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 19, 2011 8:03 AM
    Moderator

All replies

  • Hello TPascal,

    Thanks for your post.

    This FAQ Item can answer your question. I quoted the content as below:

    The DataSet is able to take care of this for you.  It's just a little non-obvious.

    First, for the auto increment fields, in the DataSet designer we need to set the AutoIncrementSeed and AutoIncrementStep both to -1(negative 1).  This will keep it clear which IDs are temporary and which ones are actually in the database.

    Second, make sure that we have defined both a DataRelation with ForeignKeyConstraint line between parent Customers(IDCust) and Orders(IDCust) in the designer.  We set the UpdateRule on this object to Rule.Cascade.  This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

    In order for this to work correctly, the INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate. For example, INSERT should contain an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

    INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);

    SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

     

    http://msdn.microsoft.com/en-us/library/2hh60x2k.aspx (Foreign Key Constraint Dialog Box)

    http://msdn.microsoft.com/en-us/library/cs5ze1dx.aspx (Rule Enumeration)

     

     

    Related Threads:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

    I hope this can help you,

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 19, 2011 8:03 AM
    Moderator
  • You need to save Order and retrieve Order Id and then update order details table.

    You could use @@Identity to fetch the last inserted Order


    Pratap --Mark the best replies as answers!
    Tuesday, May 24, 2011 11:02 AM