Pre-populate checkboxlist from values in database

Question

I have some problem in prepopulating checkboxlist by fetching values from database for same users multiple checkboxes need to be checked. This is an update profile page I want that when this page load multiple checkboxes come checked according to database which user has done at the time of registration. So, how to do it please i tried a lot of times but only last value get checked other all not get checked. Single user can have multiple hobbies. So checkboxlist is for hobbies and when update page loads multiple hobbies come checked already and then he can update and add more hobbies also. Update part I have done I just want that checkboxlist comes prepopulated with all values user have given previously. Thanks in Advance.

This is my update.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Update.aspx.cs" Inherits="Update" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table class="style1">
        <tr>
            <td class="style3">
                <asp:Label ID="Label2" runat="server" Text="Login Name"></asp:Label>
            </td>
            <td colspan="2">
                <asp:TextBox ID="txtlogin" runat="server" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                <asp:Label ID="Label4" runat="server" Text="EmailId"></asp:Label>
            </td>
            <td colspan="2">
                <asp:TextBox ID="txtemail" runat="server" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" 
                    ControlToValidate="txtemail" Display="Dynamic" ErrorMessage="Invalid" 
                    ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
            </td>
        </tr>
        <tr>
            <td class="style3">
                <asp:Label ID="Label5" runat="server" Text="Address"></asp:Label>
            </td>
            <td colspan="2">
                <asp:TextBox ID="txtadd" runat="server" TextMode="MultiLine" Width="142px" 
                    ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style6">
                <asp:Label ID="Label6" runat="server" Text="City"></asp:Label>
            </td>
            <td colspan="2" class="style7">
                <asp:DropDownList ID="ddlcity" runat="server" Height="21px" Width="142px" 
                    Enabled="False">
                </asp:DropDownList>
            </td>
            <td class="style7">
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                <asp:Label ID="Label7" runat="server" Text="Pincode"></asp:Label>
            </td>
            <td colspan="2">
                <asp:TextBox ID="txtpin" runat="server" ReadOnly="True" ></asp:TextBox>
            </td>
            <td>
                <asp:RegularExpressionValidator ID="regexpin" runat="server" 
                    ControlToValidate="txtpin" ErrorMessage="Numbers only(six digits)" 
                    ValidationExpression="[0-9]{6}"></asp:RegularExpressionValidator>
            </td>
        </tr>
        <tr>
            <td class="style3">
                <asp:Label ID="Label8" runat="server" Text="PaymentMode"></asp:Label>
            </td>
            <td colspan="2">
                <asp:RadioButtonList ID="rbtnlstpay" runat="server" Height="28px" Width="225px" 
                    Enabled="False">
                </asp:RadioButtonList>
            </td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                <asp:Label ID="Label9" runat="server" Text="Hobbies"></asp:Label>
            </td>
            <td colspan="2">
                <asp:CheckBoxList ID="cblsthobbies" runat="server" Width="128px" 
                    Enabled="False">
                </asp:CheckBoxList>
            </td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td class="style5" colspan="2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td>
                <asp:Button ID="btnedit" runat="server" Text="Edit" 
                    onclick="btnedit_Click" />
            </td>
            <td>
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style4">
                &nbsp;</td>
            <td class="style2" colspan="2">
                <asp:Label ID="lblresult" runat="server"></asp:Label>
            </td>
            <td class="style2">
            </td>
        </tr>
        <tr>
            <td class="style4">
                &nbsp;</td>
            <td class="style2" colspan="2">
                &nbsp;</td>
            <td class="style2">
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td colspan="2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td colspan="2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td colspan="2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td colspan="2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td colspan="2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

This is my update.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Update : System.Web.UI.Page
{
    DataSet ds = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = @"Data Source=.;Initial Catalog=UserRecords;Integrated Security=True";

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select * from CityMaster;select * from HobbyMaster;select * from PaymentModeMaster;select * from Users where LoginId = @Loginid;select * from UsersHobbies where LoginId = @Loginid";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Loginid",Session["loginid"].ToString());
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                ddlcity.Items.Add(new ListItem(dr["CityName"].ToString(), dr["CityId"].ToString()));
            }

            dr.NextResult();

            while (dr.Read())
            {
                cblsthobbies.Items.Add(new ListItem(dr["Hobbyname"].ToString(), dr["HobbyId"].ToString()));
            }

            dr.NextResult();

            while (dr.Read())
            {
                rbtnlstpay.Items.Add(new ListItem(dr["PaymentModename"].ToString(), dr["PaymentModeId"].ToString()));
            }

            dr.NextResult();

            while (dr.Read())
            {
                txtlogin.Text = dr["LoginId"].ToString();
                txtemail.Text = dr["EmailId"].ToString();
                txtadd.Text = dr["Address"].ToString();
                txtpin.Text = dr["Pincode"].ToString();
                ddlcity.SelectedValue = dr["CityId"].ToString();
                rbtnlstpay.SelectedValue = dr["PaymentModeId"].ToString();
            }

            dr.NextResult();

            while (dr.Read())
            {
                for(int i = 0; i < dr.FieldCount; i++)
                {
                    cblsthobbies.SelectedValue = dr["HobbyId"].ToString();
                    //cblsthobbies.SelectedValue = dr.GetValue(1).ToString();
                }
            }
            cn.Close();
        }
    }

    protected void btnedit_Click(object sender, EventArgs e)
    {
        txtemail.ReadOnly = false;
        txtadd.ReadOnly = false;
        ddlcity.Enabled = true;
        txtpin.ReadOnly = false;
        rbtnlstpay.Enabled = true;
        cblsthobbies.Enabled = true;
        btnedit.Text = "Update";
    }
}

Previous StackOverflow question [ASP.NET CheckBoxList Data-Binding](http://stackoverflow.com/questions/879434/asp-net-checkboxlist-databinding-question) — Karl Anderson, Jun 28 '13 at 18:34

score 2 · Accepted Answer · answered Jun 28 '13 at 18:27

It looks like this is where you're trying to set the checked state:

for(int i = 0; i < dr.FieldCount; i++)
{
    cblsthobbies.SelectedValue = dr["HobbyId"].ToString();
    //cblsthobbies.SelectedValue = dr.GetValue(1).ToString();
}

However, this isn't setting the checked state for individual items in the CheckBoxList. This is just setting the overall SelectedValue for the list itself. And since there's only one such value, each iteration through the loop overwrites the previous one. Thus, only the last one remains.

In order to set the checked state, you'll have to find the individual check box for each record in your last record set. Perhaps something like this:

for(int i = 0; i < dr.FieldCount; i++)
{
    cblsthobbies.Items.FindByValue(dr["HobbyId"].ToString()).Selected = true;
}

score 0 · Answer 2 · answered Jun 28 '13 at 18:24

0

Loop through the cblsthobbies.Items collection and set the Selected property value of each item like this

foreach (var i in cblsthobbies.Items) 
{
     i.Selected = i.SelectedValue == dr["HobbyId"].ToString();
}

answered Jun 28 '13 at 18:24

Ilkka

306
1
7

score 0 · Answer 3 · answered Aug 07 '15 at 18:57

Rather than have two nested loops -- one for your all possible selections and then a nested one to look for each one in your previously chosen selections -- I like to read through the previously chosen selections first and store them in a Hashtable, then just iterate through all the items already in the CheckBoxList and select one if it is found in the Hashtable. This has two advantages:

Instead of looping O(m*n) times, where m is the number of possible items and n is the number of previously selected ones, it only needs to loop O(m + n) times (or I suppose O(2m + n) counting the building of the CheckBoxList)
The code seems a bit more clear without nested loops

So first I build the check box list independently in the .aspx

            <asp:SqlDataSource runat="server" ID="dsHobbies"
                SelectCommand="SELECT iHobbyID, sHobbyName FROM tblHobby">
            </asp:SqlDataSource>
            <asp:CheckBoxList runat="server" ID="cblHobbies" DataSourceID="dsHobbies" DataValueField="iHobbyID" DataTextField="sHobbyName "></asp:CheckBoxList>

Then the code to read the previously saved selections and populate the CheckBoxList looks like this:

        // read previously chosen items from database
        string sql = "SELECT * FROM tblSelectedHobbies WHERE iPersonID=@pPersonID";
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = myConnStr;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("pPersonID", id); // id from query string, or wherever
        SqlDataReader reader = cmd.ExecuteReader();

        // iterate through saved entries and add to Hashtable
        Hashtable savedEntries = new Hashtable();
        while (reader.Read())
        {
            string hobbyID= reader["iHobbyID"].ToString();
            savedEntries[hobbyID] = true;
        }
        conn.Close();

        // check the corresponding boxes
        dsHobbies.DataBind();
        cblHobbies.DataBind();
        foreach (ListItem li in cblHobbies.Items)
        {
            if (savedEntries.ContainsKey(li.Value))
            {
                li.Selected = true;
            }
        }

Pre-populate checkboxlist from values in database

3 Answers3