C# Invalid Attempt to Call Read When Reader Is Closed

C# Invalid attempt to call Read when reader is closed

It doesn't work because you close the connection before returning the reader. Reader works only when the connection is open:

result = command.ExecuteReader();
connection.Close();

return result; // here the reader is not valid

Generally speaking, you should not be returning a reader to a business layer. Reader should be used only in the data access layer. It should be used and then it and the connection should be closed.

You should rather return an object that can work after the connection is closed, e.g. a DataSet or DataTable or alternatively a collection of DTO's. For example:

public List<Distribution> getDistributionAll()
{
    List<Distribution> distributionAll = new List<Distribution>();

    using (var connection = new SqlConnection(FoodBankDB.GetConnectionString())) // get your connection string from the other class here
    {
        SqlCommand command = new SqlCommand("SELECT b.addressLineOne FROM dbo.Beneficiaries b INNER JOIN dbo.Distributions d ON d.beneficiary = b.id", connection);
        connection.Open();
        using (var dr = command.ExecuteReader())
        {
            while (dr.Read())
            {
                string address = dr["addressLineOne"].ToString();

                distributionAll.Add(new Distribution(address));
            }
        }
    }

    return distributionAll;
}

Invalid attempt to Read when reader is closed

You can load the results of your query to memory, then close the connection and still return an IDataReader that works as expected. Note that this costs memory.

public IDataReader getRecord(string query)
    {
        MySqlDataReader reader;
        using (var connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            using (var cmd = new MySqlCommand(query, connection))
            {

                reader = cmd.ExecuteReader();
                var dt = new DataTable();
                dt.Load( reader );
                return dt.CreateDataReader();
            }
        }

        return null;
    }

In the callers:

String sql = "SELECT * FROM `table`";
var dr = objDB.getRecord(sql); // or DataTableReader dr = ...
if (dr.Read())
{
    // some code goes here
} 

Invalid attempt to call Read when reader is closed?

Don't call Ddldr.Close();, especially inside the while. This way you are doing a first iteration, closing the reader and the second iteration will of course booom as the reader is closed. The using statement will take care of it. Simply remove this line from your code.

So:

using (SqlDataReader Ddldr = DlistCmd.ExecuteReader())
{
    while (Ddldr.Read())
    {
        switch (Ddldr.GetInt32(0))
        {
            ... your cases here
            default:
                break;
        }
    }
}

Also the following lines:

string x = Request.QueryString["SubId"];
string displayQuery = "SELECT CustName, CustAdd, CustCity, CustState, CustZip FROM Customer WHERE SubId =" + x;
string broQuery = "SELECT EntityType FROM Broker WHERE SubId =" + x;
string ddlQuery = "SELECT ProductId FROM SubmissionProducts WHERE SubmissionId =" + x;

stink like a pile of s..t. You should be using parametrized queries and absolutely never write any code like this or your application will be vulnerable to SQL injection. Everytime you use a string concatenation when writing a SQL query an alarm should ring telling you that you are doing it wrong.

So here comes the correct way of doing this:

protected void Page_Load(object sender, EventArgs e)
{
    string x = Request.QueryString["SubId"];
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    using (var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT ProductId FROM SubmissionProducts WHERE SubmissionId = @SubmissionId";
        cmd.Parameters.AddWithValue("@SubmissionId", x)

        using (var reader = cmd.ExecuteReader())
        {
            while (Ddldr.Read())
            {
                switch (reader.GetInt32(reader.GetOrdinal("ProductId")))
                {
                    ... your cases here
                    default:
                        break;
                }
            }

        }
    }
}

Dapper Invalid attempt to call Read when reader is closed

SqlDataReader requires active db connection to go through the records. In your case you are closing the active connection and returning readar object and then trying to retrieving records.
That's why you are getting error. reader is closed.

I suggest two approaches to solve your problems.

1 .Read the data from data readar when its active like

 SqlDataReader dr;

        var ConnecRepository = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
           using (var connection = new SqlConnection(ConnecRepository))
           {
               connection.Open();
               SqlCommand cmd = new SqlCommand();
               cmd.Connection = connection;
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = query;
               dr = cmd.ExecuteReader();
          if (dr.Read()){
                 \\your logic here}
        } 

2. Use adapter to fill the data set and return dataset/data table to the calling program or web page. you can find number of samples how to use dataapter to fill data set.

Hope this helps. let me know if you need further assistance. happy to help!!

Invalid attempt to read when Reader is closed - MySqlDataReader C#

You're disposing the SqlCommand before attempting to use the SqlReader it produced. They'll both need to stay open until you're done with the reader.

---

Related Topics