Dataset with multiple relations not working

  • 9 years ago

    Am I doing something wrong or will the framework not handle multiple dataset relations

    I have 3 tables:

    Table 1  
    tableOneID (PK)
    displayString 

    Table 2
    tableTwoID (PK)
    displayString
    tableOneID (FK) 

    Table 3
    tableThreeID (PK)
    displayString
    tableTwoID (FK) 

    I want to be able to display the information in 3 combo boxes, filtered by the selection the 'upper' combo boxes. I have a dataset containing 3 datatables and have created relationships between the tables. It all works great for the first two combo boxes (cboTable_2's data is filtered correctly by selection in cboTable_1) but not for the next combo box - I want cboTable_3's data to be populated based on the selection in cboTable_2.
     

    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Table_1", conn);

    da.Fill(ds, "Table_1");
     

    da = new OleDbDataAdapter("SELECT * FROM Table_2", conn);

    da.Fill(ds, "Table_2");
     

    da = new OleDbDataAdapter("SELECT * FROM Table_3", conn);

    da.Fill(ds, "Table_3");
     

    DataColumn masterOne = ds.Tables["Table_1"].Columns["tableOneID"];

    DataColumn childOne = ds.Tables["Table_2"].Columns["tableOneID"];

    DataRelation relation = new DataRelation("RelationMasterChild", masterOne, childOne);

    ds.Relations.Add(relation);
     

    cboTable_1.DataSource = ds;

    cboTable_1.DisplayMember = "Table_1.displayString";

    cboTable_1.ValueMember = "Table_1.tableOneID";

     

    cboTable_2.Datasource = ds;

    cboTable_2.DisplayMember = "Table_1.RelationMasterChild.displayString";

    cboTable_2.ValueMember = "Table_1.RelationMasterChild.tableTwoID";
     

    // Now I would assume that I can connect tables 2 and 3 in the same way

    DataColumn masterTwo = ds.Tables["Table_2"].Columns["tableTwoID"];

    DataColumn childTwo = ds.Tables["Table_3"].Columns["tableTwoID"];

    relation = new DataRelation("RelationChildChild", masterTwo, childTwo);

    ds.Relations.Add(relation);

     

    cboTable_3.Datasource = ds;

    cboTable_3.DisplayMember = "Table_2.RelationChildChild.displayString";

    cboTable_3.ValueMember = "Table_2.RelationChildChild.tableThreeID";
     

    On load this all displays correctly, but when selection is changed in cboTable_1 and/or cboTable_2 the contents of cboTable_3 do not get updated.
     

    Thanks for any input.


  • 9 years ago

    hi panda869,
    I think you are trying to do things automatically, atleast write some code which will call on the change event of the cboTable_2 so that code will update the cboTable_3.
    I mean to say write code on the selection change event of the cboTable_1 and cboTable_2 based on it apply changes to cboTable_3.

    private void cboTable_1_SelectionChangeCommitted(object sender, EventArgs e)
    {       
             
    string str1 = cboTable_1.SelectedItem;
             
    string str2 = cboTable_2.SelectedItem;
              // Logic for getting information from table_3 so that which value to select in cboTable_3.
    }

    private void cboTable_2_SelectionChangeCommitted(object sender, EventArgs e)
    {       
             
    string str1 = cboTable_1.SelectedItem;
             
    string str2 = cboTable_2.SelectedItem;
              // Logic for getting information from table_3 so that which value to select in cboTable_3.
    }

    Hope this will help you.
    Thanks & Regards
    Dilipv

  • 9 years ago

    Hi Dilipv,

     That had been my workaround, but there is a way to do this automatically...

     http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2951342&SiteID=1

    Cheers,

    Anna

     

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The most exciting phrase to hear in science, the one that heralds new discoveries, is not 'Eureka!' but 'That's funny...'” - Isaac Asimov