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


internal static SqlDataReader GetDataReader(string sqlCommand)
{
 SqlDataReader reader;
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;  
}

Answer 1

Hi, @Pat. Welcome to Microsoft Q&A. 

Cause of error: Query or command timeout (default is 30 seconds)

After the breakpoint is set, if the program stays at the return reader of GetDataReader for too long and then runs to MaximumFieldLengthSql, the phenomenon you mentioned will occur.

When the program runs again, your error will appear.

Invalid attempt to read when no data is present

More explanation on timeouts

Solution: Do not interrupt GetDataReader and MaximumFieldLengthSql to allow the program logic to run smoothly

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you only want one single scalar value, why not use ExecuteScalar instead?

Also, I note the version number: you are many Cumulative Updates behind. You should download and install the most recent Cumulative Update for SQL 20222 to get access to many enhancements and bug fixes. It will not have any effect on your current issue, though.

Answer 3

Is your code shown in your question true? Does the line reader.Read(); really exist?　Please see the following stackoverflow article:

Invalid attempt to read when no data is present

Below is code I tested in my environment. I could reproduce your issue "Invalid attempt to read when no data is present" (InvalidOperationException) only when the line reader.Read(); is deleted. The InvalidOperationException is thrown at reader.GetInt32(0).

using System;
using System.Data.SqlClient;

namespace ConsoleApp9
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var connString = "Data Source=lpc:(local)\\sqlexpress;" +
                             "Initial Catalog=NORTHWIND;" +
                             "Integrated Security=True;";
            var selectQuery = "SELECT Max(Len(ProductName)) AS L FROM Products";
            int _maxLength = 0;
            using (var connection = new SqlConnection(connString))
            {
                connection.Open();

                using (var reader = CreateDataReader(connection, selectQuery))
                {
                    reader.Read();
                    _maxLength = reader.GetInt32(0);
                }
            }

            Console.WriteLine(_maxLength);
        }

        static SqlDataReader CreateDataReader(SqlConnection connection, string query)
        {
            SqlDataReader reader;
            using (var command = new SqlCommand(query))
            {
                command.Connection = connection;
                reader = command.ExecuteReader();                
            }
            return reader;
        }
    }
}

Answer 4

Thanks everyone for the quick suggestions. I found an unrelated post on another site that I tried to leverage and it did resolve the problem. I made the change below. Please let me know if this is incorrect and I should do something else.

Old line: _maxLength = reader.GetInt32(0);

New line: _maxLength = reader[0] as int? ?? default(int);

Answer 5

I would first remove code that separates various components and write all code in a single method, as shown below.

Note Code assumes that there are records with in this case CompanyName not null.

public class Demo
{
    /// <summary>
    /// Generally read from appsettings.json
    /// </summary>
    private static readonly string _connectionString =
        """
        Data Source=.\SQLEXPRESS;
        Initial Catalog=NorthWind2024;
        Integrated Security=True;Encrypt=False
        """;

    public static async Task<int> GetCount()
    {
        try
        {
            await using SqlConnection cn = new(_connectionString);
            await using SqlCommand cmd = new()
            {
                Connection = cn,
                CommandText =
                    """
                    SELECT Max(Len(CompanyName))
                    FROM NorthWind2024.dbo.Customers
                    """
            };

            await cn.OpenAsync();

            var result = await cmd.ExecuteScalarAsync();
            return result != null ? Convert.ToInt32(result) : 0;
        }
        catch (Exception ex)
        {
            // uses SeriLog
            Log.Error(ex, "Error occurred while getting the count.");
            return -1;
        }
    }
}

Also, consider using NuGet package Dapper, much less code.

public static async Task<int> GetCountDapper()
{
    try
    {
        const string query =
            """
            SELECT Max(Len(CompanyName))
            FROM NorthWind2024.dbo.Customers
            """;

        await using SqlConnection cn = new(_connectionString);

        var result = await cn.ExecuteScalarAsync<int?>(query);

        return result ?? 0;
    }
    catch (Exception ex)
    {
        // uses SeriLog
        Log.Error(ex, "Error occurred while getting the count.");
        return -1;
    }
}