Working with more than 2 nested repeater controls

Hi all,

I have successfully been able to use two asp:repeater controls on my page, however I am now trying to add a third repeater control and it's confusing me!

The example data output below is what I would like to have. At the moment I am displaying the Office and Departments fine. I now am trying to display the departments children which are Job Roles.


Office Name
    Department Name #1
        ¤ Chief Executive
        ¤ PA to Chief Guide
        ¤ PA to the Chief Executive
    Department Name #2
        ¤ Facilities Administrator
        ¤ Facilities Assistant
        ¤ Facilities Manager
        ¤ Facilities Technical Assistant
    Department Name #3
        ¤ Assistant Finance Officer
        ¤ Finance Officer
        ¤ Head of Finance & Business Development
        ¤ Management Accountant
        ¤ PA to Head of Finance
        ¤ Payment and Receipts Officer
    Department Name #4
        ¤ Head of Human Resources
        ¤ Human Resources Advisor
        ¤ Human Resources Co-ordinator
    Department Name #5
        ¤ Application Developer
        ¤ Business Systems Manager
        ¤ Database Administrator
        ¤ Development Manager
        ¤ I.T. Services Manager


My .aspx and .aspx.vb code are as follows ....



    <form id="Form1" method="post" runat="server">
      <div>
        <br />

        <ASP:Repeater id="rptrOffices" runat="server">

          <HeaderTemplate>
                    <table width="100%" cellspacing="1" cellpadding="1" border="0" class="tableOrangeDisplay">
                          <tr>
                                <th>Office / Department</th>
                                <th width="35">Action</th>
                              </tr>
          </HeaderTemplate>

          <ItemTemplate>
            <tr>
                  <td colspan="2"><strong><%#DataBinder.Eval(Container.DataItem, "Address1")%></strong></td>
            </tr>

            <asp:Repeater id="rptrDepartments" runat="server" datasource='<%# Container.DataItem.Row.GetChildRows("Office_Dept_Relationship") %>'>
              <itemtemplate>
                            <tr>
                                  <td colspan="2">&nbsp;&nbsp;&nbsp;&nbsp;<u><%#Container.DataItem("DepartmentName")%></u></td>
                            </tr>


                <!--   < asp: Repeater id="rptrJobRoles" run at="server" datasource='??????????????????' > -->
                  <!--  < itemtemplate > -->
                                <tr>
                                      <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - JOB ROLES HERE</td>
                                      <td>[edit]</td>
                                </tr>
                                <tr>
                                      <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - JOB ROLES HERE</td>
                                      <td>[edit]</td>
                                </tr>
                                <tr>
                                      <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - JOB ROLES HERE</td>
                                      <td>[edit]</td>
                                </tr>
                  <!--  < /itemtemplate > -->
                <!--  < /asp: Repeater > -->


              </itemtemplate>
            </asp:Repeater>

          </ItemTemplate>
         
          <FooterTemplate>
            </table>
          </FooterTemplate>

        </asp:Repeater>

      </div>
            </form>



---------------------------------------------------------------------------------------------------------------

Imports System.Data
Imports System.Data.SqlClient


Partial Class system_jobRoles_Default
    Inherits System.Web.UI.Page

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim strSQL As String

        'Create Dataset
        Dim objDataSet As DataSet = New DataSet()

        'Create SQL connection
        Dim objConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("eLaunch").ConnectionString)

        'Populate command with 1st dataset
        Dim cmd1 As SqlDataAdapter = New SqlDataAdapter("SELECT DISTINCT O.[OfficesID], O.[address1] FROM [G_Tbl_Offices] O INNER JOIN [G_Tbl_Department] D ON O.[OfficesID] = D.[OfficesID] ORDER BY O.[Address1]", objConn)
        cmd1.Fill(objDataSet, "offices")

        'Populate command with 2nd dataset
        Dim cmd2 As SqlDataAdapter = New SqlDataAdapter("SELECT D.[DepartmentID], D.[DepartmentName], D.[OfficesID] FROM [G_Tbl_Department] D", objConn)
        cmd2.Fill(objDataSet, "departments")


        'Populate command with 3rd dataset
        strSQL = "SELECT       J.[JobRolesID], " & _
                 " J.[JobRole], " & _
                 " J.[Tbl_DepartmentID] " & _
                 "  FROM       [G_Tbl_JobRoles] J " & _
                 "      INNER JOIN [G_Tbl_Department] D ON J.[Tbl_DepartmentID] = D.[DepartmentID] " & _
                 "      INNER JOIN [G_Tbl_Offices] O ON D.[OfficesID] = O.[OfficesID] " & _
                 "  ORDER BY J.[JobRole]"

        Dim cmd3 As SqlDataAdapter = New SqlDataAdapter(strSQL, objConn)
        cmd3.Fill(objDataSet, "jobroles")

        'Assign parent / child columns for Office/Department relationship
        Dim Parent As DataColumn = objDataSet.Tables("offices").Columns("OfficesID")
        Dim Child As DataColumn = objDataSet.Tables("departments").Columns("OfficesID")
        Dim objRelation As DataRelation = New DataRelation("Office_Dept_Relationship", Parent, Child, False)
        objDataSet.Relations.Add(objRelation)

        'Assign parent / child columns for Department/Job Role relationship
        Parent = objDataSet.Tables("departments").Columns("DepartmentID")
        Child = objDataSet.Tables("jobroles").Columns("Tbl_DepartmentID")
        objRelation = New DataRelation("Dept_JobRole_Relationship", Parent, Child, False)
        objDataSet.Relations.Add(objRelation)

        'Assign repeater a datasource
        Me.rptrOffices.DataSource = objDataSet.Tables("offices")

        'Bind data to page
        Page.DataBind()

        'Clean up
        objDataSet.Dispose()
        cmd1.Dispose()
        cmd2.Dispose()
        objConn.Close()

    End Sub

End Class



So if anyone can help explain how I should go about displaying the Departments Job Roles I would be very thankful!






GuidesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
have you tried

datasource='<%# Container.DataItem.Row.GetChildRows("Dept_JobRole_Relationship") %>'
0
appariCommented:
or this

datasource='<%# Container.DataItem.GetChildRows("Dept_JobRole_Relationship") %>'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GuidesAuthor Commented:
Hi Appari,

Thanks for your help, your second suggestion has worked so I shall give you the full points. Much appreciated!

Could you please explain why the datasource without ".Row." would work? What does .Row. do?

Another quick question ... is the way I have suggested above the best solution in ASP.Net? I'm only new to this (have been doing classic ASP for years!) and would like some feedback.

In classic ASP I did the following which seems easier with the one SQL statement ... See below ...

SQL STATEMENT
--------------------
SELECT       J.[JobRolesID],
      J.[JobRole],
      J.[Description],
      J.[Tbl_DepartmentID],
      D.[DepartmentName],
      D.[OfficesID],
      O.[Code]
FROM       [G_Tbl_JobRoles] J
      INNER JOIN [G_Tbl_Department] D ON J.[Tbl_DepartmentID] = D.[DepartmentID]
      INNER JOIN [G_Tbl_Offices] O ON D.[OfficesID] = O.[OfficesID]
ORDER BY O.[Code],
       D.[DepartmentName],
       J.[JobRole]




CLASSIC ASP CODE
-----------------------

<%
            '0 J.[JobRolesID],
            '1 J.[JobRole],
            '2 J.[Description],
            '3 J.[Tbl_DepartmentID],
            '4 D.[DepartmentName],
            '5 D.[OfficesID],
            '6 O.[Code]
            if isArray(arrAllJobRoles) then
%>
                  <table width="100%" cellspacing="1" cellpadding="1" border="0" class="tableOrangeDisplay">
                        <tr>
                              <th>Department / Job Role</th>
                              <th width="40">Action</th>
                        </tr>
<%
                  for i = LBound(arrAllJobRoles, 2) To UBound(arrAllJobRoles, 2)

                        if (lngPreviousOfficeID <> arrAllJobRoles(5, i)) then
%>
                        <tr>
                              <td colspan="2" bgcolor="#F8F8F8"><strong><%=Ucase(arrAllJobRoles(6, i))%></strong></td>
                        </tr>
<%
                        end if

                        if (lngPreviousDepartmentID <> arrAllJobRoles(3, i)) then
%>
                        <tr>
                              <td colspan="2">&nbsp; &nbsp; <u><%=arrAllJobRoles(4, i)%></u></td>
                        </tr>
<%
                        end if
%>
                        <tr>
                              <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &curren; <%=arrAllJobRoles(1, i)%></td>
                              <td align="right"><a href="#" onclick="window.open('edit.asp?JobRoleID=<%=arrAllJobRoles(0, i)%>','detailedContent');"><img src="/system/com/img/edit.gif" width="16" height="16" alt="Edit"></a></td>
                        </tr>
<%
                        lngPreviousDepartmentID = arrAllJobRoles(3, i)
                        lngPreviousOfficeID     = arrAllJobRoles(5, i)
                  next
%>
                  </table>
<%
            end if
%>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.