Reader.Read() fails to read rows even though it has rows

16,466

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
} 
Share:
16,466

Related videos on Youtube

SiliVestige
Author by

SiliVestige

Updated on June 04, 2022

Comments

  • SiliVestige
    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
    Yinda Yin over 11 years
    That was already made clear in a comment below the question (now deleted). If you have an actual answer, you should post it.
  • Tim Schmelter
    Tim Schmelter over 11 years
    @RobertHarvey: Added a possible(?) reason.
  • Yinda Yin
    Yinda Yin over 11 years
    I think the OP would have mentioned that. Then again, the code doesn't compile, so.
  • Tim Schmelter
    Tim Schmelter over 11 years
    But if there is more code than //MAKES IT HERE around the loop you'll need an additional bool variable that you set to true when you are in this while. To avoid that the property HasRows is useful.
  • SiliVestige
    SiliVestige over 11 years
    The code compiles. It requires some custom libraries from a vendor. There is nothing in between the makes it here and while statement.
  • SiliVestige
    SiliVestige over 11 years
    There is no code between makes it here and the while statement.
  • Tim Schmelter
    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
    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
    SiliVestige over 11 years
    I 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
    Yinda Yin over 11 years
    All of the code examples I am seeing in Google all use this form, and don't bother with the HasRows.
  • SiliVestige
    SiliVestige over 11 years
    I 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
    Yinda Yin over 11 years
    I guess the only way to find out is to hand the reader some rows that are smaller.
  • SiliVestige
    SiliVestige over 11 years
    Whelp. 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.