Nested Repeater Puzzle: Using data returned from (outer) query, in a nested query

Question

Hope you are having a good start today on any projects you are working on. I'm starting today with an interesting problem I need to solve involving a Nested repeater in ASP.net (to take in data from an SQL database to build webpage results for grant data related to education and welfare).

I have one repeater which is using an SQL query to pull back data based on a specific column in my SQL table, the Category column:

<asp:SqlDataSource ID="SqlDataSourceGrantCategories" runat="server" 
ConnectionString="<%$ ConnectionStrings:KenticoCMSECommerceTestingConnectionString %>" 
SelectCommand="SELECT * FROM [customtable_SampleTable] ORDER BY Category">
</asp:SqlDataSource>

Now I would like to have an additional, nested repeater which has another query providing data to it, but this inner repeater bases it's query off of the Category column (from the SQL table) as pulled by the first repeater. Here is what I have so far:

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSourceGrantCategories">
  <ItemTemplate>
  <%# Eval("Category") %> //I just need this piece of data, but used below.
  <br />
      <asp:Repeater ID="Repeater2" runat="server" DataSourceID="SqlDataSourceGrantInfo">
      <asp:SqlDataSource ID="SqlDataSourceGrantInfo" runat="server" ConnectionString="<%$ ConnectionStrings:KenticoCMSECommerceTestingConnectionString %>
    SelectCommand="SELECT * FROM [customtable_SampleTable] WHERE Category = <%# Eval('Category') %>">
    <ItemTemplate>
    <%# Eval("Title") %>
    </ItemTemplate>
  </asp:Repeater>
  <br />
  </ItemTemplate>
</asp:Repeater>

So basically, the Eval statement inside of the second, nested SqlDataSource is not being evaluated properly, it is giving me an error which says "Server tag not well formed". Is there any way for me to use or make a variable equal to the first Eval("Category") statement, which I can then use in my second SQL Query string?

Sorry if this is confusing, I'd be happy to elaborate on this further if needed.

Andrei Andrei · Accepted Answer · 2013-07-26T14:46:48

Couple of things that should be fixed here:

The error "Server tag not well formed" was due to missed double quote after ConnectionString attribute.
Remember that everything that goes inside <%# %> must be a valid C# code, which means that that if your are using a string - you should enclose it into double quotes. Typical pattern for quotes here is AttributeName='<%# Eval("Property")%>'. That was not the case in the SelectCommand value.
When you need to manipulate with both literals and bound data, like in the select query here, it is better to enclose the whole expression in the servlet tags and perform necessary transformations inside.

All in all, here is the result:

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSourceGrantCategories">
    <ItemTemplate>
        <%# Eval("Category") %>
        <br />
        <asp:SqlDataSource ID="SqlDataSourceGrantInfo" runat="server" ConnectionString="<%$ ConnectionStrings:KenticoCMSECommerceTestingConnectionString %>"
            SelectCommand='<%# "SELECT * FROM [customtable_SampleTable] WHERE Category =" + Eval("Category") %>' />
        <asp:Repeater ID="Repeater2" runat="server" DataSourceID="SqlDataSourceGrantInfo">
            <ItemTemplate>
                <%# Eval("Title") %>
            </ItemTemplate>
        </asp:Repeater>
        <br />
    </ItemTemplate>
</asp:Repeater>

Lastly it would be better, for several reasons, to use SelectParameters instead of inserting Category value directly into the query.

Nested Repeater Puzzle: Using data returned from (outer) query, in a nested query

1 Answers