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.

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.

Answer 2

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.

Answer 3

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:

http://stackoverflow.com/questions/344047/asp-net-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();
}

Answer 4

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");

Answer 5

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.