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.

“XML is like violence - if it's not working for you, you're not using enough of it.”