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

Question

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

Sharp GPT · Answer

The error message Invalid attempt to read when no data is present typically occurs when you try to read data from a SqlDataReader when no data is available to be read. In your case the error arises in the `MaximumFieldLengthSql` method. The issue seems to be that the reader does not contain any rows even though it was expected. Here are a few points to consider when troubleshooting this error: 1. Check Data Availability : Ensure that the SQL query you are executing (`SELECT Max(Len(customerName)) AS L FROM Customer`) actually returns data. You can test this directly in your SQL environment to confirm data availability. 2. Verify SQL Query : Double-check the SQL query itself to ensure it is correctly formulated and targeting the intended table and column. 3. Error Handling : It looks like you have a try-catch block in place. Make sure to log any exceptions that may occur during SQL query execution to gather more insights into what might be going wrong. 4. Data Connection : Confirm that the data connection (`connection` in your code) is properly established and valid before executing the query. 5. Debugging : You mentioned that the reader has rows in the `GetDataReader` method but not in `MaximumFieldLengthSql` method. You can use breakpoints and step through the code to see where the discrepancy occurs. 6. Logging : Ensure that logging is implemented effectively to capture any potential errors or exceptions that might be happening during the data retrieval process. In your specific case it might be beneficial to focus on the data returned by the SQL query and its journey from `GetDataReader` to `MaximumFieldLengthSql` method. By thoroughly investigating these aspects and considering the points mentioned above you should be able to pinpoint the exact cause of the error. If you have further questions or need clarification on any specific aspect feel free to ask!