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

Dataset - How to get data from a child table?

P: n/a
Kay
Hi all,

I have populated a dataset with several tables, say - Roster, Agent and few
more . The dataset is used to populated a listview with some shift info..

I think I'm sucessfully created a relationship in the dataset. In the
Roster table there's an "AgentKey" which can be linked to the Agent table.
What I'm tryting to do is, while I'm looping thru the dataset, I want to
show the Name of an Agent in the listview from the Agent table.

I have a look on the help or internet, the answer I found is I have to loop
thru the Agent table... since there may be couple hundreds Roster record may
be returned, and I also need to show several other columns in the same
manner, I'm worry the process may be pretty slow.

So I'm wondering is there a better way to do it?

Here's my code:

Dim daRoster, daAgent As SqlDataAdapter
Dim rowRoster As DataRow

daRoster = New SqlDataAdapter(sSQL, Conn)
daAgent = New SqlDataAdapter("Select Agent_Key, Name, from Agent", Conn)
dsRoster.Relations.Add("AgttoR",
dsRoster.Tables("Agent").Columns("Agent_Key"), dsRoster.Tables
("Roster").Columns("Agent_Key"))

For Each rowRoster In dsRoster.Tables("Roster").Rows
itmx = New ListViewItem
itmx.Text = rowRoster("Agent_Roster_Key")
itmx.SubItems.Add(rowRoster("Name") '**** this doesn't
work ...
lvwRoster.Items.Add(itmx)
Next
Thanks~

Kay
Nov 28 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Here is one similar piece of code I have from the past (almost the
same). The child Grids automatically update with child table
information when you move the cursor on parent records. Also the parent
grid expands into related child lists.

Dim ds As DataSet = New DataSet
ds.Clear()
adpCustomers.Fill(ds, "Customers")
adpOrders.Fill(ds, "Orders")
adpOrderDetails.Fill(ds, "Order Details")
ds.Relations.Add("CustomersToOrders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))

ds.Relations.Add("OrdersToOrderDetails", _
ds.Tables("Orders").Columns("OrderID"), _
ds.Tables("Order Details").Columns("OrderID"))

dgCustomers.DataSource = ds
dgCustomers.DataMember = "Customers"

dgOrders.DataSource = ds
dgOrders.DataMember = "Customers.CustomersToOrders"

dgOrderDetails.DataSource = ds
dgOrderDetails.DataMember =
"Customers.CustomersToOrders.OrdersToOrderDeta ils"
Hope it's what you are looking for.

Nov 28 '05 #2

P: n/a
Kay
Hi Bill,

Thanks for your help~ the first bit of your code is very similar to mine but
I need to populate the data into a listview and the each record will be in
one row, rather than in hierarchal, for example:

Agent Name/Shift Date/Shift Start/Shift End
Kay/01-11-2005/10.00/18.00

Agent Name is linked to Agent table(child), the rest are Roster
table(parent). And I don't know how to get the Agent Name(child table) from
a dataset.....

If you need more info please let me know~

T.I.A.

Kay

<bi*********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Here is one similar piece of code I have from the past (almost the
same). The child Grids automatically update with child table
information when you move the cursor on parent records. Also the parent
grid expands into related child lists.

Dim ds As DataSet = New DataSet
ds.Clear()
adpCustomers.Fill(ds, "Customers")
adpOrders.Fill(ds, "Orders")
adpOrderDetails.Fill(ds, "Order Details")
ds.Relations.Add("CustomersToOrders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))

ds.Relations.Add("OrdersToOrderDetails", _
ds.Tables("Orders").Columns("OrderID"), _
ds.Tables("Order Details").Columns("OrderID"))

dgCustomers.DataSource = ds
dgCustomers.DataMember = "Customers"

dgOrders.DataSource = ds
dgOrders.DataMember = "Customers.CustomersToOrders"

dgOrderDetails.DataSource = ds
dgOrderDetails.DataMember =
"Customers.CustomersToOrders.OrdersToOrderDeta ils"
Hope it's what you are looking for.

Nov 28 '05 #3

P: n/a
Kay,

I first did not see that Bill gave you the correct answer.And now I see he
does not need the sample I have given him in a latter post.

What I wanted to write to you was, that you would benefit not using the
listview for data-objects. (System.data). In that is the DataGrid and the
DataGridView optimalized. The listview will give you only as mostly all who
tried this sooner or later a lot of trouble.

However if you persist, while I think that even the TreeView can be better
about what you wrote to Bill.

You can get from every datarow with relations its childs and it parent if
those exist.

Just my thought,

Cor
Nov 28 '05 #4

P: n/a
Kay
Hi Cor,

I though of datagrid before but user must click on a link to see the child's
data - since I simply want to show the Agent's name (instead of AgentKey),
it seems listview may do the job - if I can retrive the data from a child
table, let me explain :

In roster table it has all the shift info like AgentID/date/time/campaign
etc, so a roster record in the listview will looks like that

Agent Name/Shift Date/Shift Start/Shift End
Kay/01-11-2005/10.00/18.00

They're in one row although the Agent name is coming from a different table.
I've created a relation in a dataset, after I done a bit more reseach I
found the GetChildRows method, however I need to loop thru the whole Agent
table to get the name. I'm worring on the performance coz I need to link to
3 different table to display one roster record, and there may hundreds or
roster records in the listview...

Do you think I'm on the right track?

Thanks!
Kay
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Kay,

I first did not see that Bill gave you the correct answer.And now I see he
does not need the sample I have given him in a latter post.

What I wanted to write to you was, that you would benefit not using the
listview for data-objects. (System.data). In that is the DataGrid and the
DataGridView optimalized. The listview will give you only as mostly all
who tried this sooner or later a lot of trouble.

However if you persist, while I think that even the TreeView can be better
about what you wrote to Bill.

You can get from every datarow with relations its childs and it parent if
those exist.

Just my thought,

Cor

Nov 28 '05 #5

P: n/a
Kay,

I tried it using completely the designer.

I opened the data -> datasource and selected in that wizard the NorthWind
database with the tables customers and customerorders.

Than I dragged a datagridview on my form

I set the properties datasource of that (opening that property until I had
by clicking the plus buttons the order table).

After that I opened the edit columns with the link at the bottom. I choise
the idcustomercolumn and said that it was a combobox. I set for that the
datasource to the customer table and set as well the displaymember to
customername and the valuemember to customerid

Than I started debug and it was running showing a nice datagridview with a
combobox with the names of the customers in it, that I could select to
change those.

I hope this helps,

Cor
Nov 29 '05 #6

P: n/a
Kay,

This was an answer to Bill, I has set it apart to try to answer it later. As
I did with yours, however added it to the wrong messagthread.

Sorry

Cor
Nov 29 '05 #7

P: n/a
Kay,

Don't be afraid for looping that is done so many times behind the scene and
you don't see it.

Another approach (I did not test it) is setting an extra columns to your
table "Author"

Than you can use in that an expression to the parent.

http://msdn.microsoft.com/library/de...ionColumns.asp

Than use the expression
http://msdn.microsoft.com/library/de...ssionTopic.asp

And than this part in it.
PARENT/CHILD RELATION REFERENCING

A parent table may be referenced in an expression by prepending the column
name with Parent. For example, the Parent.Price references the parent
table's column named Price.

Than you can use that datatable to use in a datagrid, and with nice
columnstyles you get in my opinion a nicer view on it than with a listview.

I would give it a try in your case.

Cor


"Kay" <kk@micxsoft.com> schreef in bericht
news:Ou**************@TK2MSFTNGP11.phx.gbl...
Hi Cor,

I though of datagrid before but user must click on a link to see the
child's data - since I simply want to show the Agent's name (instead of
AgentKey), it seems listview may do the job - if I can retrive the data
from a child table, let me explain :

In roster table it has all the shift info like AgentID/date/time/campaign
etc, so a roster record in the listview will looks like that

Agent Name/Shift Date/Shift Start/Shift End
Kay/01-11-2005/10.00/18.00

They're in one row although the Agent name is coming from a different
table. I've created a relation in a dataset, after I done a bit more
reseach I found the GetChildRows method, however I need to loop thru the
whole Agent table to get the name. I'm worring on the performance coz I
need to link to 3 different table to display one roster record, and there
may hundreds or roster records in the listview...

Do you think I'm on the right track?

Thanks!
Kay
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Kay,

I first did not see that Bill gave you the correct answer.And now I see
he does not need the sample I have given him in a latter post.

What I wanted to write to you was, that you would benefit not using the
listview for data-objects. (System.data). In that is the DataGrid and the
DataGridView optimalized. The listview will give you only as mostly all
who tried this sooner or later a lot of trouble.

However if you persist, while I think that even the TreeView can be
better about what you wrote to Bill.

You can get from every datarow with relations its childs and it parent if
those exist.

Just my thought,

Cor


Nov 29 '05 #8

P: n/a
Kay,

I made a sample from it, I thougth it works.

http://www.vb-tips.com/default.aspx?...3-8a9529b2b38b

I hope this helps,

Cor
"Cor Ligthert [MVP]" <no************@planet.nl> schreef in bericht
news:Of**************@TK2MSFTNGP11.phx.gbl...
Kay,

Don't be afraid for looping that is done so many times behind the scene
and you don't see it.

Another approach (I did not test it) is setting an extra columns to your
table "Author"

Than you can use in that an expression to the parent.

http://msdn.microsoft.com/library/de...ionColumns.asp

Than use the expression
http://msdn.microsoft.com/library/de...ssionTopic.asp

And than this part in it.
PARENT/CHILD RELATION REFERENCING

A parent table may be referenced in an expression by prepending the column
name with Parent. For example, the Parent.Price references the parent
table's column named Price.

Than you can use that datatable to use in a datagrid, and with nice
columnstyles you get in my opinion a nicer view on it than with a
listview.

I would give it a try in your case.

Cor


"Kay" <kk@micxsoft.com> schreef in bericht
news:Ou**************@TK2MSFTNGP11.phx.gbl...
Hi Cor,

I though of datagrid before but user must click on a link to see the
child's data - since I simply want to show the Agent's name (instead of
AgentKey), it seems listview may do the job - if I can retrive the data
from a child table, let me explain :

In roster table it has all the shift info like AgentID/date/time/campaign
etc, so a roster record in the listview will looks like that

Agent Name/Shift Date/Shift Start/Shift End
Kay/01-11-2005/10.00/18.00

They're in one row although the Agent name is coming from a different
table. I've created a relation in a dataset, after I done a bit more
reseach I found the GetChildRows method, however I need to loop thru the
whole Agent table to get the name. I'm worring on the performance coz I
need to link to 3 different table to display one roster record, and there
may hundreds or roster records in the listview...

Do you think I'm on the right track?

Thanks!
Kay
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Kay,

I first did not see that Bill gave you the correct answer.And now I see
he does not need the sample I have given him in a latter post.

What I wanted to write to you was, that you would benefit not using the
listview for data-objects. (System.data). In that is the DataGrid and
the DataGridView optimalized. The listview will give you only as mostly
all who tried this sooner or later a lot of trouble.

However if you persist, while I think that even the TreeView can be
better about what you wrote to Bill.

You can get from every datarow with relations its childs and it parent
if those exist.

Just my thought,

Cor



Nov 29 '05 #9

P: n/a
Kay
Hi Cor,

Just a quick reply & thanks for your info :D !! I'll try the datagrid and
listview see which one is better for my case. Coz there're other issues I
need to consider if I use datagrid.

Also a question... in your example:

DataGridView1.DataSource = dt2

What is the > DataGridView1 < ?

Kay :)
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl...
Kay,

I made a sample from it, I thougth it works.

http://www.vb-tips.com/default.aspx?...3-8a9529b2b38b

I hope this helps,

Cor
"Cor Ligthert [MVP]" <no************@planet.nl> schreef in bericht
news:Of**************@TK2MSFTNGP11.phx.gbl...
Kay,

Don't be afraid for looping that is done so many times behind the scene
and you don't see it.

Another approach (I did not test it) is setting an extra columns to your
table "Author"

Than you can use in that an expression to the parent.

http://msdn.microsoft.com/library/de...ionColumns.asp

Than use the expression
http://msdn.microsoft.com/library/de...ssionTopic.asp

And than this part in it.
PARENT/CHILD RELATION REFERENCING

A parent table may be referenced in an expression by prepending the
column name with Parent. For example, the Parent.Price references the
parent table's column named Price.

Than you can use that datatable to use in a datagrid, and with nice
columnstyles you get in my opinion a nicer view on it than with a
listview.

I would give it a try in your case.

Cor


"Kay" <kk@micxsoft.com> schreef in bericht
news:Ou**************@TK2MSFTNGP11.phx.gbl...
Hi Cor,

I though of datagrid before but user must click on a link to see the
child's data - since I simply want to show the Agent's name (instead of
AgentKey), it seems listview may do the job - if I can retrive the data
from a child table, let me explain :

In roster table it has all the shift info like
AgentID/date/time/campaign etc, so a roster record in the listview will
looks like that

Agent Name/Shift Date/Shift Start/Shift End
Kay/01-11-2005/10.00/18.00

They're in one row although the Agent name is coming from a different
table. I've created a relation in a dataset, after I done a bit more
reseach I found the GetChildRows method, however I need to loop thru the
whole Agent table to get the name. I'm worring on the performance coz I
need to link to 3 different table to display one roster record, and
there may hundreds or roster records in the listview...

Do you think I'm on the right track?

Thanks!
Kay
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Kay,

I first did not see that Bill gave you the correct answer.And now I see
he does not need the sample I have given him in a latter post.

What I wanted to write to you was, that you would benefit not using the
listview for data-objects. (System.data). In that is the DataGrid and
the DataGridView optimalized. The listview will give you only as mostly
all who tried this sooner or later a lot of trouble.

However if you persist, while I think that even the TreeView can be
better about what you wrote to Bill.

You can get from every datarow with relations its childs and it parent
if those exist.

Just my thought,

Cor



Nov 30 '05 #10

P: n/a
Kay,

The datagridview is the replacement of the datagrid in Net 2.0, it has all
fancy features build in which you have to do in the datagrid by extra
classes by instance a combobox.

As well is by instance the allowedit, allowadd now a part of the
datagridview, with a datagrid, you needed to set a dataview with that and
was direct in trouble with a child master datagrid with a relation.

You can use the datagrid however as well in Net 2.0

I hope this gives some idea's

Cor
Nov 30 '05 #11

P: n/a
Kay
Hi Cor,

O I C~ No wonder I can't find the datagridview!

At the moment I'm also trying to building a sql so I can gather all
information I need in a row, it seems a bit easier and more efficient. But
no matter what method I'll use your input are highly appreciated and for
sure it'll be a very good reference for me in the future! Thx for your
effort !

Kay :D
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Oi**************@TK2MSFTNGP10.phx.gbl...
Kay,

The datagridview is the replacement of the datagrid in Net 2.0, it has all
fancy features build in which you have to do in the datagrid by extra
classes by instance a combobox.

As well is by instance the allowedit, allowadd now a part of the
datagridview, with a datagrid, you needed to set a dataview with that and
was direct in trouble with a child master datagrid with a relation.

You can use the datagrid however as well in Net 2.0

I hope this gives some idea's

Cor

Dec 1 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.