Better way?

This code works just the way I want but I was wondering if there was a better way to do it.

For example I needed to get the number of ros returned so I am having to run the read function twice once to get the number of rows then the second time to use the data. AS you can see the first read lets me loop thru so i can create the xls file with out already knowing how many records i will get back.

and the nested for loop part, is that best way to create the xls sheet?

myReader = myCommandCompareResults.ExecuteReader()

While myReader.Read() 'counts the number of records returned.

a = a + 1

End While

myReader.Close()

myReader = myCommandCompareResults.ExecuteReader()

For x = 1 To a 'number of records

For i = 1 To 9 'number of columns

If myReader.Read() Then

excelWorksheet.Cells(i, x) = myReader(0)

excelWorksheet.Cells(i, x + 1) = myReader(1)

excelWorksheet.Cells(i, x + 2) = myReader(2)

excelWorksheet.Cells(i, x + 3) = myReader(3)

excelWorksheet.Cells(i, x + 4) = myReader(4)

excelWorksheet.Cells(i, x + 5) = myReader(5)

excelWorksheet.Cells(i, x + 6) = myReader(6)

excelWorksheet.Cells(i, x + 7) = myReader(7)

excelWorksheet.Cells(i, x + 8) = myReader(8)

End If

Next i

Next x

The myReader.Read() condition should be right after the for x loop. Calling it after the for y loop means that it will be called 9times x the number of records. You only would want to call myReader.Read depending on the number of records. Is this what you really want to do?

It should be:

For x = 1 To a 'number of records
    If myReader.Read() Then
        For i = 1 To 9 'number of columns
            'perform processing
        Next i
    End If
Next x

A better way to do this is:

x = 0
While myReader.Read()
    x = x + 1

    For i = 1 to 9
        excelWorksheet.Cells(x, i) = myReader(i-1)
    Next i
End While

But if it's working just the way you want, my apologies. I'm not sure if there's a better way of doing it. If I may ask, what is it that you want to do anyway? I'm a bit confused with the number of myReader.Reads().

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jan Montano19y ago
The myReader.Read() condition should be right after the for x loop. Calling it after the for y loop means that it will be called 9times x the number of records. You only would want to call myReader.Read depending on the number of records. Is this what you really want to do?

It should be:

For x = 1 To a 'number of records
    If myReader.Read() Then
        For i = 1 To 9 'number of columns
            'perform processing
        Next i
    End If
Next x

A better way to do this is:

x = 0
While myReader.Read()
    x = x + 1

    For i = 1 to 9
        excelWorksheet.Cells(x, i) = myReader(i-1)
    Next i
End While

But if it's working just the way you want, my apologies. I'm not sure if there's a better way of doing it. If I may ask, what is it that you want to do anyway? I'm a bit confused with the number of myReader.Reads().