如果数据在SQL表中，请检查CHECKLISTBOX中的项目 [英] Check Items in CHECKLISTBOX if data is in SQL Table

问题描述

大家好，



我昨天发布了一个问题，虽然我没有得到我想要的确切答案，但给出的答案引导我找到了正确的答案。分辨率所以我想我再试一次，因为你比我更聪明=）





这是今天的问题：



我有多个表来存储信息。一个表格将CHECKLISTBOX中选择的VALUE MEMBER存储到一个表格中 - 这很有效。



但是，在另一个表格中，我正在做同样的事情，它还有用于数据输入的文本框。我还有一个CHECKLISTBOX，它从第一个表单填充CHECKLISTBOX的DISPLAY MEMBER。登录到应用程序的员工和CHECKLISTBOX中检查的项目的Value Member的Value Member存储在他们自己的唯一表中。



我有此表单上有一个名为EDIT的按钮。当我点击这个按钮时，它会从我的各种表格中加载信息，因为我从COMBOBOX中选择的那些文本框中也可以正常工作。我不能，为了我的生活，弄清楚如何让CHECKLISTBOX检查我在上一段中提到的那个独特的表格中的项目。



我不知所措，会再次感激你的智慧。



这是我的代码到目前为止，但我认为我的方式远远不够......

Hi all,

I posted a question yesterday and while I didn't get the precise answer I was looking for, the answers given guided me to the proper resolution so I figured I'd try this again since you're all smarter than I am =)


Here's today's issue:

I've got multiple tables to store information in. One Form stores the VALUE MEMBER selected in a CHECKLISTBOX into a table - which is working great.

On another Form however, I am doing the same thing and it also has textboxes for data entry. I also have a CHECKLISTBOX which populates the DISPLAY MEMBER of the CHECKLISTBOX from the first form. The Value Member of both the employee logged into the application and the Value Member of the items checked in the CHECKLISTBOX are stored in a unique table of their own.

I have a button called "EDIT" on this form. When I hit this button, it loads the information out of my various tables for whomever I have selected from a COMBOBOX in those same textboxes which works fine as well. I am cannot, for the life of me, figure out how to get the CHECKLISTBOX to Check off the items in the box that are in that unique table I mentioned in my last paragraph.

I am at a loss and would appreciate your wisdom yet again.

Here is my code so far but I think I'm way off the mark....

Dim QueryBrands As String
               QueryBrands = "SELECT * FROM tblBrandsTerminals WHERE XIDTerminal = '" + TermXID.Text + "'"
               Dim reader As SqlDataReader
               Dim commandsql = New SqlCommand(QueryBrands, con)
               reader = commandsql.ExecuteReader
               While reader.Read()
                   For index As Integer = 0 To BrandListBox.Items.Count - 1
                       'clears check mark boxes
                       BrandListBox.SetItemChecked(index, False)
                       'attempts to check mark boxes
                       Dim XDRV As DataRowView = CType(BrandListBox.Items(index), DataRowView)
                       Dim XDR As DataRow = XDRV.Row
                       Dim XValueMember As String = XDR(BrandListBox.ValueMember).ToString()
                       For Each i In BrandListBox.CheckedIndices
                           BrandListBox.SetItemCheckState(i, CheckState.Checked)
                       Next
                   Next

               End While

               reader.Close()

这是检查现在所有的盒子都没有留下那些应该是空白的盒子。



再一次，我知道我可能有点不合适 - 希望你们都可以再次引导我或如果你有方便的源代码也可以使用:)



非常感谢你们！

This is checking all boxes now but not leaving the ones that should be blank unchecked.

Again, I know I am probably way off the mark - hopefully you all can guide me again or if you have the source code handy that would also work as well :)

Thank you all so much!

推荐答案

首先修复 SQL Injection [ ^ ]您的代码中的漏洞。



您还应该在<$ c中包装 SqlCommand 和 SqlDataReader 对象$ c>使用块，以确保始终清理其资源。



您需要从表中加载一个字段 - 不要使用 SELECT * 。



您需要将该字段中的值存储在集合中 - HashSet（Of T）将是适当的，正如PIEBALDconsult建议的那样。



然后循环浏览列表中的每个项目，使用存储的值来确定是否应该检查它。



这样的事情应该有效：

Start by fixing the SQL Injection[^] vulnerability in your code.

You should also wrap the SqlCommand and SqlDataReader objects in Using blocks, to ensure that their resources are always cleaned up.

You need to load a single field from your table - don't use SELECT *.

You need to store the values from that field in a collection - a HashSet(Of T) would be appropriate, as PIEBALDconsult suggested.

You then loop through each item in the list, using the stored values to determine whether or not it should be checked.

Something like this should work:

Dim checkedItems As New HashSet(Of String)(StringComparer.OrdinalIgnoreCase)
Dim QueryBrands As String = "SELECT YourColumn FROM tblBrandsTerminals WHERE XIDTerminal = @TermXID"

Using commandsql As New SqlCommand(QueryBrands, con)
    commandsql.Parameters.AddWithValue("@TermXID", TermXID.Text)
    
    Using reader As SqlDataReader = commandsql.ExecuteReader()
        While reader.Read()
            checkedItems.Add(reader.GetString(0))
        End While
    End Using
End Using

For index As Integer = 0 To BrandListBox.Items.Count - 1
    Dim drv As DataRowView = CType(BrandListBox.Items(index), DataRowView)
    Dim value As String = Convert.ToString(drv(BrandListBox.ValueMember))
    Dim isChecked As Boolean = checkedItems.Contains(value)
    BrandListBox.SetItemChecked(index, isChecked)
Next

如何从数据库中选择XIDTerminal值，将它们放入一个HashSet，然后针对Hashset进行测试？

How about selecting the XIDTerminal values from the database, putting them in a HashSet, and then testing against the Hashset?

这篇关于如果数据在SQL表中，请检查CHECKLISTBOX中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！