Display Parent/Child Data with Checkboxlist

Discussion in 'ASP .Net Web Controls' started by jmhmaine, Mar 15, 2005.

  1. jmhmaine

    jmhmaine Guest

    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, Mar 15, 2005
    #1
    1. Advertising

  2. jmhmaine

    jmhmaine Guest

    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
     
    jmhmaine, Mar 15, 2005
    #2
    1. Advertising

  3. 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.)
     
    Steven Cheng[MSFT], Mar 16, 2005
    #3
  4. jmhmaine

    jmhmaine Guest

    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.

    "Steven Cheng[MSFT]" wrote:

    > 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.)
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
     
    jmhmaine, Mar 16, 2005
    #4
  5. 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.)
     
    Steven Cheng[MSFT], Mar 17, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. davout
    Replies:
    0
    Views:
    573
    davout
    Apr 18, 2004
  2. Jeff Rodriguez
    Replies:
    23
    Views:
    1,200
    David Schwartz
    Dec 9, 2003
  3. Joe D
    Replies:
    1
    Views:
    122
    N Clements
    Jul 1, 2003
  4. Noel Dolan
    Replies:
    0
    Views:
    274
    Noel Dolan
    Jul 18, 2004
  5. Bitswapper
    Replies:
    5
    Views:
    182
    Prasad, Ramit
    Aug 27, 2013
Loading...

Share This Page