nested queries using Repeater

[ctwilliams]

How do you list parent/child items on an ASP.NET page, grouped by the parent? For example, my data looks like this:

Category    Item
--------    -------
Cat 1       Item 1
Cat 1       Item 2
Cat 1       Item 3
Cat 2       Item 4
Cat 2       Item 5

I need to display it on my ASP.NET page like this:

Cat 1
Item 1, Item 2, Item 3

Cat 2
Item 4, Item 5

I am currently using a Repeater; maybe I need to use something else to do this? Here is my ASP.NET code...

Dim strSQL as String
Dim objCommand As SQLCommand
strSQL = "Select Category, Item FROM MyTable ORDER BY Category, Item"
objCommand = New SQLCommand(strSQL, objConn)
ResultsList.DataSource = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
ResultsList.DataBind()

----------------------
<asp:Repeater ID="ResultsList" runat="server">	
<itemTemplate>
<%#Container.DataItem("Category")%> - <%#Container.DataItem("Item")%><br>
</itemTemplate>
</asp:Repeater>

If I were using Classic ASP I would just do something like this...

stmt = "SELECT Category FROM MyTable"
set rs=conn.execute(stmt)
do while not rs.eof
    response.write rs("Category") & "<br>"
    stmt2 = "SELECT Item FROM MyTable WHERE Category = '" & rs("Category")  & "' "
    set rs2=conn.execute(stmt2)
    do while not rs2.eof
        response.write rs2("Item") & ", "
    rs2.movenext
    loop
rs.movenext
loop

 

[crazyboybert]

Hi ct

You can create a datarelation in a dataset to handle master child relationships and then bind this to nested repeaters to create a master-detail view.

Basically you create your UI, populate your dataset and create a datarelation, this can be between two tables or as would be more likely in your example self referencing within one datatable. Next you bind to the parent repeater and capture the ItemDataBound event to bind the child repeater to the child data based on the data relation.

A full code sample would be fairly heavy for here so better to have a look at this link

http://www.c-sharpcorner.com/Code/2002/Aug/MasterDetailedDisplay.asp

which will walk you through it...

This was number one on a Google for 'master detail repeater' btw

hope this helps

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927