Nested Repeaters and SqlDataSource Parameters

Question

I am using nested repeaters to build a table for reasons I won't discuss here, but what I'm looking to do is have two datasources, one for the top level repeater that will correspond to the rows, and one for the second level repeater that will return cells within a row.

What I'm wondering, however, is if I can somehow specify a parameter in the nested repeater's datasource that is set a field in the results from the first datasource?

Can I set a parameter to the value of a data binding expression?

The reason I want to do this is I have two stored procedures. When the page is loaded I have a session parameter I can use to run the first stored procedure, however, for the second stored procedure, I need to associate a value from each instance of the top level repeater with a call to the second stored procedure with a different parameter value.

Chaney

Tony, When you said "There was another thing in my setup that was wrong. My 2nd datasource was defined in the same spot as the first, so it was only set once. I fixed it, " where did you define it? I have tried defining the datasource inside or outside the outer repeater, and in both cases I get the same result: the select parameter is set only the first time, and never reset for a new value of the parameter? Can you please post a bit more of your code that worked in the end? Thanks. – Dipody

We ended up sticking with the Windows code, but I can see about getting the old code when I have some time. – Chaney

Answer 1

I think the best way would be to handle the ItemDataBound event of the Outer Repeater, Find the inner DataSource control and set a SelectParameter for it.

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

    // Set the SelectParameter for this DataSource control
    // by re-evaluating the field that is to be passed.
    s.SelectParameters["MyParam"].DefaultValue = DataBinder.Eval(e.Item.DataItem, "MyFieldValueToPass").ToString();
    }

For an example using the DataList, check out the ASP.NET quickstarts here

P.S.: Please see Tony's reply below for an important correction to the above presented snippet. Notably, it would be essential to check the ItemType of the current RepeaterItem. Alternatively, it's an excellent practice to always check for nulls on every object.

Wohlert

Please reload the page, I have amended my post to fix some typos and added comments to the code. ;-) – Wohlert

I get a null exception on the DataItem unfortunately. So I am looking into figuring that part out – Chaney

You were right the whole time with using FindControl. There was another thing in my setup that was wrong. My 2nd datasource was defined in the same spot as the first, so it was only set once. I fixed it, and then I had to find the source your way and it worked. Now I just have to deal with speed. – Chaney

Thanks for the feedback. I'm glad you got it running and Good luck with the speed enhancement. ;-) – Wohlert

Worked like a charm! One thing though, I had to set the C# function to protected, since leaving it off gave me an "inaccesible due to privacy level" error. – Pasteup

Answer 2

I did this by using a HiddenField to store a value to use as a parameter later. Gets the job done.

<asp:SqlDataSource ... />
<asp:Repeater ...>
    <ItemTemplate>

        <asp:HiddenField ID="txtOuterID" runat="server" Value='<%# Eval("ID") %>' Visible="false" />

        <asp:SqlDataSource ...>
            <SelectParameters>
                <asp:ControlParameter Name="OuterID" Type="Int32" ControlID="txtOuterID" PropertyName="Value" />
            </SelectParameters>
        </asp:SqlDataSource>

        <asp:Repeater ...>

    </ItemTemplate>
</asp:Repeater>

Kilpatrick

Answer 3

You may want to look into a technique that will reduce the amount of sproc calls. It is possible to return multiple resultsets from the same stored procedure. The .net dataset has the ability to define relationships between multiple data tables, making it easy to take a datarow in one table and get all of the child nodes in another data table. Ex:

exampleData.Relations.Add(New DataRelation("FOO_RELATION", exampleData.Tables["TABLE_A"].Columns["ID"], exampleData.Tables["TABLE_B"].Columns["PARENT_ID"]));

Then from any datarow in TABLE_A you can access all of the children like this:

DataRow[] childRows = row.GetChildRows("FOO_RELATION");

This is more efficient and IMHO is easier as well. This way you don't need to bake sproc calls into the event handlers for your repeater.

Stephens

Answer 4

Cerebrus's answer works except that there is one catch. You will get null exceptions unless you follow the rules of this question I think:

How to access the item being data bound during ItemDataBound?

Essentially, I have to check to make sure that the item in question is an item or alternating item, otherwise headers and footers will cause trouble.

Edit: I also got an error trying to use FindControl to get the DataSource. The FindControl returned a null so when I went to access the data source, I got a second null exception. So I ended up simply accessing the object directly. The datasource is declared in the designer file anyway. So with this, I finally got the nested repeaters to work.

// Find the Inner DataSource control in this Row.

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == 
    ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.EditItem)
{
    CellsDataSource.SelectParameters["testRunID"].DefaultValue = 
        DataBinder.Eval(e.Item.DataItem, "TestRunID").ToString();
}

Chaney

Excellent point, Tony. Thank you for correcting me. I should've thought of that! I'm not sure why FindControl does not work for you. I'll have to create a sample with nested repeaters and check it out. P.S: I feel bad that the code didn't work as expected. Here's another vote. :P – Wohlert

Thanks for leading me down the right path though. That helped. Now I just have to give the table the right style element so it looks better. – Chaney

Answer 5

I think that the call to FindControl returns a null because you have to first call FindControl on the nested repeater then call FindControl on the returned repeater.

Repeater rpt  = (Repeater)e.Item.FindControl("rptNested");
SqlDataSource s = (SqlDataSource)rpt.FindControl("InnerDataSource");

Alienor