How to Bind Update CheckBoxList from SQL Server Database bit Datatype in ASP.Net using C# VB.Net

The ASP.Net CheckBoxList is one of the most common control we have used every time. When we need to simply bind CheckBoxList from Database we can bind a data source like Datatable easily with the CheckBoxList. But if we need to bind Checked status or Selected status from Database we can not do it in a straight forward way. Because ASP.Net CheckBoxList provides us 3 properties while binding data, those are DataSource, DataTextField and DataValueField. No Checked property or Selected property is available. To achieve this we need to manually iterate each ListItem & need to assign the Selected property individually. Here in this article i will discuss everything regarding ASP.Net CheckBoxList.

Focus Area:
1. Simply Bind ASP.Net CheckBoxList ListItems from SQL Server Database
2. Bind ASP.Net CheckBoxList ListItems from SQL Server Database with Selected Property
3. Save or Update the Selected CheckBox Item to Database

To complete the all examples create a sample table like below:

CREATE TABLE [dbo].[Article](
	[ArticleID] [int] NOT NULL,
	[Title] [varchar](max) NOT NULL,
	[IsPublished] [bit] NOT NULL,
 CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 
([ArticleID] ASC)
)

Insert Some Sample Data:

INSERT INTO ARTICLE VALUES(1,'Populate SQL Data Into Checkboxlist',1)
INSERT INTO ARTICLE VALUES(2,'Checkboxlist data population example',0)
INSERT INTO ARTICLE VALUES(3,'C# Checkboxlist lesson',1)
INSERT INTO ARTICLE VALUES(4,'VB.Net Checkboxlist Example',0)
INSERT INTO ARTICLE VALUES(5,'Asp.net Checkboxlist load data',0)

Modify the web.config file to connect to your Database like below:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now prepare the HTML Markup like below:

    <asp:CheckBoxList ID="CheckBoxList1" runat="server">
    </asp:CheckBoxList>

Everything is ready now to implement the example “How to Populate Update CheckBoxList from SQL Server Database bit (Boolean) Datatype in ASP.Net using C# VB.Net“.

1. Simply Bind CheckBoxList ListItems from SQL Server Database:
To do that just write the below server side code under Page_Load Event:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
            {
                SqlDataAdapter ad = new SqlDataAdapter("SELECT ArticleID,Title from Article", conn);
                ad.Fill(dt);
            }
            CheckBoxList1.DataSource = dt;
            CheckBoxList1.DataTextField = "Title";
            CheckBoxList1.DataValueField = "ArticleID";
            CheckBoxList1.DataBind();
            
            //LoadCheckboxlixtData();
        }
    }

Note: Don’t forget to add “using System.Data.SqlClient;”, “using System.Configuration;”, “using System.Data;” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable = New DataTable()
            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
                Dim ad As SqlDataAdapter = New SqlDataAdapter("SELECT ArticleID,Title from Article", conn)
                ad.Fill(dt)
            End Using

            CheckBoxList1.DataSource = dt
            CheckBoxList1.DataTextField = "Title"
            CheckBoxList1.DataValueField = "ArticleID"
            CheckBoxList1.DataBind()
            'LoadCheckboxlixtData()
        End If
    End Sub

Note: Don’t forget to add “Imports System.Data.SqlClient”, “Imports System.Configuration”, “Imports System.Data” namespaces.

Now run the example, will get an output like below:
Bind_Checkboxlist_From_Database_Csharp_VB.net

Note: If you look at the output, one thing is missing. The first & third checkbox not selected. Why? Read next.

2. Bind ASP.Net CheckBoxList ListItems from Database with Selected Property:
First remove all above code from Page_Load event. Now write a “LoadCheckboxlixtData()” server side method & invoke it from Page_Load:
C# Code:

    private void LoadCheckboxlixtData()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Article", conn))
            {
                conn.Open();
                using (SqlDataReader dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        ListItem oItem = new ListItem();
                        oItem.Text = dataReader["Title"].ToString();
                        oItem.Value = dataReader["ArticleID"].ToString();
                        oItem.Selected = Convert.ToBoolean(dataReader["IsPublished"]);
                        CheckBoxList1.Items.Add(oItem);
                    }
                }
                conn.Close();
            }
        }
    }

Invoke from Page_Load:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadCheckboxlixtData();
        }
    }

VB.Net Code:

    Protected Sub LoadCheckboxlixtData()
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
            Using cmd As New SqlCommand("SELECT * FROM Article", conn)
                conn.Open()
                Using dataReader As SqlDataReader = cmd.ExecuteReader()
                    While (dataReader.Read())
                        Dim oItem As ListItem = New ListItem()
                        oItem.Text = dataReader("Title").ToString()
                        oItem.Value = dataReader("ArticleID").ToString()
                        oItem.Selected = Convert.ToBoolean(dataReader("IsPublished"))
                        CheckBoxList1.Items.Add(oItem)
                    End While
                End Using
                conn.Close()
            End Using
        End Using
    End Sub

Invoke from Page_Load:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            LoadCheckboxlixtData()
        End If
    End Sub

Now run the project hope you will get below output:
Bind_Checkboxlist_From_Database_bit_boolean_Csharp_VB.net
Note: Now first & third checkboxes are selected. Because we iterate every ListItems & set the Selected property to true if the corresponding SQL Server bit value or Boolean value is true.

3. Save or Update the Selected CheckBox Item to Database:
Remove previous HTML markup & modify the HTML markup like below:

    <asp:Label runat="server" ID="lblStatus" Font-Bold="true"></asp:Label>

    <asp:CheckBoxList ID="CheckBoxList1" runat="server">
    </asp:CheckBoxList>
    
    <br/>
    
    <asp:Button runat="server" ID="cmdArticle" Text="Save Data"
        onclick="cmdArticle_Click" />

Keep the LoadCheckboxlixtData() method as it is. Now write the below code under UPDATE button (cmdArticle) click event:
C# Code:

    protected void cmdArticle_Click(object sender, EventArgs e)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE Article Set IsPublished = @IsPublished WHERE ArticleId=@ArticleId", conn))
                {
                    conn.Open();
                    foreach (ListItem oItem in CheckBoxList1.Items)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@IsPublished", oItem.Selected);
                        cmd.Parameters.AddWithValue("@ArticleId", oItem.Value);
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
        }
        
        catch (Exception Ex)
        {
            lblStatus.Text = "Operation Failed !!<br/>" + Ex.Message;
            lblStatus.ForeColor = System.Drawing.Color.Red;
            return;
        }

        lblStatus.Text = "Data Safely Saved.";
        lblStatus.ForeColor = System.Drawing.Color.Green;
    }

VB.Net Code:

    Protected Sub LoadCheckboxlixtData()
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
            Using cmd As New SqlCommand("SELECT * FROM Article", conn)
                conn.Open()
                Using dataReader As SqlDataReader = cmd.ExecuteReader()
                    While (dataReader.Read())
                        Dim oItem As ListItem = New ListItem()
                        oItem.Text = dataReader("Title").ToString()
                        oItem.Value = dataReader("ArticleID").ToString()
                        oItem.Selected = Convert.ToBoolean(dataReader("IsPublished"))
                        CheckBoxList1.Items.Add(oItem)
                    End While
                End Using
                conn.Close()
            End Using
        End Using
    End Sub

Now run the project & test the application like below:
Populate_Checkboxlist_database_Bit_Value_Csharp_VB.Net

Hope everything clear. If not yet then download the source full code from below & examine:

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*