In this article I will explain with an example, how to save and retrieve
CheckBoxList from
SQL Server database in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Hobbies with the schema as follows.
![Save and Repopulate Users selections from Database using ASP.Net CheckBoxList]()
I have already inserted few records in the table
![Save and Repopulate Users selections from Database using ASP.Net CheckBoxList]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The HTML Markup consists of following controls:
CheckBoxList – For displaying data.
Button – For updating record.
The Button has been assigned with an OnClick event handler.
Hobbies:
<asp:CheckBoxList ID="chkHobbies" runat="server"></asp:CheckBoxList>
<br />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="UpdateHobbies" />
Namespaces
You will need to import the following namespaces.
C#
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Configuration
Imports System.Data.SqlClient
Binding the CheckBoxList from Database
Inside the Page_Load event handler, the PopulateHobbies method is called.
Inside the
PopulateHobbies method, the records are fetched from the
SQL Server database using
ExecuteReader method.
Then, WHILE loop is executed and ListItem class object is created where Text, Value, Selected properties are set.
The Checked and Unchecked state of
CheckBoxList will be decided based on
IsSelected column value fetched from the
SQL Server database.
If fetched value of IsSelected column is 1 then the CheckBox will be checked and if it is 0 then the CheckBox will be set as unchecked.
Finally, the ListItem class object is added to the CheckBoxList Items.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.PopulateHobbies();
}
}
private void PopulateHobbies()
{
string sql = "SELECT HobbyId, Hobby, IsSelected FROM Hobbies";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
ListItem item = new ListItem();
item.Text = sdr["Hobby"].ToString();
item.Value = sdr["HobbyId"].ToString();
item.Selected = Convert.ToBoolean(sdr["IsSelected"]);
chkHobbies.Items.Add(item);
}
}
con.Close();
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.PopulateHobbies()
End If
End Sub
Private Sub PopulateHobbies()
Dim sql As String = "SELECT HobbyId, Hobby, IsSelected FROM Hobbies"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(sql, con)
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
Dim item As ListItem = New ListItem()
item.Text = sdr("Hobby").ToString()
item.Value = sdr("HobbyId").ToString()
item.Selected = Convert.ToBoolean(sdr("IsSelected"))
chkHobbies.Items.Add(item)
End While
End Using
con.Close()
End Using
End Using
End Sub
Updating records based on the Selections into the SQL Server Database
When UpdateHobbies button is clicked, the Update query is executed using SqlCommand class.
Then, a FOREACH loop is executed over the CheckBoxList items using ListItem and IsSelected, HobbyId is fetched.
Finally, the record is updated into the
SQL Server database using
ExecuteNonQuery function.
C#
protected void UpdateHobbies(object sender, EventArgs e)
{
string sql = "UPDATE Hobbies SET IsSelected = @IsSelected WHERE HobbyId = @HobbyId";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
foreach (ListItem item in chkHobbies.Items)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@IsSelected", item.Selected);
cmd.Parameters.AddWithValue("@HobbyId", item.Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
VB.Net
Protected Sub UpdateHobbies(ByVal sender As Object, ByVal e As EventArgs)
Dim sql As String = "UPDATE Hobbies SET IsSelected = @IsSelected WHERE HobbyId = @HobbyId"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(sql, con)
For Each item As ListItem In chkHobbies.Items
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@IsSelected", item.Selected)
cmd.Parameters.AddWithValue("@HobbyId", item.Value)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Next
End Using
End Using
End Sub
Screenshots
The Form
![Save and Repopulate Users selections from Database using ASP.Net CheckBoxList]()
Records after Updating
![Save and Repopulate Users selections from Database using ASP.Net CheckBoxList]()
Downloads
