Camillia
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?
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())
....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can load the data into a datatable
Dim dataReader = cmd.ExecuteReader()
Dim dataTable = New DataTable()
dataTable.Load(dataReader)
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
ASKER
Thanks, guys, for the explanation. I'll try it.
ASKER
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
2. Loads in datatable but datatable is empty
3. It fails here Do While (drShipment.Read()) with this error
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 :))
1. I see three rows. Note that I tried this with and without CommandBehavior.CloseConnection
2. Loads in datatable but datatable is empty
3. It fails here Do While (drShipment.Read()) with this error
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...
(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...
ASKER
>> 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.
It has records. Screenshot above. That "dataTable" is empty after ".Load"
I'll try it again but debugged it several times.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks , Eric. Let me see.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you should do MoveFirts to loop again...