By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,562 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,562 IT Pros & Developers. It's quick & easy.

parent/child grids

P: n/a
VS 2005

I' m setting up a parent/child datagridviews in a form.

I am doing a lot of this by hand coding in order to get the feel of things.
I want a change in the parent table to trigger a change in the child.

Where is the best place (if I cannot set it up to be automatic through
relationships) to catch the change in the parent to refetch the child rows?

This must be quite basic, but I can't seem to figure it out.

Thanks,

Rick

The setup is:

dataset with 2 datatables
the master is connected to a binding source to feed the form UI.
the detail is connected directly to the datagridview

The tables have their relation set (parentPK to childFK)
Mar 23 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
You don't need to refetch the child rows manually, unless you're just a
masochist. For display purposes, two grids, one for parent and one to show
children corresponding to the selected row in the parent, try this:

You need 2 BindingSources -- one for the parent, and one for the child.
Set the BindingSource for the parent to point at the parent table.
Set the BindingSource for the child to point at the parent's BindingSource,
with the datamember being the relation between the two tables.

Here's an example using NorthWind and a strongly typed dataset, but you
could just as easily do it with your own dataset.

Dim ds as CustomersDataSet = CustomersDataSet.GetCustomers()

m_CustomersBindingSource.DataSource = ds
m_CustomersBindingSource.DataMember = "Customers"
m_CustomersGrid.DataSource = m_CustomersBindingSource

m_ChildOrdersBindingSource.DataSource = m_CustomersBindingSource
m_ChildOrdersBindingSource.DataMember = "FK_Orders_Customers"

m_OrdersGrid.DataSource = m_ChildOrdersBindingSource
Hope this helps.
Robin S.
-----------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:O2*************@TK2MSFTNGP04.phx.gbl...
VS 2005

I' m setting up a parent/child datagridviews in a form.

I am doing a lot of this by hand coding in order to get the feel of
things. I want a change in the parent table to trigger a change in the
child.

Where is the best place (if I cannot set it up to be automatic through
relationships) to catch the change in the parent to refetch the child
rows?

This must be quite basic, but I can't seem to figure it out.

Thanks,

Rick

The setup is:

dataset with 2 datatables
the master is connected to a binding source to feed the form UI.
the detail is connected directly to the datagridview

The tables have their relation set (parentPK to childFK)

Mar 23 '07 #2

P: n/a
Thanks Robin, that's what I was looking for.

Rick
You don't need to refetch the child rows manually, unless you're just a
masochist. For display purposes, two grids, one for parent and one to show
children corresponding to the selected row in the parent, try this:

You need 2 BindingSources -- one for the parent, and one for the child.

Mar 24 '07 #3

P: n/a
Ok, I set that up, but with no results in the grid.

Perhaps this is an obvious question, but how does the parent table and
relation know what query to execute to get the child rows?

Do I need to put the child query into the parent?

Rick

"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:1O******************************@comcast.com. ..
You don't need to refetch the child rows manually, unless you're just a
masochist. For display purposes, two grids, one for parent and one to show
children corresponding to the selected row in the parent, try this:

You need 2 BindingSources -- one for the parent, and one for the child.
Set the BindingSource for the parent to point at the parent table.
Set the BindingSource for the child to point at the parent's
BindingSource, with the datamember being the relation between the two
tables.

Here's an example using NorthWind and a strongly typed dataset, but you
could just as easily do it with your own dataset.

Dim ds as CustomersDataSet = CustomersDataSet.GetCustomers()

m_CustomersBindingSource.DataSource = ds
m_CustomersBindingSource.DataMember = "Customers"
m_CustomersGrid.DataSource = m_CustomersBindingSource

m_ChildOrdersBindingSource.DataSource = m_CustomersBindingSource
m_ChildOrdersBindingSource.DataMember = "FK_Orders_Customers"

m_OrdersGrid.DataSource = m_ChildOrdersBindingSource
Hope this helps.
Robin S.
-----------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:O2*************@TK2MSFTNGP04.phx.gbl...
>VS 2005

I' m setting up a parent/child datagridviews in a form.

I am doing a lot of this by hand coding in order to get the feel of
things. I want a change in the parent table to trigger a change in the
child.

Where is the best place (if I cannot set it up to be automatic through
relationships) to catch the change in the parent to refetch the child
rows?

This must be quite basic, but I can't seem to figure it out.

Thanks,

Rick

The setup is:

dataset with 2 datatables
the master is connected to a binding source to feed the form UI.
the detail is connected directly to the datagridview

The tables have their relation set (parentPK to childFK)


Mar 24 '07 #4

P: n/a
Ah. You need to populate both data tables. In my example, GetCustomers is a
method of the strongly typed dataset that fills both tables.

So what you want to pull for your children is all of the children for all
of the parents. Obviously, if you're pulling all parents, you can just pull
all children. Otherwise, you're going to have to figure out how to pull all
of the children for the parents you've seleted. You can do that with a
subquery or a join.

This fills two tables in the same dataset at one time.

Dim SQLString As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(SQLString, connString)
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
For each tbl as DataTable in ds.Tables
Console.WriteLine("TableName = {0}", tbl.TableName)
Next

Hope this helps.

Robin S.
-----------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:eN**************@TK2MSFTNGP02.phx.gbl...
Ok, I set that up, but with no results in the grid.

Perhaps this is an obvious question, but how does the parent table and
relation know what query to execute to get the child rows?

Do I need to put the child query into the parent?

Rick

"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:1O******************************@comcast.com. ..
>You don't need to refetch the child rows manually, unless you're just a
masochist. For display purposes, two grids, one for parent and one to
show children corresponding to the selected row in the parent, try this:

You need 2 BindingSources -- one for the parent, and one for the child.
Set the BindingSource for the parent to point at the parent table.
Set the BindingSource for the child to point at the parent's
BindingSource, with the datamember being the relation between the two
tables.

Here's an example using NorthWind and a strongly typed dataset, but you
could just as easily do it with your own dataset.

Dim ds as CustomersDataSet = CustomersDataSet.GetCustomers()

m_CustomersBindingSource.DataSource = ds
m_CustomersBindingSource.DataMember = "Customers"
m_CustomersGrid.DataSource = m_CustomersBindingSource

m_ChildOrdersBindingSource.DataSource = m_CustomersBindingSource
m_ChildOrdersBindingSource.DataMember = "FK_Orders_Customers"

m_OrdersGrid.DataSource = m_ChildOrdersBindingSource
Hope this helps.
Robin S.
-----------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:O2*************@TK2MSFTNGP04.phx.gbl...
>>VS 2005

I' m setting up a parent/child datagridviews in a form.

I am doing a lot of this by hand coding in order to get the feel of
things. I want a change in the parent table to trigger a change in the
child.

Where is the best place (if I cannot set it up to be automatic through
relationships) to catch the change in the parent to refetch the child
rows?

This must be quite basic, but I can't seem to figure it out.

Thanks,

Rick

The setup is:

dataset with 2 datatables
the master is connected to a binding source to feed the form UI.
the detail is connected directly to the datagridview

The tables have their relation set (parentPK to childFK)



Mar 24 '07 #5

P: n/a
Ok, but from what I can tell I am back to square one.

It seems you are populating the datatables with ALL the records from the db
and then letting the binding source filter the detail records.

If this is the case, it's probably a valid senario for a small set of
orders, but like in my case I have hundreds of thousands to millions of
detail records and it's certainly not practical to fetch them all and filter
them later.

Do I understand this correctly?

Rick
Ah. You need to populate both data tables. In my example, GetCustomers is
a method of the strongly typed dataset that fills both tables.

Mar 25 '07 #6

P: n/a
Hi Rick,

Are you populating the parent table with all of the parent records? If that
is the case, then yes, you are screwed. (Not to put too fine a point on
it).

When have this kind of case, I put selection criteria on the screen and ask
them to narrow down their selection. I only pull the first 10,000 records
or something like that.

If you go back to your original idea of capturing the change on the parent,
and refilling the child, you can capture the PositionChanged event on the
BindingSource for the parent.

I would keep the BS for the child DGV, because it handles the data being
filled into the control so quickly. But you don't want it to be bound to
the foreign key. If you are going to reload the child every time they
change parents, the child grid should just be bound to the child table.

You might also want to put some kind of timer in there, to measure how long
they have been sitting still, so you're not trying to load the child while
they are paging through the parent trying to find the one record they want
to see the children for. Or clear the child datasource when they change
parent records, and wait until they hit a button to fill it.

Good luck. I'd like to know what kind of performance you get; hope it's
okay.

Robin S
-------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:%2******************@TK2MSFTNGP02.phx.gbl...
Ok, but from what I can tell I am back to square one.

It seems you are populating the datatables with ALL the records from the
db and then letting the binding source filter the detail records.

If this is the case, it's probably a valid senario for a small set of
orders, but like in my case I have hundreds of thousands to millions of
detail records and it's certainly not practical to fetch them all and
filter them later.

Do I understand this correctly?

Rick
>Ah. You need to populate both data tables. In my example, GetCustomers
is a method of the strongly typed dataset that fills both tables.


Mar 26 '07 #7

P: n/a
no, I only fill the parent with one requested record.

I guess the child adapter will never fill a table with all the DB rows since
it is never really asked to since the child table is filled from the FK.

Thanks for you explanations and help.

Rick
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:ma******************************@comcast.com. ..
Hi Rick,

Are you populating the parent table with all of the parent records? If
that is the case, then yes, you are screwed. (Not to put too fine a point
on it).

When have this kind of case, I put selection criteria on the screen and
ask them to narrow down their selection. I only pull the first 10,000
records or something like that.

If you go back to your original idea of capturing the change on the
parent, and refilling the child, you can capture the PositionChanged event
on the BindingSource for the parent.

I would keep the BS for the child DGV, because it handles the data being
filled into the control so quickly. But you don't want it to be bound to
the foreign key. If you are going to reload the child every time they
change parents, the child grid should just be bound to the child table.

You might also want to put some kind of timer in there, to measure how
long they have been sitting still, so you're not trying to load the child
while they are paging through the parent trying to find the one record
they want to see the children for. Or clear the child datasource when they
change parent records, and wait until they hit a button to fill it.

Good luck. I'd like to know what kind of performance you get; hope it's
okay.

Robin S
-------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:%2******************@TK2MSFTNGP02.phx.gbl...
>Ok, but from what I can tell I am back to square one.

It seems you are populating the datatables with ALL the records from the
db and then letting the binding source filter the detail records.

If this is the case, it's probably a valid senario for a small set of
orders, but like in my case I have hundreds of thousands to millions of
detail records and it's certainly not practical to fetch them all and
filter them later.

Do I understand this correctly?

Rick
>>Ah. You need to populate both data tables. In my example, GetCustomers
is a method of the strongly typed dataset that fills both tables.



Mar 26 '07 #8

P: n/a
Rick

Please take a look at the last episode of Dot Net Rocks TV

http://www.dnrtv.com/default.aspx Show #60 Rocky Lhotka

There is also a link to the source code used during the demo

Rocky shows databinding to datagrids with a Parent--Child-->Grandchild
relationship using databinding.

I may be off based here. I'm just starting to learn this stuff. Hope the
info helps.

Chris

"Rick" <Ri**@LakeValleySeed.comwrote in message
news:ej**************@TK2MSFTNGP06.phx.gbl...
no, I only fill the parent with one requested record.

I guess the child adapter will never fill a table with all the DB rows
since it is never really asked to since the child table is filled from the
FK.

Thanks for you explanations and help.

Rick
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:ma******************************@comcast.com. ..
>Hi Rick,

Are you populating the parent table with all of the parent records? If
that is the case, then yes, you are screwed. (Not to put too fine a point
on it).

When have this kind of case, I put selection criteria on the screen and
ask them to narrow down their selection. I only pull the first 10,000
records or something like that.

If you go back to your original idea of capturing the change on the
parent, and refilling the child, you can capture the PositionChanged
event on the BindingSource for the parent.

I would keep the BS for the child DGV, because it handles the data being
filled into the control so quickly. But you don't want it to be bound to
the foreign key. If you are going to reload the child every time they
change parents, the child grid should just be bound to the child table.

You might also want to put some kind of timer in there, to measure how
long they have been sitting still, so you're not trying to load the child
while they are paging through the parent trying to find the one record
they want to see the children for. Or clear the child datasource when
they change parent records, and wait until they hit a button to fill it.

Good luck. I'd like to know what kind of performance you get; hope it's
okay.

Robin S
-------------------------------
"Rick" <Ri**@LakeValleySeed.comwrote in message
news:%2******************@TK2MSFTNGP02.phx.gbl. ..
>>Ok, but from what I can tell I am back to square one.

It seems you are populating the datatables with ALL the records from the
db and then letting the binding source filter the detail records.

If this is the case, it's probably a valid senario for a small set of
orders, but like in my case I have hundreds of thousands to millions of
detail records and it's certainly not practical to fetch them all and
filter them later.

Do I understand this correctly?

Rick

Ah. You need to populate both data tables. In my example, GetCustomers
is a method of the strongly typed dataset that fills both tables.



Apr 6 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.