title: Nesting repeaters from a MySQL database using OdbcDataReader in ASP.NET 2.0
body: <p>I asked this question a while ago, but had no response, since then I've been busy with other things, but came back to it today and still can't crack it.</p>
  
  <p>My original question: <a href="https://stackoverflow.com/questions/11868289/using-a-one-to-many-relationship-to-show-a-category-menu-using-asp-repeaters-vb">https://stackoverflow.com/questions/11868289/using-a-one-to-many-relationship-to-show-a-category-menu-using-asp-repeaters-vb</a></p>
  
  <p>Now, since then I have been trying to see what I am doing wrong when trying to following this article
  <a href="http://www.codeproject.com/Articles/6140/A-quick-guide-to-using-nested-repeaters-in-ASP-NET" rel="nofollow noreferrer">http://www.codeproject.com/Articles/6140/A-quick-guide-to-using-nested-repeaters-in-ASP-NET</a> and the various Microsfot tutorial</p>
  
  <p>I <em>think</em> I am missing a step or misunderstanding something with filling the dataset... and now having spent this morning going round in circles I am confused as hell.</p>
  
  <p>It's driving me up the wall.</p>
  
  <p>I have got this far: (I'm using ASP.NET 2.0 and a MySQL database by the way)</p>
  
  <pre><code>Sub getCategories() Handles Me.Load
  
      Dim DBConnection = ConfigurationManager.ConnectionStrings("dbConnNew").ConnectionString
      Dim connection As OdbcConnection = New OdbcConnection(DBConnection)
      Dim connect As New OdbcCommand("SELECT masterCat.name AS masterCat, masterCat.id as mcId, category.id as cId, category.name AS category, category.mapsTo as catMapsTo, subCat.mapsTo as subCatMapsTo, subCat.name AS subCat FROM masterCat INNER JOIN category ON masterCat.id = category.mapsTo INNER JOIN subCat ON category.id = subCat.mapsTo WHERE masterCat.id &lt;&gt; '9' ORDER BY masterCat.priority", connection)
  
      Dim DS As New DataSet()
      connection.Open()
      Dim read As OdbcDataReader = connect.ExecuteReader(CommandBehavior.CloseConnection)
      DS.Load(read, LoadOption.OverwriteChanges, "masterCat, category, subCat")
  
      'DS.Relations.Add("mCatid", DS.Tables("masterCat").Columns("mcId"), DS.Tables("category").Columns("cId")) &lt;&lt; This Line Breaks
  
      connection.Close()
  
      masterCat.DataSource = DS
      masterCat.DataBind()
  
  End Sub
  
  Private Sub CategoryRepeater_ItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles masterCat.ItemDataBound
      Dim item As RepeaterItem = e.Item
      If (item.ItemType = ListItemType.Item) OrElse (item.ItemType = ListItemType.AlternatingItem) Then
          Dim catRpt = DirectCast(item.FindControl("catRepeater"), Repeater)
          Dim drv As DataRowView = DirectCast(item.DataItem, DataRowView)
          catRpt.DataSource = drv.CreateChildView("mcId")
          catRpt.DataBind()
      End If
  End Sub
  </code></pre>
  
  <p>And markup:</p>
  
  <pre><code>         &lt;ul&gt;
              &lt;asp:Repeater ID="masterCat" runat="server"&gt;
                  &lt;ItemTemplate&gt;
                      &lt;li&gt;
                          &lt;%# Container.DataItem("masterCat")%&gt;
                          &lt;ul&gt;                            
                              &lt;asp:Repeater ID="catRepeater" runat="server"&gt;
                              &lt;ItemTemplate&gt;
                                  &lt;li&gt;
                                  &lt;%# Container.DataItem("category")%&gt;
                              &lt;/li&gt;
                              &lt;/ItemTemplate&gt;
                              &lt;/asp:Repeater&gt;
                          &lt;/ul&gt;
                      &lt;/li&gt;
                  &lt;/ItemTemplate&gt;
              &lt;/asp:Repeater&gt;
           &lt;/ul&gt;
  </code></pre>
  
  <p>To recap, I am trying to produce a nested repeater of master categories, categories and sub-categories. The code above is just going two steps, I figure the third must just be a repeat.</p>
  
  <p>My data is arranged in three tables, which look like this</p>
  
  <p><code>masterCat</code></p>
  
  <pre><code>id    |    name    | priority (for sorting)
  </code></pre>
  
  <p><code>category</code></p>
  
  <pre><code>id    |    name    |    mapsTo (this is the ID of the masterCat to which this relates)
  </code></pre>
  
  <p><code>subCat</code></p>
  
  <pre><code> id    |    name    | mapsTo (ID of the category to which this relates)
  </code></pre>
  
  <p>The SQL query above correctly returns the entire data with these columns:</p>
  
  <pre><code>masterCat   - name of the master category
  mcId        - ID of the master category
  cId         - ID of a category
  category    - name of a category
  catMapsTo   - the ID of the masterCategory to which category relates
  subCat       - subCategory name
  subCatMapsTo - the ID of the category to which the subCategory relates
  </code></pre>
  
  <p>What I need to end up with is something which looks like (just two levels deep in this example)</p>
  
  <p><ul>
          <li>Master Cat
              <ul>
                  <li>Category</li>
                  <li>Category</li>
                  <li>Category</li><br>
              </ul><br>
          </li>
          <li>Master Cat
              <ul>
                  <li>Category</li>
                  <li>Category</li>
                  <li>Category</li><br>
              </ul>
          </li>
          <li>Master Cat
              <ul>
                  <li>Category</li>
                  <li>Category</li>
                  <li>Category</li><br>
              </ul></p>
  
  <pre><code>    &lt;/li&gt;
  </code></pre>
  
  <p></ul></p>
  
  <p>Help would be much appreciated!</p>