Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

VB Check CheckListBox From Database

I have a checklistbox in my windows form.  I am querying data from database into sqldatareader.  How do I check the checklistbox if the value is a string concatenated by comma.


Example, my data is as below.


Apple, Orange, Banana


I would like it to check the following:  

User generated image

The below code only add the list to my checklistbox instead of checking the boxes like the above.

Dim dr As SqlDataReader
   Dim strQuery As String = "SELECT NAME WHERE ID = '" & strID & "'"

        Dim myCon As New SqlConnection(strCon)
        Dim myCom As New SqlCommand(strQuery, myCon)
        myCom.CommandTimeout = 500
        myCon.Open()

        dr = myCom.ExecuteReader(CommandBehavior.CloseConnection)

        While dr.Read
            If drProject.HasRows Then
                chklstFruit.BeginUpdate()
                chklstFruit.Items.Add(dr.Item("NAME"))
                chklstFruit.EndUpdate()
            End If
        End While

Open in new window




Avatar of Norie
Norie

How are you originally populating the listbox?
If you have

dim selected
selected = "Apple, Orange, Banana"

if (InStr(selected,"Orange ") then
  ' check the box
end if

Open in new window

That is an easy option. Except if the choices are  Apples, Oranges"  you can see that looking for the singular would still work.

The two things I have done is to create a function that puts the selected items in an array and loops to see if if what you are looking for in a function is in the selected. Or create a Dictionary and that can create a quick look up too.

dim selected
selected = "Apple, Orange, Banana"

if find("Apple", selected) = 1 then
      'we found a match check the box
end if

Function find(needle, haystack)

     result = 0                          ' set result to not found  
     ArrayHaystack = split(haystack,",") ' put comma delimited in an array
     for each item in ArrayHaystack      ' look through array
         if item = needle then
           result = 1                     ' found
        end if
     next

   find = result
End Function

Open in new window

You can do something similar by creating a dictionary and looking up the key https://www.tutorialspoint.com/vbscript/vbscript_dictionary_objects.htm
Avatar of holemania

ASKER

I am populating the checklist box by manually adding it to the Items collection.  I just need to check the list based off each item from a string separated by comma.

Will take a look at what Scott provided tomorrow and update.
So I've got something close, but need to break the string (separated by comma) into separate individual item and compare.  It is going through the list from the checkedlistbox and comparing against my string, but my string is one long string so it does not match.  How could I separate my string into individual item?

Dim dr As SqlDataReader
Dim strQuery As String = "SELECT NAME WHERE ID = '" & strID & "'"

        Dim myCon As New SqlConnection(strCon)
        Dim myCom As New SqlCommand(strQuery, myCon)
        myCom.CommandTimeout = 500
        myCon.Open()

        dr = myCom.ExecuteReader(CommandBehavior.CloseConnection)

Dim Fruits = CStr(dr.Item("NAME").ToString)

For i As Integer = 0 To chklstFruit.Items.Count - 1
   Dim item = (CStr(chklstFruit.Items(i)))
        chklstFruit.SetItemChecked(i, item = Fruits)
Next

Open in new window

Getting closer, but it is only checking 1 item and not multiple items.  I put the string into an Array and did a loop.  It is matching, but not checking the others.

Dim dr As SqlDataReader
Dim strQuery As String = "SELECT NAME WHERE ID = '" & strID & "'"

        Dim myCon As New SqlConnection(strCon)
        Dim myCom As New SqlCommand(strQuery, myCon)
        myCom.CommandTimeout = 500
        myCon.Open()

        dr = myCom.ExecuteReader(CommandBehavior.CloseConnection)

Dim Fruits = CStr(dr.Item("NAME").ToString)
Dim words As String() = Fruits.Split(New Char(){","c}).Select((Function(s) s.Trim)).ToArray()

For i As Integer = 0 To chklstFruit.Items.Count - 1
   For Each word in words
      Dim item = (CStr(chklstFruit.Items(i)))
           chklstFruit.SetItemChecked(i, item = Fruits)
   Next
Next

Open in new window


I got it working now. See below.

Dim dr As SqlDataReader
Dim strQuery As String = "SELECT NAME WHERE ID = '" & strID & "'"

        Dim myCon As New SqlConnection(strCon)
        Dim myCom As New SqlCommand(strQuery, myCon)
        myCom.CommandTimeout = 500
        myCon.Open()

        dr = myCom.ExecuteReader(CommandBehavior.CloseConnection)

Dim Fruits = CStr(dr.Item("NAME").ToString)
Dim words As String() = Fruits.Split(New Char(){","c}).Select((Function(s) s.Trim)).ToArray()

For i As Integer = 0 To chklstFruit.Items.Count - 1
   For Each word in words
      Dim item = (CStr(chklstFruit.Items(i)))
         If item = word then
           chklstFruit.SetItemChecked(i, item = Fruits)
         End If
   Next
Next

Open in new window

Erm ....
First, if you need to break a data into smaller things, it means your database has a design issue, in other words: there is a column agregating data that it should not.
In my opinion, there is a N,N relationship missing somewhere.

I strongly suggest that you look at this first, before implementing workaround solutions.
ASKER CERTIFIED SOLUTION
Avatar of holemania
holemania

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial