I need help with my VB code trying to fill a checkedlistbox and check items based on a value in SQL

I am trying to read in all columns in a SQL table to a VB checklistbox item and then check the ones that have a "checked" value of 1 in SQL.
Everything is working except it throws an exception when I get to the line where it is supposed to check one.
I am a pretty basic skilled programmer.
Any help is appreciated.
The line below that throws the exception is CheckedListBox1.SetItemCheckState(Index, CheckState.Checked)
Here is my code:
  Dim connectionString As String
        Dim cnn As SqlClient.SqlConnection
        Dim cmd As SqlClient.SqlCommand
        Dim sql As String
        Dim reader As SqlClient.SqlDataReader
        Dim i As Integer = 0
        Dim td As DateTime = DateTime.Now
        Dim Newdate = td.ToString("yyyy-MM-dd")

        connectionString = "Data Source=SQLSERVER\SQLEXPRESS;Initial Catalog=DBName;Persist Security Info=True;User ID=FakeID;Password=FakePassword"
        sql = "select CONVERT(VARCHAR(10), AddTime, 120),PatientMRN,PatientName,Checked,Completed from CarePlanOCR where CONVERT(VARCHAR(10), DateColumn, 120) = '" + Newdate + "'" + " order by CONVERT(VARCHAR(10), AddTime, 120)"
        cnn = New SqlClient.SqlConnection(connectionString)
        Try
            cnn.Open()
            cmd = New SqlClient.SqlCommand(sql, cnn)
            reader = cmd.ExecuteReader()
            reader.Read()
            While reader.Read()
                Dim checkedstate As Integer = reader.Item(3)
                'Add based on checked state
                If checkedstate = 1 Then

                    Dim Index As Integer = CheckedListBox1.Items.Add(reader.Item(0) + " - " + reader.Item(1) + " - " + reader.Item(2))
                    CheckedListBox1.SetItemCheckState(Index, CheckState.Checked)


                Else
                    Dim index As Integer = CheckedListBox1.Items.Add(reader.Item(0) + " - " + reader.Item(1) + " - " + reader.Item(2))

                End If

            End While
            reader.Close()
            cmd.Dispose()
            cnn.Close()

        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

Open in new window

OHACIIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Please post the exception and inner exception for the error you are receiving?
0
OHACIIT DirectorAuthor Commented:
I apologize I am not a programmer, how do I do this in visual studio 2010?
0
Fernando SotoRetiredCommented:
Hi OHACI;

Try changing your code to this one.
If checkedstate = 1 Then
    Dim Index As Integer = CheckedListBox1.Items.Add(reader.Item(0) + " - " + reader.Item(1) + " - " + reader.Item(2), True)
Else
    Dim index As Integer = CheckedListBox1.Items.Add(reader.Item(0) + " - " + reader.Item(1) + " - " + reader.Item(2))
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

OHACIIT DirectorAuthor Commented:
The same thing, it throws an exception as soon as it hits a record with the checked state = 1
0
Fernando SotoRetiredCommented:
Hi OHACI;

To get the exception details you can do the following. Please note the display of the image below will be different because of the different versions of Visual Studio. You should find a link that states "Copy Details", in the image below next to last line, click on it and it will copy to the clipboard. Now paste that in to a comment on this question. Please in the Write Comment box find the word CODE and click on it then paste the exception message by pressing Ctrl-V on the keyboard.Exception
0
OHACIIT DirectorAuthor Commented:
I figured out the issue, my try and my catch exception was not allowing me to see exactly where the exception was happening.  Once I removed this I could see it was in another part of my code where it was looking at the "selected checklistbox2 item" and since nothing was selected on form load it was throwing out the exception.
I will mark your code with true on the end as answered because that does work once I fixed the other issue.
Thanks for your help Fernando!
0
Fernando SotoRetiredCommented:
Very good OHACI, always glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.