Dataset relations not working like expected

panda869 · March 2nd, 2008, 12:04 PM

Can someone please tell me if I'm approaching this incorrectly (and if so, suggest a better way) or tell me if I just can't do this at all?

I have 3 tables:

[u]Table 1</u>
tableOneID (PK)
displayString

[u]Table 2</u>
tableTwoID (PK)
displayString
tableOneID (FK)

[u]Table 3</u>
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.

Code:

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";

The problem: On load this data is displayed correctly
cboTable_1 filters data in cboTable_2 which in turn filters data in cboTable_3
but when cboTable_1 and/or cboTable_2 selection is changed, cboTable_3 is not updated

Thanks for any input.