Displaying multiple dataset tables in a datalist

Colonel Angus · May 10th, 2005, 12:57 PM

I have a dataset with two tables and I have one datalist on my page that I want to display that data. I can easily bind table A to the datalist, but how can I involve table B as another column?

Can I somehow take the 2 dataset tables and merge them into one?

Thanks in advance for your comments.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

jbenson001 · May 10th, 2005, 02:36 PM

Do you need separate tables. Maybe you can combine your results into one table. This would be easy if you are using a stored procedure.

planoie · May 10th, 2005, 02:41 PM

Do you want to merge the tables or do you want to show the second table as it relates to the first, as a child?

This sample shows the basics of doing an "inner binding".

http://www.geekdork.com/samples/complexDatagrid.aspx

-Peter

Colonel Angus · May 11th, 2005, 07:11 AM

Gentlemen, thank you for your comments.

Having the two combine into a single stored procedure was my initial intention, but the query was a rather gnarly one, and the DBA I work with and I both agreed it was a better idea to split it into two procedures, each returning a table in the dataset (they match one to one on a related field).

I don't want to show the second table as a child. I want to take the data from the second table and display it as just another column in the datalist. I have toyed with the idea of placing another, separately bound datalist next to the first one and working the HTML to display them consistently. But if I can get it into one datalist, that would be best.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Prashant.k.m · May 11th, 2005, 07:27 AM

hi,

As Peter's said, go for Data table relations, thats the best one.

Prashant

planoie · May 11th, 2005, 09:13 AM

Colonel,

Yeah, set up a relationship. The second table is not necessarily a child table per se, but the rows it contain are related in such a way that you can have the second table related with a parent-child relation to get your 1 to 1 relationship. Then your datalist can be constructed, bound to one table, and have any additional columns you want related by that relationship in the same way as that example I posted does it.

(BTW- You missed the INETA speaker yesterday.)

-Peter

Colonel Angus · May 11th, 2005, 10:44 AM

I looked at the code in your link, and I'm on the road to implementing it (meetings and endless voicemails tend to slow me down). I see what you are saying about the table relationship. It's more of a table join than anything else. Thanks for the help!

P.S. - I gotta keep up with checking the meeting info :) I hope I didn't miss too much.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Colonel Angus · May 11th, 2005, 11:06 AM

Peter-

I'm very close, but I can't close the deal. Here's my code:

Code:

Dim ParentCol As DataColumn = dsData.Tables("OpenList").Columns("CaseID")
Dim ChildCol As DataColumn = dsData.Tables("NarDate").Columns("CounselingRecordID")
Dim relation As New DataRelation("OpenListDates", ParentCol, ChildCol)
dsData.Relations.Add(relation)
dlListing.DataSource = dsData.Relations("relation")
dlListing.DataSource = dsData.Tables("OpenListDates").DefaultView
dlListing.DataBind()

Note the two DataSource settings. The first one results in no data returned and the second one gives an object not set to an instance error.

I have a feeling that the first one has more promise. I noticed in your code the ChildView being used, but I don't know how to access it from this standpoint.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

planoie · May 11th, 2005, 01:34 PM

First off, the second try has an incorrect table name:

OpenListDates should be OpenList

Looks like you were trying to bind to the result of the relationship?
This second approach is the one you want. Your datalist will need some fancy databinding syntax to dig out the related data. It will probably be a little tricky because you aren't aiming to bind a repeating control to a collection, but rather a single child item.

I think the binding syntax will be along these lines:

Container.DataItem.CreateChildView("OpenListDates" ).Item(0).Item(column#)

CreateChildView will return the DataView of the related table (only the applicable rows, in your case just 1). You take the first row, then the appropriate column.

Hopefully that will get you headed in the right direction.

-Peter

Colonel Angus · May 11th, 2005, 01:46 PM

Thanks Peter.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.