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

 

[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.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.OrdersToOrderDetails"


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

 

[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]]

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

 

[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/d...guide/html/cpconCreatingExpressionColumns.asp

Than use the expression
http://msdn.microsoft.com/library/d...fSystemDataDataColumnClassExpressionTopic.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

 

[Cor Ligthert [MVP]]

Kay,

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

http://www.vb-tips.com/default.aspx?ID=5fd5a8cf-54dc-4946-a193-8a9529b2b38b

I hope this helps,

Cor

 

[Kay]

Hi Cor,

Just a quick reply & thanks for your info !! 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]]

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

 

[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