parent child relationship

[Guest]

Hi,
I'm just learning how to create a 1 to M with ADO.NET.

I've created this little test app. A user selects a company name from the
list, then clicks a button containing the code below. The code is meant to
return all the child rows of the related parent row that has been clicked and
display them in a grid. Unfortunately it seems to be returning the entire
child table, not simply the related rows. What could I be doing wrong here?
(I'm using VS2003)

Thanks for any suggestions in advance
Ant
// First create a dataset
DataSet dsCustomers = new DataSet();

// Add tables to it
dsCustomers.Tables.Add("Customers");
dsCustomers.Tables.Add("Orders");

// Fill it & give it schema
daCustomers.Fill(dsCustomers.Tables["Customers"]);
daOrders.Fill(dsCustomers.Tables["Orders"]);

// Add a relation to it
dsCustomers.Relations.Add("Child",

dsCustomers.Tables["Customers"].Columns["CustomerID"],

dsCustomers.Tables["Orders"].Columns["CustomerID"]
);

// Get a view of the selected row
DataRowView selectedRow = (DataRowView)lstParent.SelectedItem;

// Retrieve child rows into ds using Child relation
dsCustomers.Merge(selectedRow.Row.GetChildRows("Child"));

// Bind child table to grid
dgChild.SetDataBinding(dsCustomers,"Orders");

dgChild.Refresh();

Thank you.

 

[Cor Ligthert [MVP]]

Ant,

Using the defaultview(dataview) with a rowfilter is much easier for this
problem.

Cor

 

[Guest]

Thanks for the reply Cor,

Would you be able to show me a code example using defaultview with the code
example I pasted in? I've never used default view before:

Many thanks
Ant

 

[Cor Ligthert [MVP]]

// First create a dataset
DataSet dsCustomers = new DataSet();

// Add tables to it
dsCustomers.Tables.Add("Customers");
dsCustomers.Tables.Add("Orders");

// Fill it & give it schema
daCustomers.Fill(dsCustomers.Tables["Customers"]);
daOrders.Fill(dsCustomers.Tables["Orders"]);

Remove this part until the ***************

// Add a relation to it
dsCustomers.Relations.Add("Child",

dsCustomers.Tables["Customers"].Columns["CustomerID"],

dsCustomers.Tables["Orders"].Columns["CustomerID"]
);

*********************************
LstParent.DataSource = ds.Customer.Tables["Customers"];
LstParent.DisplayMember = "I cannot see that probable CustomerName";
LstParent.ValueMember = "CusstomerID";

LstParent.SelectedIndexChanged += new
System.EventHandler(this.lstPSelectedIndexChanged);
LstParent.SelectedIndex = 0 'normally should this force your event
// Get a view of the selected row
//DataRowView selectedRow = (DataRowView)lstParent.SelectedItem;// Retrieve child rows into ds using Child relation
//dsCustomers.Merge(selectedRow.Row.GetChildRows("Child"));// Bind child table to grid
// dgChild.SetDataBinding(dsCustomers,"Orders");

// dgChild.Refresh();

private void lstPSelectedIndexChanged(object sender, EventArgs e)
{
// Bind child table to grid
ds.Customers.Tables[DefaultView] = "CustomerID = " +
ListParent.SelectedValue.ToString();
dgChild.DataSource = dsCustomers.Tables["Orders"].DefaultView;
//Defaultview not strict needed
//dgChild.Refresh();
}

///
Just done in this message so watch typos or whatever.

I hope this helps,

Cor

 

[Guest]

Hey Cor,

Thank you very much
Cheers
Ant

// First create a dataset
DataSet dsCustomers = new DataSet();

// Add tables to it
dsCustomers.Tables.Add("Customers");
dsCustomers.Tables.Add("Orders");

// Fill it & give it schema
daCustomers.Fill(dsCustomers.Tables["Customers"]);
daOrders.Fill(dsCustomers.Tables["Orders"]);

Remove this part until the ***************

// Add a relation to it
dsCustomers.Relations.Add("Child",

dsCustomers.Tables["Customers"].Columns["CustomerID"],

dsCustomers.Tables["Orders"].Columns["CustomerID"]
);

*********************************
LstParent.DataSource = ds.Customer.Tables["Customers"];
LstParent.DisplayMember = "I cannot see that probable CustomerName";
LstParent.ValueMember = "CusstomerID";

LstParent.SelectedIndexChanged += new
System.EventHandler(this.lstPSelectedIndexChanged);
LstParent.SelectedIndex = 0 'normally should this force your event
// Get a view of the selected row
//DataRowView selectedRow = (DataRowView)lstParent.SelectedItem;// Retrieve child rows into ds using Child relation
//dsCustomers.Merge(selectedRow.Row.GetChildRows("Child"));// Bind child table to grid
// dgChild.SetDataBinding(dsCustomers,"Orders");

// dgChild.Refresh();

private void lstPSelectedIndexChanged(object sender, EventArgs e)
{
// Bind child table to grid
ds.Customers.Tables[DefaultView] = "CustomerID = " +
ListParent.SelectedValue.ToString();
dgChild.DataSource = dsCustomers.Tables["Orders"].DefaultView;
//Defaultview not strict needed
//dgChild.Refresh();
}

///
Just done in this message so watch typos or whatever.

I hope this helps,

Cor