根据数据库中另一个CheckedListbox的选中值填充一个checkkedlistbox

Question

我正在使用SQL Server 2016 Visual studio 2017。
需要根据Checkedlistbox1的SelectedItem的值从我的数据库中填充checkedlistbox2。

我正在填写表格上的checkedlistbox1加载如下:

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

在这里，我试图使用一种方法来填充Checkedlistbox2，我在Checkedlistbox1的ItemCheck事件处理程序中调用:

下面的代码没有给出所需的结果

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

我要调用的函数:

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

我没有得到结果。
如果我检查checkedlistbox1中的(DepartmentName)，SubDeptName应该在checkedlistbox2中加载。如果我在checkedlistbox1中取消选择相同的，它应该从checkedlistbox2中删除或删除，请帮助工作代码示例。提前感谢

Answer 1

大多数数据库对象需要被处置。Connections、Commands和DataReaders有一个Dispose方法，必须调用该方法来释放它们使用的非托管资源。使用Using...End Using块确保即使出现错误也会调用Dispose，这对我们来说很容易。

DataSource、DisplayMember和ValueMember的设置顺序很重要。设置数据源应该是最后一个线。如果将ValueMember设置在DataSource之前，则不会触发任何动作。如果您先设置Datasource，框将为您绑定值成员。然后，你要设置一个新的ValueMember(你想要的)和盒子将不得不重新连接绑定。因此，如果您最后设置DataSource，绑定将只发生一次。

可以使用CheckedItems集合进行循环。将每个项目添加到列表中。在循环之后，使用Join和逗号分隔符为sql字符串准备In子句。我使用了一个内插字符串来构建由前面的$指示的sql字符串。然后可以将变量插入到{ }包围的行中。

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