SqlDataReader error “Invalid attempt to read when no data is present”

Pat Hanks

1y1.4k.NET

My environment used is Visual Studio 2022 v17.12.4. Language is C# and connecting to SQL Server Express v16.0.1135.2. This is a Windows form using .Net Framework 4.8.1.

I am getting the error message “Invalid attempt to read when no data is present” and can't seem to find the cause. I have the following methods that I use in many places without issue. However in this one case it is causing the error. I confirmed in the method GetDataReader the reader has the row I am expecting. However in the method MaximumFieldLengthSql where it is returned to, there are no rows in the Results View and the error is thrown.

Any help pointing me in the right direction is much appreciated.

This is the result view of the reader in the GetDataReader method.

This is the result view of the reader in the MaximumFieldLengthSql method once returned.

Here are the 2 methods. The SQL statement passed to MaximumFieldLengthSql is "SELECT Max(Len(customerName)) AS L FROM Customer".

private static int MaximumFieldLengthSql(string sqlCommand)
{
    int _maxLength = 0;
    try
    {
        using (SqlDataReader reader = MyData.SQLServer.GetDataReader(sqlCommand))
        {
            // BUG - Reader has no rows, even though it does in SQLServer.GetDataReader 
            if ((reader != null) & (reader.HasRows))
            {
                reader.Read();
                _maxLength = reader.GetInt32(0);
                reader.Close();
            }
        }
    }
    catch (Exception ex)
    {
        string errorMessage = $"Error retrieving data from table '{Tables.CustomerTable.TableName}'”;
        Logging.AddException(ex, Logging.EventNumbers.RetrievingData, errorMessage, System.Diagnostics.EventLogEntryType.Error);
    }
    return _maxLength;
}


// Located in the SqlServer class
internal static SqlDataReader GetDataReader(string sqlCommand)
{
    SqlDataReader reader = null;
    try
    {
        if (connection == null) { Server.InitializeConnection(); }
        using (SqlCommand dbCommand = new SqlCommand(sqlCommand))
        {
            dbCommand.Connection = connection;
            reader = dbCommand.ExecuteReader();
        }
    }
    catch (Exception ex)
    {
        reader = null;
        Logging.AddException(ex,
                        Logging.EventNumbers.RetrievingData,
                        "Error executing datareader",
                        EventLogEntryType.Error);
    }
    return reader;
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul1y ago
This flowchart shows the process flow from the MaximumFieldLengthSql method to GetDataReader and the associated checks.
- MaximumFieldLengthSql Method:
  
  Calls GetDataReader to fetch data.
  
  Checks if the reader is not null and has rows.
  
  Reads the data and retrieves the maximum field length.
- GetDataReader Method:
  
  Checks if the database connection is valid.
  
  Initializes the connection if required.
  
  Creates a SqlCommand and executes it to get a SqlDataReader.
- Flow:
  
  If an exception occurs at any point, it’s logged, and a default value is returned.
0

Tuhin Paul1y ago

Do not dispose of the SqlCommand within the GetDataReader method. Return the SqlDataReader while keeping the associated SqlCommand and SqlConnection alive.

internal static SqlDataReader GetDataReader(string sqlCommand)
{
    try
    {
        if (connection == null) { Server.InitializeConnection(); }
        SqlCommand dbCommand = new SqlCommand(sqlCommand, connection);
        return dbCommand.ExecuteReader(CommandBehavior.CloseConnection); // Close connection when reader is done
    }
    catch (Exception ex)
    {
        Logging.AddException(ex,
                        Logging.EventNumbers.RetrievingData,
                        "Error executing datareader",
                        EventLogEntryType.Error);
        return null;
    }
}

Tuhin Paul1y ago
Main Issues are as follows:
1. The using statement in the GetDataReader method disposes of the SqlCommand after its execution, including the associated SqlDataReader. This disposal might cause the SqlDataReader to lose its data or become invalid when accessed in the MaximumFieldLengthSql method.
2. The HasRows property is accessed in the MaximumFieldLengthSql method. If the SqlDataReader becomes invalid or empty due to the using block in GetDataReader, it will report no rows.
3. If Server.InitializeConnection() does not ensure an open and valid connection, the SqlDataReader could fail to read data despite executing a query.
0

Pat Hanks1y ago

I was given an answer by a friend that resolved the issue. All I did is make a small change in the MaximumCaliberLengthSql method as below.

using (SqlDataReader reader = SQLServer.GetDataReader(sqlCommand))
{
    if ((reader != null) && (reader.HasRows))
    {
        reader.Read();
        _maxLength = reader[0] as int? ?? default(int);
        reader.Close();
    }
}