How To Count how many ROWS are returned in SQL Data Reader

My code keeps reading for another row when none are present....

There's are only unique records in the dB so far, but dupes are possible

While reader.has rows

reader.read

if value not db.null

get values

end if

end while

The program gets the row and reads it. After processing the values, it goes to thru the While and reads the next row (none), it then crashes on if value not db.null... because the value is null or no record. The dB has Values Required option on - no empty fields allowed.

Is there a way to count the ROWS returned?

Thanks.

That depends on your coding language, which one are you using ? For example the .NET Framework has the read() method which returns false if no record can be read anymore, leading to the following code:

while dr.Read()
{}

and to the fact that you won′t get an error if no rows is read anymore.

In my good old ASP ages there was something similar like rs.EOF or something, I guess there has to be be something in your coding language too.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I'm using VB.

But I'm using the WHILE there are ROWS, READ, so it shouldn't read if no more rows are present. Only ONE ROW is returned, so the WHILE statement should return FALSE on the second run of the loop and exit the LOOP because no more ROWS are present.

Moverover, it continued to READ, so perhaps the framework (NET) detected another row (empty) and reads it (while rows are present, read). Howbeit, this ROW is empty so an error is generated at the if not null.db line - but even so, it should NOT generate an error because NOT db.Null should detect that there is a Null Value and return FALSE and exit the loop.