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

relaydude
relaydude used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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

Author

Commented:
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.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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.

Author

Commented:
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>
Hi,

Sorry, i didn't realise it wasn't a one to one relationship. Please see the attached amended code on how you can do this.

/Carl.
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 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"
                    ItemStyle-VerticalAlign="Top" />
                <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" ItemStyle-VerticalAlign="Top" />
                <asp:TemplateField HeaderText="Topics" ItemStyle-VerticalAlign="Top">
                    <ItemTemplate>
                        <asp:PlaceHolder ID="phTopics" runat="server" />
                    </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>
 
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
                Dim phTopics As PlaceHolder = CType(e.Row.FindControl("phTopics"), PlaceHolder)
                phTopics.Controls.Add(New LiteralControl("<table><tr><td>&nbsp;</td><td>&nbsp;</td></tr>"))
                Dim i As Integer = 0
                For Each row As DataRow In dt.Rows
                    phTopics.Controls.Add(New LiteralControl("<tr>"))
                    phTopics.Controls.Add(New LiteralControl("<td>" & dt.Rows(i).Item("ID").ToString() & "</td>"))
                    phTopics.Controls.Add(New LiteralControl("<td>" & dt.Rows(i).Item("Topic").ToString() & "</td>"))
                    phTopics.Controls.Add(New LiteralControl("</tr>"))
                    i += 1
                Next
                phTopics.Controls.Add(New LiteralControl("</table>"))
                i = 0
            End If
        End If
    End Sub
 
End Class

Open in new window

screenshot-nestedgrids.png

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial