JeffDrummond
asked on
Dataset/datatables as a source for a Datalist/Datagrid combination
I have a report with a datagrid in the ItemTemplateColumn of a datalist.
The datalist is populated with one stored procedure call to the database
and the datagrid with a second stored procedure call on every datalist
ItemDataBound event.
I want to change this.
I want to make one stored procedure call and populate one dataset
with two datatables containing the info I need and create a
datarelation to link the two.
I can see how to get it going to the point where the datalist item
is being populated, but at that moment I don't know how to get to the
dataset and get the data I need for the datagrid.
Is this feasible and if so can you help me where I'm stuck?
Thanks!
The datalist is populated with one stored procedure call to the database
and the datagrid with a second stored procedure call on every datalist
ItemDataBound event.
I want to change this.
I want to make one stored procedure call and populate one dataset
with two datatables containing the info I need and create a
datarelation to link the two.
I can see how to get it going to the point where the datalist item
is being populated, but at that moment I don't know how to get to the
dataset and get the data I need for the datagrid.
Is this feasible and if so can you help me where I'm stuck?
Thanks!
ASKER
Yes, I have the dataset populated with two tables
and a datarelation conecting the two.
The datalist is bound to Table 1.
When a datalist row is populated, I need to
populate the datagrid with the child rows in Table 2.
This is where I'm stuck. How to get to the datasource
for the datagrid.
and a datarelation conecting the two.
The datalist is bound to Table 1.
When a datalist row is populated, I need to
populate the datagrid with the child rows in Table 2.
This is where I'm stuck. How to get to the datasource
for the datagrid.
To implement the master - details datagrid, you must bind the details grid to the relationship object.
Have a look at this msdn walkthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingmasterdetailslistwithdatagrid.asp
Have a look at this msdn walkthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingmasterdetailslistwithdatagrid.asp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have the datarelation set up. When I run the report now I am getting
an error: "A field or property with the name 'CaseDescript' was not found on the selected datasource."
The datasource is my datatable dtCaseTypes and the 'CaseDescript' column IS present in the
datasource.
This is my code-behind:
dtActivJPO = New DataTable
dtCaseTypes = New DataTable
'--set datatable to first table in dataset
dtActivJPO = ds.Tables(0)
'--set datatable to second table in dataset
dtCaseTypes = ds.Tables(1)
'--create relationship between the two datatables
drl = New DataRelation("JPOCaseTypes ", _
ds.Tables(0).Columns("Empl oyeeID"), _
ds.Tables(1).Columns("Empl oyeeID"), False)
'--add the relationship to the dataset
ds.Relations.Add(drl)
dvActivJPO = New DataView(dtActivJPO)
dlActivJPO.DataSource = dvActivJPO
dlActivJPO.DataBind()
This is the datagrid which displays the child rows:
<asp:datagrid id=dgJPOCaseTypes runat="server" Width="770px" ShowFooter="True" CellPadding="2" GridLines="None" BorderWidth="0px" DataSource='<%# Container.DataItem.Row.Get ChildRows( "JPOCaseTy pes") %>' autogeneratecolumns="False ">
<Columns>
<asp:BoundColumn DataField="CaseDescript" SortExpression="CaseDescri pt" HeaderText="Caseload Types Assigned">
<HeaderStyle HorizontalAlign="Left" CssClass="ProductHeader">< /HeaderSty le>
<ItemStyle HorizontalAlign="Left" CssClass="ItemStyle" VerticalAlign="Top"></Item Style>
</asp:BoundColumn>
</Columns>
</asp:datagrid></TD>
an error: "A field or property with the name 'CaseDescript' was not found on the selected datasource."
The datasource is my datatable dtCaseTypes and the 'CaseDescript' column IS present in the
datasource.
This is my code-behind:
dtActivJPO = New DataTable
dtCaseTypes = New DataTable
'--set datatable to first table in dataset
dtActivJPO = ds.Tables(0)
'--set datatable to second table in dataset
dtCaseTypes = ds.Tables(1)
'--create relationship between the two datatables
drl = New DataRelation("JPOCaseTypes
ds.Tables(0).Columns("Empl
ds.Tables(1).Columns("Empl
'--add the relationship to the dataset
ds.Relations.Add(drl)
dvActivJPO = New DataView(dtActivJPO)
dlActivJPO.DataSource = dvActivJPO
dlActivJPO.DataBind()
This is the datagrid which displays the child rows:
<asp:datagrid id=dgJPOCaseTypes runat="server" Width="770px" ShowFooter="True" CellPadding="2" GridLines="None" BorderWidth="0px" DataSource='<%# Container.DataItem.Row.Get
<Columns>
<asp:BoundColumn DataField="CaseDescript" SortExpression="CaseDescri
<HeaderStyle HorizontalAlign="Left" CssClass="ProductHeader"><
<ItemStyle HorizontalAlign="Left" CssClass="ItemStyle" VerticalAlign="Top"></Item
</asp:BoundColumn>
</Columns>
</asp:datagrid></TD>
ASKER
I've got this up and running. The columns not being found
in my previous post were BoundColumns, and I needed to
change them to template columns in order for the
GetChildRows to work.
So using the datarelation and the GetChildRows
method works perfectly.
in my previous post were BoundColumns, and I needed to
change them to template columns in order for the
GetChildRows to work.
So using the datarelation and the GetChildRows
method works perfectly.
SELECT * FROM Table1;SELECT * FROM Table2;SELECT * FROM Table3
When loaded into a dataset, you will get 3 tables in your dataset with the results from the 3 queries.
You can then bind the individual tables to the controls by references the tables by ordinal number:
myControl.datasource = myDs.tables(0);
myOtherControl.datasource = myDs.tables(1);
HTH,
Greg