Check Items in CHECKLISTBOX if data is in SQL Table

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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....

VB

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!

Posted 24-Apr-15 13:08pm

kaniption

Updated 27-Apr-15 7:45am

v5

Add a Solution

Comments

PIEBALDconsult 24-Apr-15 20:46pm

Please do not use concatenation to form SQL statements; use a parameterized statement.
Also, no reason to test RDR.HasRows
Plus, don't use SELECT * if all you want to do is test for existence.

kaniption 27-Apr-15 10:04am

I only have two columns in this table, that of which I have to reference. Otherwise you are correct, I would have selected the columns needed normally and not *. I was just taking the easy way out considering there are only two columns. I have added the parameter @XIDTerm in place of the textbox per your suggestion, but obviously still having issues.

PIEBALDconsult 27-Apr-15 10:49am

Can you update (Improve) the question with the code as it now stands?

kaniption 27-Apr-15 12:44pm

Sure thing, I pasted new code - its now updating the checkboxes but unfortunately is is checking everything and not just the ones I am looking to be checked. I am saving the information in a similar manner and someone suggested I retrieve it this way as well.

PIEBALDconsult 27-Apr-15 13:13pm

You don't seem to be testing anything in the for loop.

kaniption 27-Apr-15 13:28pm

okay, updated it again. now its not checking anything. :/

Are you able to provide an example so that I can see what I am doing incorrectly? I've researched for about 12 hours online now and I cannot figure it out.

2 solutions

Solution 1

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

Posted 24-Apr-15 15:16pm

PIEBALDconsult

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-04-27T08:04:00

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:

VB.NET

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