CODEWITHSUNDEEP
asp.netsqlsql-serverrepeater
Tony Peterson
Tony Peterson

Reputation: 21172

Nested Repeaters and SqlDataSource Parameters

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.

Upvotes: 6

Views: 11918

Answers (5)

Alexander Taylor
Alexander Taylor

Reputation: 17632

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>

Upvotes: 5

Phaedrus
Phaedrus

Reputation: 8421

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

Upvotes: 1

Jim Petkus
Jim Petkus

Reputation: 4508

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.

Upvotes: 3

Cerebrus
Cerebrus

Reputation: 25775

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.

Upvotes: 4

Tony Peterson
Tony Peterson

Reputation: 21172

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();
}

Upvotes: 3

Related Questions