Bind data repeater inside data repeater in ASP.NET
I'm learning .NET at the moment, so forgive me if this is a silly question.
I have two MSSQL tables, one called 'Comment' and the other called 'CommentAdditionalAuthor'. The Comment table holds data from a comment form, such as comment title, date, main author name etc. The CommentAdditionalAuthor table contains information about other authors who contributed to the comment. So there is a one-to-many relationship there - each comment can have one or more additional authors.
I have a repeater control which binds data from the Comment table, which works nicely. Now what I want to do though is output the author details alongside the comment. So effectively a repeater within a repeater.
I've tried to clean up this code as much as possible but sorry that it's still pretty lengthy as I'd like to keep it relevant to what I'm doing so I can hopefully understand the solution. Having found other examples of this, I'm struggling to implement them as they bind to the data in different ways, which I just can't make work in my application.
The code in the aspx file is like this:
<div id="comments">
<asp:Repeater ID="rptComments" runat="server">
<ItemTemplate>
<div class="comment-data">
<h3 class="item">Submitted to <%# GetPageDetails(Eval("nodeid")) %> article on <%# Eval("created") %></strong></h3>
<p class="item"><strong>Name</strong> <%# Eval("firstname") %> <%# Eval("surname") %></p>
<p class="item"><strong>Occupation</strong> <%# Eval("occupation") %></p>
<p class="item"><strong>Affiliation</strong> <%# Eval("affiliation") %></p>
<p class="item"><strong>Email</strong> <a href='mailto:<%# Eval("email") %>'><%# Eval("email") %></a> <em>Publish email: <%# Eval("publishemail") %></em></p>
<p class="item"><strong>Competing interests?</strong> <%# Eval("competingintereststext") %> </p>
<p class="item"><strong>eLetter title</strong> <%# Eval("title") %></p>
<p><%# Eval("comment").ToString().Replace("\n", "<br/>")%></p>
<!-- This is what I want to do, but can't get it to bind: -->
<div class="additional-authors">
<h3>Additional authors</h3>
<asp:Repeater id="rptAdditionalAuthors" runat="server">
<ItemTemplate>
<%# Eval("firstnameother")%><br>
<%# Eval("surnameother")%><br>
<%# Eval("occupationother")%><br>
<%# Eval("affiliationother")%><br>
<%# Eval("emailother")%><br>
</ItemTemplate>
</asp:Repeater>
</div>
</div>
</ItemTemplate>
</asp:Repeater>
And here's what the code-behind is doing:
private void BindData()
{
var rr = _sqlHelper.ExecuteReader(string.Format("select * from comment {0} order by created desc", Filter));
var commentDt = new DataTable("Comments");
commentDt.Columns.Add("id", Type.GetType("System.Int32"));
commentDt.Columns.Add("nodeid", Type.GetType("System.Int32"));
commentDt.Columns.Add("firstname", Type.GetType("System.String"));
commentDt.Columns.Add("surname", Type.GetType("System.String"));
commentDt.Columns.Add("occupation", Type.GetType("System.String"));
commentDt.Columns.Add("affiliation", Type.GetType("System.String"));
commentDt.Columns.Add("title", Type.GetType("System.String"));
commentDt.Columns.Add("email", Type.GetType("System.String"));
commentDt.Columns.Add("publishemail", Type.GetType("System.Boolean"));
commentDt.Columns.Add("competinginterests", Type.GetType("System.Boolean"));
commentDt.Columns.Add("competingintereststext", Type.GetType("System.String"));
commentDt.Columns.Add("comment", Type.GetType("System.String"));
commentDt.Columns.Add("statusid", Type.GetType("System.Int32"));
commentDt.Columns.Add("spam", Type.GetType("System.Boolean"));
commentDt.Columns.Add("ham", Type.GetType("System.Boolean"));
commentDt.Columns.Add("created",Type.GetType("System.DateTime"));
while (rr.Read())
{
var dr = commentDt.NewRow();
dr["id"] = rr.GetInt("id");
dr["nodeid"] = rr.GetInt("nodeid");
dr["firstname"] = rr.GetString("firstname");
dr["surname"] = rr.GetString("surname");
dr["occupation"] = rr.GetString("occupation");
dr["affiliation"] = rr.GetString("affiliation");
dr["title"] = rr.GetString("title");
dr["email"] = rr.GetString("email");
dr["publishemail"] = rr.IsNull("publishemail") == true ? false : rr.GetBoolean("publishemail");
dr["competinginterests"] = rr.IsNull("competinginterests") == true ? false : rr.GetBoolean("competinginterests");
dr["competingintereststext"] = rr.GetString("competingintereststext");
dr["comment"] = rr.GetString("comment");
dr["statusid"] = rr.GetInt("statusid");
dr["spam"] = rr.IsNull("spam") == true ? false : rr.GetBoolean("spam");
dr["ham"] = rr.IsNull("ham") == true ? false : rr.GetBoolean("ham");
dr["created"] = rr.GetDateTime("created");
commentDt.Rows.Add(dr);
/* My failing attempt at the second bind:
var rrAuthor = _sqlHelper.ExecuteReader(string.Format("select * from CommentOtherAuthor WHERE commentid = 81"));
var otherAuthorDt = new DataTable("OtherAuthors");
otherAuthorDt.Columns.Add("firstname", Type.GetType("System.String"));
otherAuthorDt.Columns.Add("surname", Type.GetType("System.String"));
otherAuthorDt.Columns.Add("occupation", Type.GetType("System.String"));
otherAuthorDt.Columns.Add("affiliation", Type.GetType("System.String"));
otherAuthorDt.Columns.Add("email", Type.GetType("System.String"));
while (rrAuthor.Read())
{
var drAuthor = otherAuthorDt.NewRow();
drAuthor["firstnameother"] = rr.GetString("firstname");
drAuthor["surnameother"] = rr.GetString("surname");
drAuthor["occupationother"] = rr.GetString("occupation");
drAuthor["affiliationother"] = rr.GetString("affiliation");
drAuthor["emailother"] = rr.GetString("email");
otherAuthorDt.Rows.Add(drAuthor);
}
rptAdditionalAuthors.DataBind();
*/
}
var pgitems = new PagedDataSource
{
DataSource = commentDt.DefaultView,
AllowPaging = true,
PageSize = 25,
CurrentPageIndex = CurrentPage
};
rptComments.DataSource = pgitems;
rptComments.DataBind();
if (pgitems.PageCount > 1)
{
var pages = new ArrayList();
for (var i = 0; i < pgitems.PageCount; i++)
pages.Add((i + 1).ToString());
rptPages.DataSource = pages;
rptPages.DataBind();
rptPages.Visible = true;
}
else
{
rptPages.Visible = false;
}
}
The code-behind stuff doesn't work because of the second repeater I've set up, but the first repeater does work if I remove all that stuff. I thought it best to keep it in there so you can see my 'logic' (probably not very logical ;) ). Also worth noting that in the second data set I've hard-coded it to get the additional author details with id 81 - that was just to get the repeater working - obviously I need to substitute this with the id value from the Comment table, to get the authors for the current comment in the loop.
If anyone can help me get this second repeater working I'd be most grateful. It's over the edge of my learning curve at the moment!
Thanks folks!
Answer
There are several articles available online on Nested repeaters, including this one.
In essence, you need to first create your DataSet and define the relationships between the DataTables in code-behind, and then bind the child repeater to the child rows. The article linked to above has a full working example that's prety easy to modify.
if that article doesn't suit your needs, try one of these.