Better way?

Question

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

Jan Montano · Answer

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