Insert checked unchecked items in CheckedListBox to database in Windows Forms

Hi brahounkamel,

Using this article i have created the example.

Bind (Populate) CheckedListBox from Database using DataTable (DataSet) in Windows Application using C# and VB.Net

Refer the sample and modify the code accordingly.

Note: I am using sql Database. So i am using SqlClient. You need to change it with OledbClient.

Namespaces

C#

using System.Data.SqlClient;
using System.Linq;

VB.Net

Imports System.Data.SqlClient
Imports System.Linq

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    string constring = @"Data Source=.\SQL2022;Initial Catalog=AjaxSamples;uid=sa;pwd=pass@123";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter("SELECT CustomerId, Name FROM Customers", con))
        {
            //Fill the DataTable with records from Table.
            DataTable dt = new DataTable();
            sda.Fill(dt);

            //Assign DataTable as DataSource.
            clbCustomers.DataSource = dt;
            clbCustomers.DisplayMember = "Name";
            clbCustomers.ValueMember = "CustomerId";
        }
    }
}

private void btnSubmit_Click(object sender, EventArgs e)
{
    // Referencing the UnChecked Items.
    string unCheckedItems = string.Join(", ", (from object item in clbCustomers.Items
                                                where !clbCustomers.CheckedItems.Contains(item)
                                                select (item as DataRowView).Row[1]).ToArray());
    // Referencing the Checked Items.
    string checkedItems = string.Join(", ", (from object item in clbCustomers.Items
                                                where clbCustomers.CheckedItems.Contains(item)
                                                select (item as DataRowView).Row[1]).ToArray());
    // Insert into Database.
    string query = "INSERT INTO Table (Checked, UnChecked) VALUES(@Checked, @UnChecked)";
    string constring = @"Data Source=.\SQL2022;Initial Catalog=AjaxSamples;uid=sa;pwd=pass@123";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@Checked", checkedItems);
            cmd.Parameters.AddWithValue("@UnChecked", unCheckedItems);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim constring As String = "Data Source=.\SQL2022;Initial Catalog=AjaxSamples;uid=sa;pwd=pass@123"
    Using con As SqlConnection = New SqlConnection(constring)
        Using sda As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerId, Name FROM Customers", con)
            'Fill the DataTable with records from Table.
            Dim dt As DataTable = New DataTable()
            sda.Fill(dt)

            'Assign DataTable as DataSource.
            clbCustomers.DataSource = dt
            clbCustomers.DisplayMember = "Name"
            clbCustomers.ValueMember = "CustomerId"
        End Using
    End Using
End Sub

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    ' Referencing the UnChecked Items.
    Dim unCheckedItems As String = String.Join(", ",
                                                (From item In clbCustomers.Items
                                                Where Not clbCustomers.CheckedItems.Contains(item)
                                                Select (TryCast(item, DataRowView)).Row(1)).ToArray())
    ' Referencing the Checked Items.
    Dim checkedItems As String = String.Join(", ",
                                                (From item In clbCustomers.Items
                                                Where clbCustomers.CheckedItems.Contains(item)
                                                Select (TryCast(item, DataRowView)).Row(1)).ToArray())
    ' Insert into Database.
    Dim query As String = "INSERT INTO Table (Checked, UnChecked) VALUES(@Checked, @UnChecked)"
    Dim constring As String = "Data Source=.\SQL2022;Initial Catalog=AjaxSamples;uid=sa;pwd=pass@123"
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@Checked", checkedItems)
            cmd.Parameters.AddWithValue("@UnChecked", unCheckedItems)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Screenshot

For more details on inserting into database refer the article Insert data into Database in Windows Forms.