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

SiliVestige picture SiliVestige · Dec 12, 2012 · Viewed 16.3k times · Source

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
                    } 
    }
        }
    }

Answer

Robert Harvey picture Robert Harvey · Dec 12, 2012

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
}