Troubleshooting – Error: Invalid attempt to call HasRows when reader is closed

Usually this error will be encountered while trying to access data from DataReader.
Problem:

System.InvalidOperationException was unhandled
  Message=”Invalid attempt to call HasRows when reader is closed.”
  Source=”System.Data”
  StackTrace:
       at System.Data.SqlClient.SqlDataReader.get_HasRows()

 
Reason:
Most of the times the reason will be – you use SqlCnnection object in using block – which will close the connection instance after executing the code in it.
For a DataReader to get data, the connection should be OPEN.

using(SqlConnection sqlConn = new SqlConnection(connectionString))
{
sqlConn.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConn;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = “MY_SP”;
sqlCommand.CommandTimeout = 600;
dataReader= sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
}

So, when you try to check HasRows property of the DataReader object  as follows, you will get an error.

if (dataReader !=null && dataReader.HasRows)
{
while (dataReader.Read())
{
// your code
}
dataReader.Close();
}

Solution:
The solution for this is

  • DO NOT use SqlConnection in using block
  • DO NOT close the SqlConnection object. Close it using DataReader object as objDataReader.Close();

Leave a Comment

Your email address will not be published. Required fields are marked *