parent child relationship

10 Feb 2006 5:16 AM

Ant

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.

10 Feb 2006 6:41 AM

Cor Ligthert [MVP]

Ant,

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

Cor

10 Feb 2006 8:23 AM

Ant

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

Show quote

"Cor Ligthert [MVP]" wrote:

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

10 Feb 2006 10:10 AM

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

10 Feb 2006 10:32 AM

Ant

Hey Cor,

Thank you very much
Cheers
Ant

Show quote

"Cor Ligthert [MVP]" wrote:

>
> > // 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
>
>
>