asked on

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

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

Imperdonato

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

ASKER CERTIFIED SOLUTION

Imperdonato

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bham3dman

ASKER

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.

bham3dman

ASKER

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

bham3dman

ASKER

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