looping through datareader

17 Jan 2006 10:17 PM

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.

17 Jan 2006 10:40 PM

Kerry Moorman

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

Show quoteHide quote

"tjon***@phenom-biz.com" wrote:

> 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.
>
>

18 Jan 2006 9:12 AM

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

18 Jan 2006 1:55 PM

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.