Retain CheckBox checked based on Database on page load using C# and VB.Net in ASP.Net

Hi rob83,

I have created sample code which full-fill your requirement. So please refer the below code logic and implement as per your requirement.

HTML

<div>
    <asp:CheckBoxList ID="chkMusic" runat="server" Font-Bold="true" Font-Size="15px"
        DataTextField="Name" DataValueField="CustomerId" CssClass="panel">
    </asp:CheckBoxList>
    <br />
    <asp:Button Text="Update" OnClick="OnUpdate" runat="server" />
</div>

C#

private string Constring = ConfigurationManager.ConnectionStrings["constr"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.GetDataCheckBox();
    }
}

private void CheckedData(DataTable dt)
{
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        bool isselected = Convert.ToBoolean(dt.Rows[i]["IsSelected"]);
        if (isselected)
        {
            chkMusic.Items.FindByValue(dt.Rows[i]["CustomerId"].ToString()).Selected = true;
        }
    }
}

private void GetDataCheckBox()
{
    using (SqlConnection con = new SqlConnection(Constring))
    {
        using (SqlCommand cmd = new SqlCommand(" Select top 10 CustomerId ,Name,(CASE WHEN (EXISTS(SELECT CheckedId FROM CustomerTemp CM WHERE  CM.CheckedId = Customers.CustomerId )) THEN 1 else 0 END) IsSelected From Customers ", con))
        {
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            chkMusic.DataSource = dt;
            chkMusic.DataBind();
            CheckedData(dt);
        }
    }
}

protected void OnUpdate(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(Constring);
    foreach (ListItem item in chkMusic.Items)
    {
        if (item.Selected)
        {
            if (!IsCheckedValueExists(Convert.ToInt32(item.Value)))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO CustomerTemp values(@CheckedId,@Name)", con))
                {
                    cmd.Parameters.AddWithValue("@CheckedId", item.Value);
                    cmd.Parameters.AddWithValue("@Name", item.Text.Trim());
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        else
        {
            using (SqlCommand cmd = new SqlCommand("Delete from CustomerTemp where CheckedId = @CheckedId", con))
            {
                cmd.Parameters.AddWithValue("@CheckedId", item.Value);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

private bool IsCheckedValueExists(int checkboxId)
{
    SqlConnection con = new SqlConnection(Constring);
    SqlCommand cmd = new SqlCommand("Select Id From CustomerTemp WHERE CheckedId = " + checkboxId, con);
    con.Open();
    SqlDataReader idr = cmd.ExecuteReader();
    bool IsExists = idr.HasRows ? true : false;
    con.Close();
    return IsExists;
}

Vb.Net

Private Constring As String = ConfigurationManager.ConnectionStrings("constr").ToString()
Protected Sub Page_Load(sender As Object, e As EventArgs)
	If Not IsPostBack Then
		Me.GetDataCheckBox()
	End If
End Sub

Private Sub CheckedData(dt As DataTable)
	For i As Integer = 0 To dt.Rows.Count - 1
		Dim isselected As Boolean = Convert.ToBoolean(dt.Rows(i)("IsSelected"))
		If isselected Then
			chkMusic.Items.FindByValue(dt.Rows(i)("CustomerId").ToString()).Selected = True
		End If
	Next
End Sub

Private Sub GetDataCheckBox()
	Using con As New SqlConnection(Constring)
		Using cmd As New SqlCommand(" Select top 10 CustomerId ,Name,(CASE WHEN (EXISTS(SELECT CheckedId FROM CustomerTemp CM WHERE  CM.CheckedId = Customers.CustomerId )) THEN 1 else 0 END) IsSelected From Customers ", con)
			Dim sda As New SqlDataAdapter(cmd)
			Dim dt As New DataTable()
			sda.Fill(dt)
			chkMusic.DataSource = dt
			chkMusic.DataBind()
			CheckedData(dt)
		End Using
	End Using
End Sub

Protected Sub OnUpdate(sender As Object, e As EventArgs)
	Dim con As New SqlConnection(Constring)
	For Each item As ListItem In chkMusic.Items
		If item.Selected Then
			If Not IsCheckedValueExists(Convert.ToInt32(item.Value)) Then
				Using cmd As New SqlCommand("INSERT INTO CustomerTemp values(@CheckedId,@Name)", con)
					cmd.Parameters.AddWithValue("@CheckedId", item.Value)
					cmd.Parameters.AddWithValue("@Name", item.Text.Trim())
					con.Open()
					cmd.ExecuteNonQuery()
					con.Close()
				End Using
			End If
		Else
			Using cmd As New SqlCommand("Delete from CustomerTemp where CheckedId = @CheckedId", con)
				cmd.Parameters.AddWithValue("@CheckedId", item.Value)
				con.Open()
				cmd.ExecuteNonQuery()
				con.Close()
			End Using
		End If
	Next
End Sub

Private Function IsCheckedValueExists(checkboxId As Integer) As Boolean
	Dim con As New SqlConnection(Constring)
	Dim cmd As New SqlCommand("Select Id From CustomerTemp WHERE CheckedId = " + checkboxId, con)
	con.Open()
	Dim idr As SqlDataReader = cmd.ExecuteReader()
	Dim IsExists As Boolean = If(idr.HasRows, True, False)
	con.Close()
	Return IsExists
End Function

Sql

CREATE TABLE [Customers]
(
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
[Country] [varchar](50) NOT NULL
);

CREATE TABLE [dbo].[CustomerTemp]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[CheckedId] [int] NULL,
[Name] [nvarchar](20) NULL
); 

Screenshot