We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How do I use results from a datareader to select checkboxlist items?

Medium Priority
570 Views
Last Modified: 2013-11-26
Greetings Experts,

I need a little help with selecting checkboxlist (cblStates) items based on a sql query using a datareader.  My sql query is returning a list of StateIDs that I need to match up with a checkboxlist's item values.  If the checkboxlist item value matches one of the returned StateIDs, I want that checkbox to be checked.

The code below is where I am so far - just can't figure out what to do with the data being read.

Thanks for the help - I'm definitely a beginner in this area.
Dim conString As String
 conString = ConfigurationManager.ConnectionStrings("TTAPSDBConnectionString").ConnectionString
        Dim sqlCon As New SqlConnection(conString)
        Dim reader As SqlDataReader
 
        If sqlCon.State = ConnectionState.Closed Then
            sqlCon.Open()
        End If
 
        Dim cmd As New SqlCommand
        cmd.Connection = sqlCon
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT StateID FROM Product_States WHERE ProductID = " & Session("ProductID")
 
        reader = cmd.ExecuteReader
 
        While reader.Read
 
            'Trying to figure out what to do here.
 
        End While
 
        sqlCon.Close()

Open in new window

Comment
Watch Question

Use the following code:

Please note that I have assumed rest of your code runs fine, and have written code only where you had problems.


Hope that helps.

Dim conString As String
 conString = ConfigurationManager.ConnectionStrings("TTAPSDBConnectionString").ConnectionString
        Dim sqlCon As New SqlConnection(conString)
        Dim reader As SqlDataReader
 
        If sqlCon.State = ConnectionState.Closed Then
            sqlCon.Open()
        End If
 
        Dim cmd As New SqlCommand
        cmd.Connection = sqlCon
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT StateID FROM Product_States WHERE ProductID = " & Session("ProductID")
 
        reader = cmd.ExecuteReader
 
        While reader.Read
            For Each item As ListItem In cblStates.Items
                If item.Value = ("StateID") Then
                    item.Selected = true;
                Else
                    item.Selected = false;
                End If                
            Next
        End While
 
        sqlCon.Close()

Open in new window

Sorry, the code should have been: *Note: I missed "reader" and ToString() in the above snippet.



Dim conString As String
 conString = ConfigurationManager.ConnectionStrings("TTAPSDBConnectionString").ConnectionString
        Dim sqlCon As New SqlConnection(conString)
        Dim reader As SqlDataReader
 
        If sqlCon.State = ConnectionState.Closed Then
            sqlCon.Open()
        End If
 
        Dim cmd As New SqlCommand
        cmd.Connection = sqlCon
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT StateID FROM Product_States WHERE ProductID = " & Session("ProductID")
 
        reader = cmd.ExecuteReader
 
        While reader.Read
            For Each item As ListItem In cblStates.Items
                If item.Value = reader("StateID").ToString() Then
                    item.Selected = true;
                Else
                    item.Selected = false;
                End If                
            Next
        End While
 
        sqlCon.Close()

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for the response Imperdonato.  This almost works.  Since the For Next loops through the checkbox list for each StateID, those checkboxes that initially tested true for earlier loops, test false for the final loop.  Thus the only checkbox left checked is the one that matched the last StateID.

I did step through this and verified that the checkboxes that were supposed to test true did - they just tested false for the final StateID.

Any ideas?

Thanks.

Author

Commented:
I thought the code below might work, but I'm getting the following error:

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"
Dim i As Integer = 0
 
While reader.Read
 If Me.cblStates.Items.Item(i).Value = reader("StateID").ToString() Then
      Me.cblStates.Items.Item(i).Selected = True
 Else
      Me.cblStates.Items.Item(i).Selected = False
 End If
 i += 1
End While

Open in new window

Author

Commented:
Ok, I figured it out.  I really need to get more sleep. :)  So, in a "duh" moment, I figured out a couple of reasons why the above code wouldn't work (a databinding issue and figured out that I have to loop through the checkboxes to attempt a match on each item).  

However, I did figure out a fix for the original problem - I just needed to loop through and deselect each checkbox item first and then select matches in the reader loop.

Thanks for putting me on the right track Imperdonato.
'Deselect all checkboxes
        For Each li In cblStates.Items
            li.Selected = False
        Next
 
'Loop through checkboxlist for each StateID
Dim i As Integer = 0
 
        While reader.Read
 
            For Each item As ListItem In cblStates.Items
                If item.Value = reader("StateID").ToString() Then
                    item.Selected = True
                End If
            Next
 
        End While

Open in new window

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.