looping through datareader

[tjonsek]

Albeit new to VB.NET, I've done this several times before, but today
I've run into an interesting problem. I am trying to loop through a
datareader and display the data in a spreadsheet. So far, I only get 1
row to display. (I know it has multiple rows)
Here's the code for the loop:

xlRow = "4"
With reader
.Read()
Do While .HasRows
xlSheet.Cells(xlRow, 1) = .Item("emp_name")
xlSheet.Cells(xlRow, 2) = .Item("dt_filed")
xlSheet.Cells(xlRow, 3) = .Item("dt_check")
xlSheet.Cells(xlRow, 4) = .Item("g_type")
xlSheet.Cells(xlRow, 5) = .Item("g_desc")
xlSheet.Cells(xlRow, 6) = .Item("totPay")
xlSheet.Cells(xlRow, 7) = .Item("dept")
xlSheet.Cells(xlRow, 8) = .Item("union_name")
xlRow = xlRow + 1
.NextResult()
Loop
End With
reader.Close()

I appreciate any help.

 

[Guest]

tjonsek,

The NextResult method moves to the next result when you are executing
multiple sql statements.

You probably want something like:

With reader
Do While .Read()
xlSheet.Cells(xlRow, 1) = .Item("emp_name")
xlSheet.Cells(xlRow, 2) = .Item("dt_filed")
xlSheet.Cells(xlRow, 3) = .Item("dt_check")
xlSheet.Cells(xlRow, 4) = .Item("g_type")
xlSheet.Cells(xlRow, 5) = .Item("g_desc")
xlSheet.Cells(xlRow, 6) = .Item("totPay")
xlSheet.Cells(xlRow, 7) = .Item("dept")
xlSheet.Cells(xlRow, 8) = .Item("union_name")
xlRow = xlRow + 1
Loop
End With
reader.Close()

Kerry Moorman

 

[Cyril Gupta]

Let me explain what Kerry did so that you will understand it.

DataReaders moves to the next record everytime you call the Read method.

In your code you have used

Do While .HasRows

Well, the DataReader will keep having Rows, and it will go in an endless
loop

Do this instead

If .HasRows Then
Do While .Read
bla bla bla
Loop
End If

Regards
Cyril Gupta

 

[tjonsek]

Great. Thanks so much. I've been an ASP coder for a long time, finally
convincing everyone here to switch to .NET, but it's up to me to teach
myself. I'm not familiar with datareaders and only just started
utilizing them. What you said Cyril made sense. Thanks, both of you.