Reader.Read() fails to read rows even though it has rows
To future readers of this question: note that the problem occurred because the OP was returning too many columns in the query. See the comments below this answer.
I'm not quite sure why this is happening, but you really only need to check for rows once, not twice, and the Read()
method already does this.
So all you really need is
while (reader.Read())
{
// Do your thing
}
Related videos on Youtube
SiliVestige
Updated on June 04, 2022Comments
-
SiliVestige almost 2 years
I have a problem where it appears that the reader indicates that it has rows from the returned SQL but the while loop for the reader never runs. I put a messagebox in the reader.hasrows as a verification and I put a messagebox on the first line after the while loop as well. The messagebox for the hasrows is executed but the messagebox for the Read is not executed. It is very puzzling. I tried the query against the database and it indeed does return rows. Here is the code snippet.
using (DbConnection connection = CADBase.DbProviderFactory.CreateConnection()) { connection.ConnectionString = CADBase.DbConnectionString; connection.Open(); using (DbCommand command = connection.CreateCommand()) { SQL = <statement here>; command.CommandText = SQL using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { //NEVER MAKES IT HERE } } } }
-
Yinda Yin over 11 yearsThat was already made clear in a comment below the question (now deleted). If you have an actual answer, you should post it.
-
Tim Schmelter over 11 years@RobertHarvey: Added a possible(?) reason.
-
Yinda Yin over 11 yearsI think the OP would have mentioned that. Then again, the code doesn't compile, so.
-
Tim Schmelter over 11 yearsBut if there is more code than
//MAKES IT HERE
around the loop you'll need an additionalbool
variable that you set totrue
when you are in thiswhile
. To avoid that the propertyHasRows
is useful. -
SiliVestige over 11 yearsThe code compiles. It requires some custom libraries from a vendor. There is nothing in between the makes it here and while statement.
-
SiliVestige over 11 yearsThere is no code between makes it here and the while statement.
-
Tim Schmelter over 11 years"It requires some custom libraries from a vendor" What does that mean?
reader.HasRows()
cannot compile since that's a property not a method. -
Yinda Yin over 11 years@SiliVestige: When I don't know what's wrong, I fix what doesn't look right. You don't need to check twice for rows. Try this; if it still doesn't work, I'll buy you a beer.
-
SiliVestige over 11 yearsI agree but it is perplexing. Why would it return that it has rows but never read the rows? When I take hasrows out I get the same result. Is there something I can do during the executereader to just grab a value to see if it has one?
-
Yinda Yin over 11 yearsAll of the code examples I am seeing in Google all use this form, and don't bother with the HasRows.
-
SiliVestige over 11 yearsI updated the post with removing hasrows, compiled, and reran the code. No rows are appearing which means there are no rows to be returned. I then took the SQL statement, went to SQLplus targetting the same database and I got a result. Is there a size problem on reader? I'm pulling in 64 columns (don't ask me, not my database architecture).
-
Yinda Yin over 11 yearsI guess the only way to find out is to hand the reader some rows that are smaller.
-
SiliVestige over 11 yearsWhelp. It appears that there is a limit to the number of columns that can be read. I dropped the exact query down to one column and it works fine. I can't find any documentation on the non-wrapped dbreader to see if there is a limit but this wrapped dbreader obviously does. Thanks for the investigation tips.