How do I bind my nested repeater to its parent based on current ID of parent?

Question

I have a 2 tables in SQL Server. One table has a list of all superheros and the other table is a list of abilities. Currently at runtime the child repeater gets all items in the table when it should only get the items pertaining to the parent table. On my aspx page I have a nested repeater like so:

<asp:Repeater id="rptHero" runat="server" DataSourceID="sdsHeros" OnItemDataBound="rptHero_ItemDataBound">
    <ItemTemplate>
        <table>
            <tr>
            <td> <%# Eval("HeroName")%> </td>                        
                <asp:Repeater id="rptAbility" runat="server" >                 
                  <ItemTemplate>                     
                    <tr>
                        <td> <%# Eval("AbilityName")%> </td>                     
                    </tr>
                  </ItemTemplate>
               </asp:Repeater>               
            </tr>
        </table>
        <p>&nbsp;</p>            
    </ItemTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="sdsHero" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer2 %>" 
    SelectCommand="SELECT [num], [HeroName] FROM [Super_Heros]"></asp:SqlDataSource>
<asp:SqlDataSource ID="sdsAbility" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer2 %>" 
    SelectCommand="SELECT [num], [AbilityName] FROM [Super_Ability]"></asp:SqlDataSource>

And in my code behind I have:

protected void rptHero_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        DataRowView row = (DataRowView)e.Item.DataItem;

        Repeater nestedRepeater = e.Item.FindControl("rptAbility") as Repeater;
        nestedRepeater.DataSource = sdsAbility;
        nestedRepeater.DataBind();
    }
}

Following Example 1 and Example 2 posted by our fine patrons here on Stack Overflow this should work however, I think the part I am skipping over where the [num] of the parent repeater gets compared to the [HeroID] of the child repeater as in a SQLJoin.

Answer 1

Here is what I ended up with

<asp:Repeater id="repMenu1" runat="server" OnItemDataBound="repMenu1_ItemDataBound">
    <ItemTemplate>
        <table id="gradient-style">
        <tr>
            <th> <%#DataBinder.Eval(Container.DataItem, "Hero")%> </th>
        </tr>
        <asp:Repeater id="repMenu2" runat="server">
            <ItemTemplate>
                <tr>
                <td> <a href="ScoreGraphs.aspx?id=<%#DataBinder.Eval(Container.DataItem, "num")%>"> <%#DataBinder.Eval(Container.DataItem, "Abilities")%></a> </td>
                </tr>       
            </ItemTemplate>
        </asp:Repeater>
        </table>     
    </ItemTemplate>
</asp:Repeater>

And in the code behind

protected void Page_Load(object sender, EventArgs e)
{
    GetSQLData();
}

//my custom function to get data and create relationship between 2 tables and 
//bind the data on page load so that when menu1 is databound, it will find the matching submenu       
//items in the database and bind them to the second (nested) repeater, 
//displaying the sub-menu items below the parent

protected void GetSQLData()
{
    using (SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer2"].ConnectionString))
    {
        conn1.Open();
        SqlDataAdapter cmd = new SqlDataAdapter(@"SELECT * FROM [Abilities]; SELECT * FROM [Heros]", conn1);
        DataSet ds = new DataSet();
        cmd.Fill(ds);
        ds.Relations.Add(new DataRelation("nestThem", ds.Tables[0].Columns["num"], ds.Tables[1].Columns["AbilityID"]));

        repMenu1.DataSource = ds;
        repMenu1.DataBind();
    }
}

//Binding the data
protected void repMenu1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    DataRowView dv = e.Item.DataItem as DataRowView;
    if (dv != null)
    {
        Repeater repSubMenu = e.Item.FindControl("repMenu2") as Repeater;
        if (repSubMenu != null)
        {
            repSubMenu.DataSource = dv.CreateChildView("nestThem");
            repSubMenu.DataBind();
        }
    }
}

I found this solution to be both simple and clean.