asked on

Parent/child Gridview (i.e., nested) controls: Filtering child GridView using row IDs of parent table

Greetings,

Here's the problem: I have two GridView tables which I am trying to display in a parent/child fashion. I would like to use the primary key ID in each row of the parent table as a filter for the SQL query which is used to generate the child table for that row. I would also like all child tables displayed by default (in other words I don't want users have to "select" individual rows in order to display the corresponding child table). Unfortunately right now the child table of the first row is repeating in each subsquent parent table row. Here is my ASPX page code:

<asp:GridView ID="ParentGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="ParentSqlDataSource">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:TemplateField HeaderText="Topics">
      <ItemTemplate>
            <asp:GridView ID="ChildGridView" runat="server" DataSourceID="ChildSqlDataSource"
            AutoGenerateColumns="False">
                  <Columns>
                   <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
                   <asp:BoundField DataField="Topic" HeaderText="Topic" ReadOnly="True"
                   SortExpression="Topic" />
                  </Columns>
            </asp:GridView>
                  <asp:SqlDataSource ID="ChildSqlDataSource" runat="server"
                  ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                  SelectCommand="SELECT [ID], [Topic] FROM [tblTopics] WHERE (ID = @ID)">
                  <SelectParameters>
                  <asp:ControlParameter Name="ID" ControlID="ParentGridView"
                  PropertyName="SelectedDataKey.Values[ID]" />
                  </SelectParameters>
                  </asp:SqlDataSource>

      </ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="ParentSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ID], [Title] FROM [tblTitles]">
</asp:SqlDataSource>

Any suggestions? At this point I am wondering if I need a totally new approach to the problem? Any help would be much appreciated.

Thanks!

relaydude

ASKER

I am upping the points to the 500 pt max. I am hoping to find a solution soon because the deadline for my project is just a few days off.

carlnorrbom

Hi,

You could do something like the attached and it should do the trick.

/Carl.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="NestedGridPage.aspx.vb"
    Inherits="NestedGridPage" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="ParentGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
            DataSourceID="ParentSqlDataSource">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
                <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                <asp:TemplateField HeaderText="Topics">
                    <ItemTemplate>
                        <table style="border-collapse:collapse;">
                            <tr>
                                <td><asp:Label ID="lblID" runat="server" /></td>
                                <td><asp:Label ID="lblTopic" runat="server" /></td>
                            </tr>
                        </table>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="ParentSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT [ID], [Title] FROM [tblTitles]"></asp:SqlDataSource>
    </div>
    </form>
</body>
</html>
 
Imports System.Data.SqlClient
Imports System.Data
 
Partial Class NestedGridPage
    Inherits System.Web.UI.Page
 
    Private sqlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
    Private sqlAdapter As New SqlDataAdapter("SELECT [ID], [Topic] FROM [tblTopics] WHERE (ID = @ID)", sqlconn)
    Private dt As New DataTable()
 
    Protected Sub ParentGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles ParentGridView.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            If dt.Rows.Count > 0 Then
                dt.Rows.Clear()
            End If
            sqlAdapter.SelectCommand.Parameters.Clear()
            sqlAdapter.SelectCommand.Parameters.AddWithValue("@ID", Convert.ToInt32(e.Row.Cells(0).Text.ToString()))
            sqlAdapter.Fill(dt)
            If dt.Rows.Count > 0 Then
                DirectCast(e.Row.FindControl("lblID"), Label).Text = dt.Rows(0).Item("ID").ToString()
                DirectCast(e.Row.FindControl("lblTopic"), Label).Text = dt.Rows(0).Item("Topic").ToString()
            End If
        End If
    End Sub
 
End Class

Open in new window

relaydude

ASKER

Carl...thanks for the code. When I tried running it I got the following error message:

"Handles clause requires a WithEvents variable defined in the containing type or one of its base types error"

I am not sure if this is correct, but I tried adding the following line to the code behind file:

Public WithEvents ParentGridView As GridView

After adding this line I no longer get the error message about the Handles clause needing WithEvents. However, when I run the page the child table is coming up empty. Any other suggestions would be much appreciated.

carlnorrbom

Hi,

Something must have gotten cooked up in your code. It runs just fine for me. Post your complete code and maybe I can find out where things are going wrong.

/Carl.

relaydude

ASKER

Thanks Carl...I really appreciate the help.

I just recreated clean pages and your code now runs. However, only the first row of the child table displays. Here is a simplified version of the database tables involved.

Parent table: tblTitles

ID Title
1 New Horizons in Science
2 Genetic Engineering
3 The Cloning Controversy
4 Food and Family Health
5 Politics and Science

ChildTable: tblTopics

ID Topic
1 Social aspects
1 Political controversy
1 Ethics
2 Food safety
2 Regulation

For each title (in other words parent table row) I need that row's child table to display all the applicable child table topics. So for row 1 and 2 I am need something like this:

1 New Horizons in Science
1 Social aspects
1 Political controversy
1 Ethics
2 Genetic Engineering
2 Food safety
2 Regulation

Again, right now I only get the first row of the child tables. I need all applicable child table rows to show where the ID matches the parent row ID.

********************************************************************************************************************
Below is the full code I am using.

File: NestedGridPage.aspx.vb

Imports System.Data.SqlClient
Imports System.Data

Partial Class NestedGridPage
Inherits System.Web.UI.Page

Private sqlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Private sqlAdapter As New SqlDataAdapter("SELECT [ID], [Topic] FROM [tblTopics] WHERE (ID = @ID)", sqlconn)
Private dt As New DataTable()

Protected Sub ParentGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles ParentGridView.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
If dt.Rows.Count > 0 Then
dt.Rows.Clear()
End If
sqlAdapter.SelectCommand.Parameters.Clear()
sqlAdapter.SelectCommand.Parameters.AddWithValue("@ID", Convert.ToInt32(e.Row.Cells(0).Text.ToString()))
sqlAdapter.Fill(dt)
If dt.Rows.Count > 0 Then
DirectCast(e.Row.FindControl("lblID"), Label).Text = dt.Rows(0).Item("ID").ToString()
DirectCast(e.Row.FindControl("lblTopic"), Label).Text = dt.Rows(0).Item("Topic").ToString()
End If
End If
End Sub

End Class

File: NestedGridPage.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="NestedGridPage.aspx.vb"
Inherits="NestedGridPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form2" runat="server">
<div>
<asp:GridView ID="ParentGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="ParentSqlDataSource">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:TemplateField HeaderText="Topics">
<ItemTemplate>
<table style="border-collapse:collapse;">
<tr>
<td><asp:Label ID="lblID" runat="server" /></td>
<td><asp:Label ID="lblTopic" runat="server" /></td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="ParentSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ID], [Title] FROM [tblTitles]"></asp:SqlDataSource>
</div>
</form>
</body>
</html>

ASKER CERTIFIED SOLUTION

carlnorrbom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

relaydude

ASKER

Perfect...works like a charm! Thanks Carl for the thorough and quick response. You really helped me out. Thanks again!