none
How to setup datarelations for 1 parent and 2 detail tables RRS feed

  • Question

  • I have a datarelation set up between 1 parent and 1 detail table in a dataset and I want to set up another datarelation with the same parent but another detail table.

    here is the setup for the 1 parent 1 detail.

     oParentColumns1 = dsDetail.Tables("tblOrderDetail").Columns("OrderDetailID")
            oChildColumns1= dsDetail.Tables("tblOrderDetailLots").Columns("OrderDetailLotsID")
            ' Create DataRelation.

            relDetails1 = New DataRelation("DetailsLots", oParentColumns1, oChildColumns1,False)

    In order to add another detail table (tbldetailProds), do I create 2 new datacolumns (a new parent and new child) for the new relation or just a new child and use the same oParentColumns1?

    So is it:

     oParentColumns2 = dsDetail.Tables("tblOrderDetail").Columns("OrderDetailID")
            oChildColumns2= dsDetail.Tables("tblOrderDetailProd").Columns("OrderDetailProdID")
            ' Create DataRelation.

            relDetails2 = New DataRelation("DetailsLots", oParentColumns2, oChildColumns2,False) ---NOTE create a new oParentColumns2 for the new datarelation.


    OR

            oChildColumns2= dsDetail.Tables("tblOrderDetailProd").Columns("OrderDetailProdID")
            ' Create DataRelation.

            relDetails2 = New DataRelation("DetailsLots", oParentColumns1, oChildColumns2,False)  --- NOTE:  using same oParentColumns1 for both relations but a new child datacolumn

    thanks
    Wednesday, July 29, 2009 5:57 PM

Answers

  • These two data relations are between two different tables. So create or obtain the other third table and create another relation using its columns as the child columns. The column must be the one that compares to the parent key column.

    I would reuse the same parent column in both relations, but the child columns are both from the different child tables.

    Thanks
    Chris Robinson
    Program Manager -DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Chris Robinson- MSFT Wednesday, July 29, 2009 7:57 PM
    • Marked as answer by smhaig Wednesday, July 29, 2009 8:55 PM
    Wednesday, July 29, 2009 7:57 PM

All replies

  • These two data relations are between two different tables. So create or obtain the other third table and create another relation using its columns as the child columns. The column must be the one that compares to the parent key column.

    I would reuse the same parent column in both relations, but the child columns are both from the different child tables.

    Thanks
    Chris Robinson
    Program Manager -DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Chris Robinson- MSFT Wednesday, July 29, 2009 7:57 PM
    • Marked as answer by smhaig Wednesday, July 29, 2009 8:55 PM
    Wednesday, July 29, 2009 7:57 PM
  • Thanks for this reply.  I am therefore using the same datacolumn for the  parent for both relations. 


      oParentColumns1  - used for both detail tables

    oParentColumns1 = dsDetail.Tables("tblOrderDetail").Columns("OrderDetailID")
            oChildColumns1= dsDetail.Tables("tblOrderDetailLots").Columns("OrderDetailLotsID")
            ' Create DataRelation.

            relDetails1 = New DataRelation("DetailsLots", oParentColumns1, oChildColumns1,False)



          oChildColumns2= dsDetail.Tables("tblOrderDetailProd").Columns("OrderDetailProdID") -- different datacolumn
            ' Create DataRelation.

            relDetails2 = New DataRelation("DetailsLots", oParentColumns1, oChildColumns2,False)  --- NOTE:  using same oParentColumns1 for both relations but a new child datacolumn




    I am presuming that although each relation is named differently (reldetails1, reldetails2)   that it is added to the datarelations collection of the dataset?  Is this correct.

    I am finding that when I do a .setparentrow on a row I am adding to the 2nd detail table, that the table's foreign key is not updated. So for some reason this second table is not recognized as having a relationship with the parent table otherwise the .setparentrow would properly update.




    detailrow = dsdet.Tables(

    "tblOrderDetailsprod").NewRow
    detailrow (
    "OrderDetailID") = 0   -- foreign key

    detailrow (

    "StorageTableID") = 6

    detailrow .SetParentRow(parentrow)

    dsdet.Tables(

    "tblOrderDetailprods").Rows.Add(detailrow )  - the orderdetailId should be updated here.


    parentrowis the parent row just added to the datatable in the dataset.  The orderdetilID of the detail table should be -1 if that is the first parent record added. but it is not being updated and remains 0.

    However, the same code when applied to the first detail table works correctly


    Wednesday, July 29, 2009 8:29 PM
  • After careful viewing I have found the error.

     oChildColumns2= dsDetail.Tables("tblOrderDetailProd").Columns("OrderDetailProdID") -- different datacolumn
            ' Create DataRelation.

            relDetails2 = New DataRelation("DetailsLots", oParentColumns1, oChildColumns2,False)  --- NOTE:  using same oParentColumns1 for both relations but a new child datacolumn

    The child column is the name of the foreign key in the detail table.  I was using the primary key of the detail table.


    oChildColumns2= dsDetail.Tables("tblOrderDetailProd").Columns("OrderDetailProdID") -- different datacolumn  is what I coded but correct one is:

    oChildColumns2= dsDetail.Tables("tblOrderDetailProd").Columns("OrderDetailID") -- different datacolumn

    I was able to find this when I looked at the relations collection of the dataset in the immediate window  which showed that the first table created the childcolumn with the correct key,  but the second table did not.

    Thanks for help on this.





    Wednesday, July 29, 2009 8:55 PM