Nested Repeater with SqlDataSource Parameters

If you ever worked with ASP.NET, at some point you have wanted to have do a nested data list. I have seen many people post solutions but nothing that I have seen has been acceptable solution. I need a way to use a field from the outer repeater and use it as a sql data source paremeter for the inner repeater.

Can anyone please provide a direct solution. I have provided my most recent code to see what I am trying to go for.

Thanks in Advance.
<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater_ItemDataBound"  DataSourceID="SqlDataSource4">
        <ItemTemplate>
            <li><b>
                <%# DataBinder.Eval(Container.DataItem, "id") %>
            </b></li>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NutritionConnectionString %>"
                SelectCommand="SelectFeedingTube" SelectCommandType="StoredProcedure">
                <SelectParameters>
                <asp:Parameter Name="tubeID" Type="Int32" />
                </SelectParameters>
            </asp:SqlDataSource>
            <asp:Repeater ID="Repeater2" runat="server" DataSourceID="SqlDataSource2">
                <ItemTemplate>
                <%# DataBinder.Eval(Container.DataItem, "dateStart")%>
                </ItemTemplate>
            </asp:Repeater>
        </ItemTemplate>
    </asp:Repeater>3

    void Repeater_ItemDataBound(Object sender, RepeaterItemEventArgs e)
    {
        // Find the Inner DataSource control in this Row.
        SqlDataSource s = (SqlDataSource)e.Item.FindControl("SqlDataSource2");

        // Set the SelectParameter for this DataSource control
        // by re-evaluating the field that is to be passed.
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.EditItem)
        {
            s.SelectParameters["tubeID"].DefaultValue = DataBinder.Eval(e.Item.DataItem, "id").ToString();
        }
    }

Open in new window

spencerclarkAsked:
Who is Participating?
 
intlaqaCommented:
Hi,

I do not like it because of the performance, for example if you have 50 items in the parent repeater, you will connect to the database 50 times to get the child items for every parent item, a lot of round trips.

Anyway, if you want to make it, just place an ASP.NET control on the parent repeater ItemTemplate has the id of the parent item, for example:
<asp:Label ID="lblItemId" Text='<%# Eval("Id") %>' runat="server"></asp:Label>

Then, on the SqlDataSource2 (The child DataSource which gets the child items), add a ControlParameter to take the value from the parent control, for example:

<asp:ControlParameter ControlID="lblItemId" Name="tubeID" PropertyName="Text" Type="Int32" />

And that's it. Here is an example based on your code:


<%@ Page Language="C#" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource4">
        <HeaderTemplate><ul></HeaderTemplate>
        <ItemTemplate>
        
            <li><b><asp:Label ID="lblItemId" Text='<%# Eval("Id") %>' runat="server"></asp:Label></b></li>
            
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NutritionConnectionString %>"
                SelectCommand="SelectFeedingTube" SelectCommandType="StoredProcedure">
                <SelectParameters>
                <asp:ControlParameter ControlID="lblItemId" Name="tubeID" PropertyName="Text" Type="Int32" />
                </SelectParameters>
            </asp:SqlDataSource>
            
            <asp:Repeater ID="Repeater2" runat="server" DataSourceID="SqlDataSource2">
                <HeaderTemplate><ul></HeaderTemplate>
                <ItemTemplate>
                <li><%# Eval("dateStart") %></li>
                </ItemTemplate>
                <FooterTemplate></ul></FooterTemplate>
            </asp:Repeater>
        
        </ItemTemplate>
        <FooterTemplate></ul></FooterTemplate>
    </asp:Repeater>
    </form>
</body>
</html>

Open in new window

0
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.

All Courses

From novice to tech pro — start learning today.