Fill a CheckedListbox from a database based on checked values of another CheckedListbox

Question

I am using SQL Server 2016 Visual studio 2017.
Need to fill checkedlistbox2, from my database, based on the value of the SelectedItem of Checkedlistbox1.

I am filling checkedlistbox1 on Form.Load as below: This code is working.

Private Sub fillChkboxList()
    Dim conn As New SqlConnection("Data Source=192.168.200.36;user id=sa;password=XXXX@123;database=XXXXXXX")
    Dim sda As New SqlDataAdapter("select DepartmentName, DepartmentID from DepartmentMain where active=1 order by DepartmentName", conn)
    Dim dt As New DataTable
    sda.Fill(dt)
    CheckedListBox1.DataSource = dt
    CheckedListBox1.DisplayMember = "DepartmentName"
    CheckedListBox1.ValueMember = "DepartmentID"
End Sub

Here I am trying to use a method to fill Checkedlistbox2, which I am calling in the ItemCheck event handler of Checkedlistbox1:

Below Code is not giving required results

Public Function fillChkboxListSub()
    Dim i As Integer
    Dim conn1 As New SqlConnection("Data Source=192.168.200.36;user id=sa;password=XXXX@123;database=XXXXXXX")

    With CheckedListBox2
        For i = 0 To CheckedListBox1.Items.Count - 1 Step i + 1
            If CheckedListBox1.GetItemCheckState(i) = CheckState.Checked Then
                Dim xx As String = (CType(CheckedListBox1.Items(i), DataRowView))("DepartmentID")
                Dim sqlstr2 As String = "select SubName,SubDeptID from DepartmentSub where active=1 and DepartmentID in ('" & xx & "') order by SubName"
                Dim command2 As New SqlCommand(sqlstr2, conn1)
                Dim adpt2 As New SqlDataAdapter(command2)
                adpt2.SelectCommand = command2
                adpt2.Fill(dt2)

                CheckedListBox2.DataSource = dt2
                CheckedListBox2.DisplayMember = "SubName"
                CheckedListBox2.ValueMember = "SubDeptID"
            End If
        Next
    End With
End Function

This function I am calling on:

Private Sub CheckedListBox1_ItemCheck(sender As Object, e As ItemCheckEventArgs) Handles CheckedListBox1.ItemCheck
    fillChkboxListSub()
End Sub

I am not getting the result.
If I check the (DepartmentName) in checkedlistbox1, SubDeptName should load in checkedlistbox2. If I deselect the same in checkedlistbox1, it should be deleted or removed from checkedlistbox2Please help with working code example. Thanks in Advance

Answer 1

Most database objects need to be disposed. Connections, Commands, and DataReaders have a Dispose method that must be called to release unmanaged resources that they use. It is made easy for us with Using...End Using blocks that ensure that Dispose is called even if there is an error.

The order of setting the DataSource, DisplayMember, and ValueMember is important. Setting DataSource should be last line. If you set ValueMember before DataSource no action is triggered. If you set Datasource first, the box will bind value member for you. Then, you're going to set a new ValueMember (the one you want) and box will have to re-wire binding. So, if you set DataSource last, bindings will happen only once.

You can use the CheckedItems collection to loop through. Add eacn item to a list. After the loop use Join with a comma separator to prepare the In clause for the sql string. I used an interpolated string to build the sql string indicated by the preceding $. Variables can then be inserted in line surrounded by { } .

Private CnString As String = "Data Source=192.168.200.36;user id=sa;password=XXXX@123;database=XXXXXXX"
Private Sub fillChkboxList1()
    Dim dt As New DataTable
    Using conn As New SqlConnection(CnString),
        cmd As New SqlCommand("select DepartmentName, DepartmentID from DepartmentMain where active=1 order by DepartmentName", conn)
        conn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    CheckedListBox1.DisplayMember = "DepartmentName"
    CheckedListBox1.ValueMember = "DepartmentID"
    CheckedListBox1.DataSource = dt
End Sub
Public Sub fillChkboxList2()
    Dim lst As New List(Of Integer)
    For Each item In CheckedListBox1.CheckedItems
        Dim drv = DirectCast(item, DataRowView)
        Dim DepId As Integer = CInt(drv("DepartmentId"))
        lst.Add(DepId)
    Next
    Dim DepIdString = String.Join(",", lst)
    Dim sql As String = $"select SubName,SubDeptID from DepartmentSub where active=1 and DepartmentID in ({DepIdString}) order by SubName"
    Debug.Print(sql) 'See if your select string looks correct.
    Dim dt As New DataTable
    Using cn As New SqlConnection(CnString),
            cmd As New SqlCommand(sql, cn)
        cn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    CheckedListBox2.DisplayMember = "SubName"
    CheckedListBox2.ValueMember = "SubDeptID"
    CheckedListBox2.DataSource = dt
End Sub