Display Parent/Child Data with Checkboxlist

[jmhmaine]

Overview:
I have a page that displays 24 email newsletters that a user can subscribe
to by clicking checking the a check for each one. These fall into different
categories, currently 5, but this can change. The data is stored in two
tables, one called Family which contains the categories, the other is
Projects, which contains the individual newsletters items. Family and
Projects have a one-to-many relationship.

Goal:
What I want to display is:
<Category Name 1>
[] Newsletter Topic 1
[] Newsletter Topic 2
[] Newsletter Topic X
<Category Name 2>
[] Newsletter Topic 1
[] Newsletter Topic 2
[] Newsletter Topic X
<Category Name X>
[] Newsletter Topic 1
[] Newsletter Topic 2
[] Newsletter Topic X

[Submit]

Each newsletter topic has a checkbox before it, so the user can select which
topics interest them. This form needs to display the current selections, so
the user can view the topics already selected.

Currently I have the following in the code behind page in VB.NET, which only
lists all 24 topics, without the Category name:
Dim da As SqlDataReader
Dim sqlStr As String

sqlStr = "SELECT ProjectID, EmailTopic FROM vEmailTopics"
da = SqlHelper.ExecuteReader(Global.ConnectionString,
CommandType.Text, sqlStr)

cblEmailList.DataSource = da
cblEmailList.DataValueField = "ProjectID"
cblEmailList.DataTextField = "EmailTopic"
cblEmailList.DataBind()

How do I achive the desired goal? Thanks.

 

[jmhmaine]

I've updated my code since I posted this question. To recap:

Currently my code displays:
[] Newsletter Topic 1
[] Newsletter Topic 2
[] Newsletter Topic 3
[] Newsletter Topic 4
...
[Submit]

Where [] is a checkbox created by a CheckBoxList control. Because I have 24
items,
I want to breakup the display so that it looks like this:
<Category Name 1>
[] Newsletter Topic 8
[] Newsletter Topic 2
[] Newsletter Topic 7
<Category Name 2>
[] Newsletter Topic 1
[] Newsletter Topic 3
[] Newsletter Topic 4
<Category Name 3>
[] Newsletter Topic 5
[] Newsletter Topic 6
[] Newsletter Topic 8

[Submit]

Current working code:

'Used to populate CheckBoxList Control (cblEmailList)
PrivateSub bindEmailData()
Dim da As SqlDataReader
Dim sqlStr As String
Dim sqlParam As New SqlParameter

sqlStr = "SELECT ProjectID, EmailTopic FROM vEmailTopics"
da = SqlHelper.ExecuteReader(Global.ConnectionString, CommandType.Text,
sqlStr)

cblEmailList.DataSource = da
cblEmailList.DataValueField = "ProjectID"
cblEmailList.DataTextField = "EmailTopic"
cblEmailList.DataBind()

da.Close()

sqlParam.ParameterName = "@WCID"
sqlParam.SqlDbType = SqlDbType.Int
sqlParam.Value = CType(wcid.Value, Integer) 'Uses hidden value

da = SqlHelper.ExecuteReader(Global.ConnectionString,
CommandType.StoredProcedure, "spEmailTopicsByEmail", sqlParam)

Do While da.Read()
Dim currentCheckBox As ListItem =
cblEmailList.Items.FindByValue(da("ProjectID").ToString())
currentCheckBox.Selected = True
Loop

'Clean up Dataset Object
da.Close()
da = Nothing
End Sub

'This function is used to insert and delete selections
Private Sub InsertEmailSignup()
Dim i As Integer 'Used in For Loop
Dim cntSelected As Integer = 0

'Data Access
Const PositionWCID As Short = 0
Const PositionProjectID As Short = 1
Const PositionDeleteItem As Short = 2
Const NumberOfInputParamsForArray As Short = 2 'Note: Zero based so
substract 1 from total

Dim parms() As SqlParameter = New
SqlParameter(NumberOfInputParamsForArray) {}

' @WCID Input Parameter
parms(PositionWCID) = New SqlParameter("@WCID", SqlDbType.Int)
parms(PositionWCID).Direction = ParameterDirection.Input
parms(PositionWCID).Value = CType(wcid.Value, Integer) 'Uses hidden value

' @ProjectID Input Parameter
parms(PositionProjectID) = New SqlParameter("@ProjectID", SqlDbType.Int)
parms(PositionProjectID).Direction = ParameterDirection.Input

' @DeleteItem Input Parameter
parms(PositionDeleteItem) = New SqlParameter("@DeleteItem",
SqlDbType.Bit)
parms(PositionDeleteItem).Direction = ParameterDirection.Input

'Iterate through the Items collection of the CheckBoxList
For i = 0 To cblEmailList.Items.Count - 1
If cblEmailList.Items(i).Selected Then
parms(PositionProjectID).Value = cblEmailList.Items(i).Value
parms(PositionDeleteItem).Value = 0
SqlHelper.ExecuteNonQuery(Global.ConnectionString,
CommandType.StoredProcedure, "spEmailSignupInsert", parms)

cntSelected += 1
Else
parms(PositionProjectID).Value = cblEmailList.Items(i).Value
parms(PositionDeleteItem).Value = 1 'Value to delete item
SqlHelper.ExecuteNonQuery(Global.ConnectionString,
CommandType.StoredProcedure, "spEmailSignupInsert", parms)
End If
Next
End Sub

 

[Steven Cheng[MSFT]]

Hi Jmh,

Welcome to ASPNET newsgroup. From your description, you have two datatables
one contains Emails(sub table) info and anther is the Category table(sub
table). Now you're wanting to bind all the mails info to a checkbox list
and also need to divided those checkboxes into different groups according
to their category. However, you found that directly binding to the
checkboxlist won't work as expected, yes?

As for this problem, I don't think directly binding to CheckBoxList control
is a good idea , becaue the CheckBoxList control only support one layer
databinding, if we provide a list of data to it ,it will loop all the
records and display the datas no matter those datas are divided into groups
according to some certain fields.

I think we need to use hierarchy databinding to achieve it. For example, we
can use Repeater or DataList control to bind with the Category table and
then put checkboxlist in the repeater / datalist 's ItemTemplate so that
when binding each category in Repeater/DataList, we also bind the sub Mail
iTems of that category. To make it clear, I've built a simple demo page,
you can have a look to see whether it helps:

In addition, I'm thinking that we can also do it using the TreeView IE
Webcontrol since the TreeNodes in TreeView control support display a
checkbox in each node, and we can make all the mail nodes as childnodes of
the Category Nodes. This is also an alertivate option.

==============aspx page==============
<HTML>
<HEAD>
<title>listcheckboxes</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<table width="80%">
<tr>
<td>
<asp:Repeater id="rptCategory" runat="server">
<HeaderTemplate>
<hr size="1" width="100%" />
</HeaderTemplate>
<ItemTemplate>
<br />
&lt;<%# ((System.Data.DataRowView)Container.DataItem)[0] %>&gt;
&nbsp;&nbsp;&nbsp;&nbsp;<asp:CheckBoxList id="cblMails"
runat="server"
DataSource='<%#
((System.Data.DataRowView)Container.DataItem).CreateChildView("category_mail
") %>'
DataTextField="Name" DataValueField="Name">
</asp:CheckBoxList>
</ItemTemplate>
<FooterTemplate>
<hr size="1" width="100%" />
</FooterTemplate>
</asp:Repeater>

</td>
</tr>
<tr>
<td></td>
</tr>
</table>
</form>
</body>
</HTML>

==========code behind================
public class listcheckboxes : System.Web.UI.Page
{
protected System.Web.UI.WebControls.CheckBoxList cblMails;
protected System.Web.UI.WebControls.Repeater rptCategory;

private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
DataSet ds = GetDataSource();
rptCategory.DataSource = ds.Tables["Categorys"];
rptCategory.DataBind();
}
}


private DataSet GetDataSource()
{
DataSet ds = new DataSet("NewsMails");

DataTable dtCategory = new DataTable("Categorys");
DataTable dtMails = new DataTable("Mails");

dtCategory.Columns.Add("Name");
dtCategory.Columns.Add("Desc");

dtCategory.PrimaryKey = new DataColumn[]{dtCategory.Columns[0]};


dtMails.Columns.Add("MailID", typeof(long));
dtMails.Columns.Add("Name");
dtMails.Columns.Add("Desc");
dtMails.Columns.Add("Category");
dtMails.PrimaryKey = new DataColumn[]{dtMails.Columns[0]};

ds.Tables.Add(dtCategory);
ds.Tables.Add(dtMails);

ds.Relations.Add("category_mail", dtCategory.PrimaryKey, new
DataColumn[]{dtMails.Columns["Category"]});

int i=0,j=0;
DataRow dr = null, dr1 = null;

for(i = 0;i<5;i++)
{
dr = dtCategory.NewRow();
dr[0] = "Category_" + i;
dr[1] = "Category_" + i + " 's description.";

dtCategory.Rows.Add(dr);

for(j=0;j<5;j++)
{
dr1 = dtMails.NewRow();
dr1[0] = i * 10 + j;
dr1[1] = "Mail_" + dr1[0];
dr1[2] = "Description of" + dr1[1];
dr1[3] = dr[0];

dtMails.Rows.Add(dr1);
}
}


return ds;
}


#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
===================================


Hope helps. Good Luck!


Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[jmhmaine]

Steven:

Thanks for the post, it looks like a good start. I have some questions:
1. Your code assumes that there are always 5 categories. This can change, so
we shouldn't hard code the number.
2. If you look at my code in the post, I select the checkboxes based on
prior selections. How should I update this code based on your code?
3. In my code I also have a sub that inserts and delete the rows in the
database based on selections. How do I update this code based on your code to
find all the checkboxes?
4. Do you have this example in VB.NET?

Thanks.

Josh.

Hi Jmh,

Welcome to ASPNET newsgroup. From your description, you have two datatables
one contains Emails(sub table) info and anther is the Category table(sub
table). Now you're wanting to bind all the mails info to a checkbox list
and also need to divided those checkboxes into different groups according
to their category. However, you found that directly binding to the
checkboxlist won't work as expected, yes?

As for this problem, I don't think directly binding to CheckBoxList control
is a good idea , becaue the CheckBoxList control only support one layer
databinding, if we provide a list of data to it ,it will loop all the
records and display the datas no matter those datas are divided into groups
according to some certain fields.

I think we need to use hierarchy databinding to achieve it. For example, we
can use Repeater or DataList control to bind with the Category table and
then put checkboxlist in the repeater / datalist 's ItemTemplate so that
when binding each category in Repeater/DataList, we also bind the sub Mail
iTems of that category. To make it clear, I've built a simple demo page,
you can have a look to see whether it helps:

In addition, I'm thinking that we can also do it using the TreeView IE
Webcontrol since the TreeNodes in TreeView control support display a
checkbox in each node, and we can make all the mail nodes as childnodes of
the Category Nodes. This is also an alertivate option.

==============aspx page==============
<HTML>
<HEAD>
<title>listcheckboxes</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<table width="80%">
<tr>
<td>
<asp:Repeater id="rptCategory" runat="server">
<HeaderTemplate>
<hr size="1" width="100%" />
</HeaderTemplate>
<ItemTemplate>
<br />
<<%# ((System.Data.DataRowView)Container.DataItem)[0] %>>
<asp:CheckBoxList id="cblMails"
runat="server"
DataSource='<%#
((System.Data.DataRowView)Container.DataItem).CreateChildView("category_mail
") %>'
DataTextField="Name" DataValueField="Name">
</asp:CheckBoxList>
</ItemTemplate>
<FooterTemplate>
<hr size="1" width="100%" />
</FooterTemplate>
</asp:Repeater>

</td>
</tr>
<tr>
<td></td>
</tr>
</table>
</form>
</body>
</HTML>

==========code behind================
public class listcheckboxes : System.Web.UI.Page
{
protected System.Web.UI.WebControls.CheckBoxList cblMails;
protected System.Web.UI.WebControls.Repeater rptCategory;

private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
DataSet ds = GetDataSource();
rptCategory.DataSource = ds.Tables["Categorys"];
rptCategory.DataBind();
}
}


private DataSet GetDataSource()
{
DataSet ds = new DataSet("NewsMails");

DataTable dtCategory = new DataTable("Categorys");
DataTable dtMails = new DataTable("Mails");

dtCategory.Columns.Add("Name");
dtCategory.Columns.Add("Desc");

dtCategory.PrimaryKey = new DataColumn[]{dtCategory.Columns[0]};


dtMails.Columns.Add("MailID", typeof(long));
dtMails.Columns.Add("Name");
dtMails.Columns.Add("Desc");
dtMails.Columns.Add("Category");
dtMails.PrimaryKey = new DataColumn[]{dtMails.Columns[0]};

ds.Tables.Add(dtCategory);
ds.Tables.Add(dtMails);

ds.Relations.Add("category_mail", dtCategory.PrimaryKey, new
DataColumn[]{dtMails.Columns["Category"]});

int i=0,j=0;
DataRow dr = null, dr1 = null;

for(i = 0;i<5;i++)
{
dr = dtCategory.NewRow();
dr[0] = "Category_" + i;
dr[1] = "Category_" + i + " 's description.";

dtCategory.Rows.Add(dr);

for(j=0;j<5;j++)
{
dr1 = dtMails.NewRow();
dr1[0] = i * 10 + j;
dr1[1] = "Mail_" + dr1[0];
dr1[2] = "Description of" + dr1[1];
dr1[3] = dr[0];

dtMails.Rows.Add(dr1);
}
}


return ds;
}


#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
===================================


Hope helps. Good Luck!


Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Josh,

Thanks for your followup.
I'm not sure what is the hard code you mean since the Categorys in my code
sample is also dynamic generated. As you can found , there was a function
called "GetDataSource" in my page code , I use this function to simulate
the DataBase dataaccessing. I generate two dataTables category and email
and fill them in a DAtaSet. Also, add an relation between them so that I
can make use of the relation later in the databinding.

I use a Repeater control to bind the Category DataTable's records, and
during the binding of each Category, I also retreves the emails associated
with that category and bind them to a checkboxlist. All this binding is
dynamic and the items of the Category or Email checkboxlists can change
according to the database's actual data.

In addition, we can also access all the checkboxlist's items when post back
the page. I've made a futher example which displaying all hte selected
items when the page is submited through a push button and I've turned it
into a VB.NET version.

Hope helps.

==============aspx================
<table width="80%">
<tr>
<td>
<asp:Repeater id="rptCategory" runat="server">
<HeaderTemplate>
<hr size="1" width="100%" />
</HeaderTemplate>
<ItemTemplate>
<br />
&lt;<%# Container.DataItem(0) %>&gt;
&nbsp;&nbsp;&nbsp;&nbsp;
<asp:CheckBoxList id="cblMails" runat="server" DataSource='<%#
Container.DataItem.CreateChildView("category_mail") %>'
DataTextField="Name" DataValueField="Name">
</asp:CheckBoxList>
</ItemTemplate>
<FooterTemplate>
<hr size="1" width="100%" />
</FooterTemplate>
</asp:Repeater>
</td>
</tr>
<tr>
<td>
<asp:Button id="btnSubmit" runat="server"
Text="Submit"></asp:Button></td>
</tr>
</table>

==========code behind=============
Public Class listcheckboxes
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

End Sub
Protected WithEvents rptCategory As System.Web.UI.WebControls.Repeater
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button

'NOTE: The following placeholder declaration is required by the Web
Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim ds As DataSet = GetDataSource()
rptCategory.DataSource = ds.Tables("Categorys")
rptCategory.DataBind()
End If

End Sub

Private Function GetDataSource() As DataSet
Dim ds As DataSet = New DataSet("NewsMails")

Dim dtCategory As DataTable = New DataTable("Categorys")
Dim dtMails As DataTable = New DataTable("Mails")

dtCategory.Columns.Add("Name")
dtCategory.Columns.Add("Desc")

dtCategory.PrimaryKey = New DataColumn() {dtCategory.Columns(0)}


dtMails.Columns.Add("MailID", GetType(Long))
dtMails.Columns.Add("Name")
dtMails.Columns.Add("Desc")
dtMails.Columns.Add("Category")
dtMails.PrimaryKey = New DataColumn() {dtMails.Columns(0)}

ds.Tables.Add(dtCategory)
ds.Tables.Add(dtMails)

ds.Relations.Add("category_mail", dtCategory.PrimaryKey, New
DataColumn() {dtMails.Columns("Category")})

Dim i, j As Integer

Dim dr, dr1 As DataRow

For i = 0 To 3
dr = dtCategory.NewRow()
dr(0) = "Category_" & i
dr(1) = "Category_" & i & " 's description."

dtCategory.Rows.Add(dr)

For j = 0 To 2

dr1 = dtMails.NewRow()
dr1(0) = i * 10 & j
dr1(1) = "Mail_" & dr1(0)
dr1(2) = "Description of" & dr1(1)
dr1(3) = dr(0)

dtMails.Rows.Add(dr1)
Next

Next


Return ds
End Function

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click

Dim item As RepeaterItem
Dim cbl As CheckBoxList


For Each item In rptCategory.Items

cbl = item.FindControl("cblMails")

Dim li As ListItem

For Each li In cbl.Items

If (li.Selected = True) Then
Response.Write("<br>" & li.Value)
End If
Next


Next

End Sub
End Class

===========================


Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)