Dataset - How to get data from a child table?

[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.Relati ons.Add("AgttoR ",
dsRoster.Tables ("Agent").Colum ns("Agent_Key") , dsRoster.Tables
("Roster").Colu mns("Agent_Key" ))

For Each rowRoster In dsRoster.Tables ("Roster").R ows
itmx = New ListViewItem
itmx.Text = rowRoster("Agen t_Roster_Key")
itmx.SubItems.A dd(rowRoster("N ame") '**** this doesn't
work ...
lvwRoster.Items .Add(itmx)
Next
Thanks~

Kay

[bill.jenner]

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.Fi ll(ds, "Customers" )
adpOrders.Fill( ds, "Orders")
adpOrderDetails .Fill(ds, "Order Details")
ds.Relations.Ad d("CustomersToO rders", _
ds.Tables("Cust omers").Columns ("CustomerID "), _
ds.Tables("Orde rs").Columns("C ustomerID"))

ds.Relations.Ad d("OrdersToOrde rDetails", _
ds.Tables("Orde rs").Columns("O rderID"), _
ds.Tables("Orde r Details").Colum ns("OrderID"))

dgCustomers.Dat aSource = ds
dgCustomers.Dat aMember = "Customers"

dgOrders.DataSo urce = ds
dgOrders.DataMe mber = "Customers.Cust omersToOrders"

dgOrderDetails. DataSource = ds
dgOrderDetails. DataMember =
"Customers.Cust omersToOrders.O rdersToOrderDet ails"
Hope it's what you are looking for.

[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*********@gm ail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.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.Fi ll(ds, "Customers" )
adpOrders.Fill( ds, "Orders")
adpOrderDetails .Fill(ds, "Order Details")
ds.Relations.Ad d("CustomersToO rders", _
ds.Tables("Cust omers").Columns ("CustomerID "), _
ds.Tables("Orde rs").Columns("C ustomerID"))

ds.Relations.Ad d("OrdersToOrde rDetails", _
ds.Tables("Orde rs").Columns("O rderID"), _
ds.Tables("Orde r Details").Colum ns("OrderID"))

dgCustomers.Dat aSource = ds
dgCustomers.Dat aMember = "Customers"

dgOrders.DataSo urce = ds
dgOrders.DataMe mber = "Customers.Cust omersToOrders"

dgOrderDetails. DataSource = ds
dgOrderDetails. DataMember =
"Customers.Cust omersToOrders.O rdersToOrderDet ails"
Hope it's what you are looking for.

[Cor Ligthert [MVP]]

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

[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******** ******@tk2msftn gp13.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

[Cor Ligthert [MVP]]

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 idcustomercolum n 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

[Cor Ligthert [MVP]]

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

[Cor Ligthert [MVP]]

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" <[e-mail] m> schreef in bericht
news:Ou******** ******@TK2MSFTN GP11.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******** ******@tk2msftn gp13.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

[Cor Ligthert [MVP]]

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******** ******@TK2MSFTN GP11.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" <[e-mail] m> schreef in bericht
news:Ou******** ******@TK2MSFTN GP11.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******** ******@tk2msftn gp13.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

[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.D ataSource = dt2

What is the > DataGridView1 < ?

Kay :)
"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:u3******** ******@TK2MSFTN GP10.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******** ******@TK2MSFTN GP11.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" <[e-mail] m> schreef in bericht
news:Ou******** ******@TK2MSFTN GP11.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******** ******@tk2msftn gp13.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