Databind in checkbox list in ASP.NET

Checkbox list does not support multiple databind. To be a bit clearer, it does not support databind for multi select in checkbox list. It only stores one entry from the checkbox list.

In one of my recent project I had to implement checkbox list where user can tick multiple checkboxes and store them into the database table. In this project I was using SQL server express database and storing the data using the data in colon (:) delimited value so that I can push the data back into Oracle database to use this in Oracle APEX application. You can store your data whatever fashion you want to save in. In oracle APEX application it is an in build feature where you can bind data dynamically in checkbox list. Unfortunately you can not do this in ASP.NET.

Lets start the task, I have used SqlDataSource for database operation in this example and connection string pulled from web.config file. Here is my SqlDataSource structure –

<asp:SqlDataSource ID="dsImported" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
        SelectCommand="SELECT * FROM [rtt_sites] where sit_id=@sit_id" UpdateCommand="update rtt_sites set name=@name, sit_type=@sit_type, status=@status, primary_function=@primary_function, fee_type=@fee_type, access_type=@access_type, update_date=getdate() where sit_id=@sit_id">
        <UpdateParameters>
            <asp:Parameter Name="sit_id" Type="Decimal" />
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="primary_function" Type="String" />
        </UpdateParameters>
        <SelectParameters>
            <asp:QueryStringParameter QueryStringField="sit" Name="sit_id" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

In the above sqldatasource I am actually using a query string to fetch data in a Formview. User can update or change record if they want to. Now here is my formview structure –

<asp:FormView ID="FormView1" runat="server" AllowPaging="True" 
        DataKeyNames="recId" DataSourceID="dsImported" DefaultMode="Edit" 
        Width="100%">
        <EditItemTemplate>

        <table cellpadding="3" cellspacing="3" >
        <tr><th>Record Id:</th><td><asp:Label ID="Label1" runat="server" Text='<%# Eval("recId") %>' /></td></tr>
<tr><th>Site Id:</th><td><asp:TextBox ID="sit_idTextBox" runat="server" Text='<%# Bind("sit_id") %>' ReadOnly="true" /></td></tr>
<tr><th>Site Name:</th><td><asp:TextBox ID="nameTextBox" runat="server" Text='<%# Bind("name") %>' ReadOnly="true" Width="400" /></td></tr>
<tr><th>Site Type: </th><td>
    <asp:CheckBoxList ID="chkSiteType" runat="server" RepeatColumns="5" DataSourceID="dsSiteType"
        DataTextField="description" DataValueField="returnVal">
    </asp:CheckBoxList>

        <tr><th>Status:</th><td>
            <asp:DropDownList ID="dlStatus" runat="server" selectedValue='<%# Bind("status") %>' >
            <asp:ListItem  Text="Select status" Value="" />
            <asp:ListItem Text="Current" Value="CURRENT" />
            <asp:ListItem Text="Closed" Value="CLOSED" />

            </asp:DropDownList>
        </td></tr>
<tr><th>Primary function:</th><td> 
    <asp:DropDownList ID="dlPrimaryFunction" runat="server" 
        selectedValue='<%# Bind("primary_function") %>' DataSourceID="dsSiteType" 
        DataTextField="description" DataValueField="returnVal" AppendDataBoundItems="true" >
    <asp:ListItem Value="" Text="Select function" />

    </asp:DropDownList>
</td></tr>
<tr><th>Longitude: </th><td><asp:TextBox ID="txtLongitude" runat="server" Text='<%# Bind("longitude") %>' Width="200" /></td></tr>
        <tr><th>Latitude: </th><td><asp:TextBox ID="txtLatitude" runat="server" Text='<%# Bind("latitude") %>' Width="200" /></td></tr>
        <tr><th>Fee Type:</th><td> 
            <asp:CheckBoxList ID="chkFeeType" runat="server" RepeatColumns="6">
                <asp:ListItem Value="ANOFEES">No Fees</asp:ListItem>
                <asp:ListItem Value="BOAT">Boat Tour</asp:ListItem>
                <asp:ListItem Value="PARK">Park Entry</asp:ListItem>
                <asp:ListItem Value="CAMPING">Camping</asp:ListItem>
                <asp:ListItem Value="CAVING">Caving</asp:ListItem>
                <asp:ListItem Value="SITE">Site Entry</asp:ListItem>

            </asp:CheckBoxList>
        </td></tr>
        <tr><th>Access Type: </th><td><asp:CheckBoxList ID="chkAccessType" runat="server" 
                RepeatColumns="7">
            <asp:ListItem Value="BICYCLE">Bicycle</asp:ListItem>
            <asp:ListItem Value="BUS">Bus</asp:ListItem>
            <asp:ListItem Value="CANOE">Canoe</asp:ListItem>
            <asp:ListItem Value="CAR">Car</asp:ListItem>
            <asp:ListItem Value="CARAVAN">Caravan</asp:ListItem>
            <asp:ListItem Value="AIRCRAFT">Fixed Wing Aircraft</asp:ListItem>
            <asp:ListItem Value="FOOT">Foot</asp:ListItem>
            <asp:ListItem Value="4WD">Four Wheel Drive</asp:ListItem>
            <asp:ListItem Value="HELICOPTER">Helicopter</asp:ListItem>
            <asp:ListItem Value="HORSE">Horse</asp:ListItem>
            <asp:ListItem Value="PWR_BOAT">Power Boat</asp:ListItem>
            <asp:ListItem Value="SAIL_BOAT">Sail Boat</asp:ListItem>
            <asp:ListItem Value="Bike">Trail Bike</asp:ListItem>
            </asp:CheckBoxList> </td></tr>
<tr><th>Created on: </th><td><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("create_date") %>'  ReadOnly="true"  /></td></tr>
<tr><th>Last updated: </th><td><asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("update_date") %>' ReadOnly="true" /></td></tr>
<tr><th>Created on: </th><td></td></tr>
<tr><th>Created on: </th><td></td></tr>
<tr><th>Created on: </th><td></td></tr>
        </table>
            <asp:Label ID="lblFeetype" runat="server" Text='<%# Eval("fee_type") %>' Visible="false" /> <br />
            <asp:Label ID="lblAccessType" runat="server" Text='<%# Eval("access_type") %>' Visible="false" /> <br />
           <asp:Label ID="lblSiteType" runat="server" Text='<%# Eval("sit_type") %>' Visible="false" />
            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 
                CommandName="Update" Text="Update" />
            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 
                CausesValidation="False" PostBackUrl="~/importSite.aspx" Text="Cancel" />

                <asp:SqlDataSource ID="dsSiteType" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" 
        SelectCommand="SELECT [description], [returnVal] FROM [rtt_param] WHERE ([CODE] = @CODE)">
        <SelectParameters>
            <asp:Parameter DefaultValue="SITE_TYPE" Name="CODE" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
        </EditItemTemplate>

    </asp:FormView>
    <asp:Label ID="lblMsg" runat="server" ></asp:Label>
<br />

Now in the above formview I also have couple of dynamic dropdown list populated from database table. Hope it is easy to understand. If you look at the above code closely, you will notice I have binded data in all textbox and dropdown list controls however I did not bind data in checkbox list control at this stage.

Lets start first bind the data dynamically in those checkboxes. So I want to bind data on formview databound event. To retrieve the value from database for these checkboxes I have created a few labels to fetch the data. I also have set the visible property to false so that they are not visible to user.

Now the aim is to grab the value from these label controls and split them into arrays then fetch the data into checkbox list control :).

In VB –

Protected Sub FormView1_DataBound(sender As Object, e As System.EventArgs) Handles FormView1.DataBound
        'access the checkbox list control in Formview
        Dim c As CheckBoxList = CType(FormView1.FindControl("chkFeeType"), CheckBoxList)
         'access the Label control in Formview
        Dim feeType As Label = CType(FormView1.FindControl("lblFeetype"), Label)
        'get the value of label field
        Dim rawVal As String = feeType.Text

        'create an array using split 
        Dim str As String() = rawVal.Split(":"c)

        'loop through the array and bind to checkbox list
        For i As Integer = 0 To c.Items.Count - 1
            If str.Contains(c.Items(i).Value) Then
                c.Items(i).Selected = True
            End If
        Next

    End Sub

In C# –

FormView1_DataBound(object sender, System.EventArgs e)
{
	//access the checkbox list control in Formview
	CheckBoxList c = (CheckBoxList)FormView1.FindControl("chkFeeType");
	//access the Label control in Formview
	Label feeType = (Label)FormView1.FindControl("lblFeetype");
	//get the value of label field
	string rawVal = feeType.Text;

	//create an array using split 
	string[] str = rawVal.Split(':');

	//loop through the array and bind to checkbox list
	for (int i = 0; i <= c.Items.Count - 1; i++) {
		if (str.Contains(c.Items(i).Value)) {
			c.Items(i).Selected = true;
		}
	}

}

[warning]I have omitted some codes here, just wanted to show one example how to fetch checkbox list dynamically. Follow the same procedure if you have multiple checkbox list like mine.[/warning]

Here is the screen shot when data fetched in checkboxes –

You can see in the above screen data are nicely fetched in CheckboxList. Now the question is how can I update the data when user tick or un-tick any of those check boxes. Well to do this I had to create a custom function and event and call that in page load to bind update parameters in sqldatasource.

Lets look at the function first, this function basically grab the value from checkboxlist and combine them together in whatever format I want.

In VB –

Private Function GetStringFromCheckBoxList(ByVal chk As CheckBoxList) As String

        Dim strTemp As String = String.Empty

        For Each lst As ListItem In chk.Items
            If lst.Selected Then
                strTemp = strTemp & lst.Value & ":"
            End If
        Next

        Return strTemp.Trim(":")

    End Function

In C# –

private string GetStringFromCheckBoxList(CheckBoxList chk)
{

	string strTemp = string.Empty;

	foreach (ListItem lst in chk.Items) {
		if (lst.Selected) {
			strTemp = strTemp + lst.Value + ":";
		}
	}

	return strTemp.Trim(":");

}

The above function basically grabbing the selected value from checkbox list and return as string with colon(:) separated value. Now I got the the string value and I have to update the database column. Here is my code on page load –

In VB –

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        'access the checkbox lists in FormView control
        Dim chkBox As CheckBoxList = CType(FormView1.FindControl("chkFeeType"), CheckBoxList)
        Dim chkAccess As CheckBoxList = CType(FormView1.FindControl("chkAccessType"), CheckBoxList)
        Dim chkSite As CheckBoxList = CType(FormView1.FindControl("chkSiteType"), CheckBoxList)
        If Me.IsPostBack Then

            'collect and format the checkbox list values and set to string variable
            Dim st As String = GetStringFromCheckBoxList(chkBox)
            Dim ac As String = GetStringFromCheckBoxList(chkAccess)
            Dim sit As String = GetStringFromCheckBoxList(chkSite)

            'assign update parameter values here to update database
            dsImported.UpdateParameters.Add("fee_type", st)
            dsImported.UpdateParameters.Add("access_type", ac)
            dsImported.UpdateParameters.Add("sit_type", sit)
        End If

    End Sub

In C# –

Page_Load(object sender, System.EventArgs e)
{
	//access the checkbox lists in FormView control
	CheckBoxList chkBox = (CheckBoxList)FormView1.FindControl("chkFeeType");
	CheckBoxList chkAccess = (CheckBoxList)FormView1.FindControl("chkAccessType");
	CheckBoxList chkSite = (CheckBoxList)FormView1.FindControl("chkSiteType");

	if (this.IsPostBack) {
		//collect and format the checkbox list values and set to string variable
		string st = GetStringFromCheckBoxList(chkBox);
		string ac = GetStringFromCheckBoxList(chkAccess);
		string sit = GetStringFromCheckBoxList(chkSite);

		//assign update parameter values here to update database
		dsImported.UpdateParameters.Add("fee_type", st);
		dsImported.UpdateParameters.Add("access_type", ac);
		dsImported.UpdateParameters.Add("sit_type", sit);
	}

}

Now here is your fully functional checkbox list databind in very simple and easy way. Let me know if you have any problem or issue implementing this. Good luck