CODEWITHSUNDEEP
vb.netado.net
user1342164
user1342164

Reputation: 1454

For each statement not working correctly

I am trying to find each record that containts a certain reference number and then for each record assign it to a session so that it can be added to a PDF. When I run the code below it just executes for 1 reference number even though there may be 3 records I need to get data from. Any idea why it is not execting it for each record? TIA

      cmd = New SqlCommand
            cmd.Connection = Con
            cmd.CommandText = "SELECT [Physician First Name], [Physician Last Name], [Recipient Primary Business Street Address Line 1], [Recipient City], [Recipient State], [Recipient Zip Code] FROM tblData WHERE ReferenceNumber = @ReferenceNumber"
            cmd.Parameters.Add(New SqlParameter("@ReferenceNumber", (ReferenceTextBox.Text)))

    Dim reader As SqlDataReader = cmd.ExecuteReader()

    For Each NPIlookup In reader

                If (reader.Read()) Then

                    Session("DoctorFirstName") = reader(0)


                End If
                Session("PDF") &= Session("DoctorFirstName")

            Next

Upvotes: 0

Views: 257

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131492

That's not how a DataReader works

When you enumerate over a reader, you enumerate over the collection of fields for the current record, not over all records. You need to calll reader.Read() to get the next record, then either get a specifig field with the Item property, get the value cast to a specific type using eg. GetReader, or iterate over the reader's fields with For Each.

Check the reader's documentation here for an example.

To read specific fields you need to write something like this:

While reader.Read()
    Dim firstName=reader.GetString(0)
    ...
End While

To iterate over the fields

While reader.Read()
    For Each value as Object in reader
        ...
    Next        
End While

Upvotes: 1

Aric TenEyck
Aric TenEyck

Reputation: 8032

The reader.Read() in your for each loop advances the reader to the next record, skipping the one you're interested in.

You shouldn't modify the collection (in this case, the reader) during the for each loop.

Upvotes: 5

Related Questions