Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Looping thru ExcuteReader result closes the connection?

This is VB.Net. Why is this working on this and what can I do to fix it?

That "While" statement is causing "Do While (drShipment.Read()) not to have records. If I comment out the "Do While"...then the next line works fine and "drShipment.Read()" has rows.

I think the "While" loop closes the datareader. How can I keep it open?

cmdShipment = New SqlCommand(....)


   cmdShipment.CommandTimeout = 3600
                drShipment = cmdShipment.ExecuteReader(CommandBehavior.CloseConnection) '***I've removed CommandBehavior.CloseConnection but this While loop is making datareader not to have any rows


            Dim count As Integer = 0
             '***** this is causing "Do While" datareader below not to have any rows
         
                If drShipment.HasRows
                    While drShipment.Read()
                        count = count + 1
                    End While
                End If
...
'**** if I remove the While loop above, this line works and datareader has data
 Do While (drShipment.Read())
    ....

Open in new window

Avatar of HainKurt
HainKurt
Flag of Canada image

does not close, it reads and pointer show EOF
you should do MoveFirts to loop again...
SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you can load the data into a datatable

    Dim dataReader = cmd.ExecuteReader()
    Dim dataTable = New DataTable()
    dataTable.Load(dataReader)

Open in new window


then you can use it like
For Each row As DataRow In dataTabl.Rows
   For Each column As DataColumn In thisTable.Columns
      Console.WriteLine(row(column))
   Next
Next

Open in new window


Avatar of Camillia

ASKER

Thanks, guys, for the explanation. I'll try it. 
No, didn't work. I'll go step by step

1. I see three rows. Note that I tried this with and without CommandBehavior.CloseConnection
User generated image2. Loads in datatable but datatable is empty

User generated image3. It fails here Do While (drShipment.Read()) with this error
User generated image

4. If I remove the DataRow loop...the Do While (drShipment.Read())   works. So the reader gets closed still?

I just want to get the number of rows in drShipment = cmdShipment.ExecuteReader

(I'll Google again. This can't be that hard :))
put breakpoint on "for each..." line and check the value of dataTable
(dont check the value before it, I am assuming debugger will loop it and consume it, not sure though)
it should have records, if the datareader is returning any records...

>> if the datareader is returning any records... 

It has records. Screenshot above. That "dataTable" is empty after ".Load"

I'll try it again but debugged it several times. 
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks , Eric. Let me see. 
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial