[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-13
5
Medium Priority
?
504 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

0
Comment
Question by:bham3dman
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23639673
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

0
 
LVL 5

Accepted Solution

by:
Imperdonato earned 2000 total points
ID: 23639715
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

0
 
LVL 5

Author Comment

by:bham3dman
ID: 23639761
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.
0
 
LVL 5

Author Comment

by:bham3dman
ID: 23639919
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

0
 
LVL 5

Author Comment

by:bham3dman
ID: 23639999
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

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month17 days, 16 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question