invalid attempt to call read when READER IS CLOSED

Amol Panchal 1 Reputation point
2021-10-03T08:20:41.173+00:00

if (conn.State == ConnectionState.Closed) conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

        cmd.CommandText = "select customer.cust_name,customer.cust_id,purchase_details.purchase_id,purchase_details.pdate,purchase_details.subtotal,purchase_details.total,purchase_details.supp_bill_id from Purchase_details inner join customer on purchase_details.supplier_id=customer.cust_id where id =" + pd;

        SqlDataReader r1 = cmd.ExecuteReader();
        while (r1.Read())
        {

            cbsupplier.Text = r1["cust_name"].ToString();
            txtdate.Value = Convert.ToDateTime(r1["pdate"]);
            txtsubtotal.Text = r1["subtotal"].ToString();
            custid.Text = r1["cust_id"].ToString();


        }
        r1.Close();
        conn.Close();
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,628 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Karen Payne MVP 35,386 Reputation points
    2021-10-03T10:24:57.767+00:00

    You should create a connection as needed rather than what you are currently doing. I recommended using the following pattern.

    public static void Example()
    {
        using (var cn = new SqlConnection { ConnectionString = "" })
        {
            using (var cmd = new SqlCommand() { Connection = cn, CommandText = "" })
            {
                cn.Open();
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
    
                }
            }
        }
    

  2. Karen Payne MVP 35,386 Reputation points
    2021-10-03T13:07:10.617+00:00

    If you are truly not open to change this code will be another option and is not what I would ever use but wrote it for a Microsoft article to what not to do.

    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace SqlServerLibrary.Classes
    {
        public sealed class SqlServerConnections
        {
            private static readonly Lazy<SqlServerConnections>
                Lazy = new(() => new SqlServerConnections());
    
            public static SqlServerConnections Instance => Lazy.Value;
            private readonly Hashtable _connections = new ();
    
            /// <summary>
            /// Normally comes from appsetting.json or appConfig
            /// </summary>
            public string ConnectionString 
                => "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWind2020;Integrated Security=True";
    
            /// <summary>
            /// Reset an existing connection string
            /// </summary>
            /// <param name="connectionString">An existing connection string</param>
            public void Reset(string connectionString)
            {
                SqlConnection connection = null;
    
                try
                {
                    connection = (SqlConnection)_connections[connectionString];
                    connection.Dispose();
                    connection = null;
                }
                catch (Exception)
                {
                    // ignored
                }
            }
    
            /// <summary>
            /// Used to reset all known connections to null
            /// </summary>
            public void ResetAll()
            {
                foreach (var cn in _connections)
                {
                    SqlConnection connection = null;
    
                    try
                    {
                        connection = (SqlConnection)cn;
                        connection.Dispose();
                        connection = null;
                    }
                    catch (Exception)
                    {
                        // ignored
                    }
                }
            }
            /// <summary>
            /// Returns an open connection for connection string
            /// </summary>
            /// <param name="connectionString">Valid connection string</param>
            /// <returns>Connection</returns>
            public SqlConnection Connection(string connectionString)
            {
                SqlConnection connection = null;
                var bNeedAdd = false;
    
                try
                {
                    connection = (SqlConnection)_connections[connectionString];
                }
                catch (Exception)
                {
                    // ignored
                }
    
                if (connection == null)
                {
                    bNeedAdd = true;
                }
    
                if (connection == null || connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
                {
                    try
                    {
                        connection?.Dispose();
                        connection = null;
                    }
                    catch (Exception)
                    {
                        // ignored
                    }
    
                    connection = new SqlConnection();
                }
    
                if (connection.State == ConnectionState.Closed)
                {
                    connection.ConnectionString = connectionString;
                    connection.Open();
                }
    
                if (bNeedAdd)
                {
                    _connections.Add(connectionString, connection);
                }
    
                return connection;
            }
        }
    }
    

    Usage in backend

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using static SqlServerLibrary.Classes.SqlServerConnections;
    
    namespace SqlServerLibrary.Classes
    {
        public class DataOperations
        {
            public static (Exception exception, List<string> nameList) ReadCategoryNames()
            {
                List<string> names = new ();
    
                using var cn = Instance.Connection(Instance.ConnectionString);
    
                const string selectStatement = "SELECT CategoryName FROM dbo.Categories";
    
                using var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement };
    
                try
                {
                    cn.Open();
    
                    var reader = cmd.ExecuteReader();
    
                    while (reader.Read())
                    {
                        names.Add(reader.GetString(0));
                    }
    
                    return (null, names);
    
                }
                catch (Exception e)
                {
                    return (e, null);
                }
    
            }
        }
    
    }
    

    Calling the above

    public Demo()
    {
        var (exception, nameList) = DataOperations.ReadCategoryNames();
        if (exception is null)
        {
            // use list
        }
        else
        {
            // report issue
            // exception.Message
        }
    
    }
    
    0 comments No comments

  3. Sam of Simple Samples 5,526 Reputation points
    2021-10-03T21:01:34.11+00:00

    I put your code into a console program and adapted it to use the sample ContosoUniversity/ContosoUniversity.Database database. The following code works for me.

    SqlConnection conn = new SqlConnection(ConnectionString);
    if (conn.State == ConnectionState.Closed) conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT CourseID, Title, Credits FROM Courses";
    SqlDataReader r1 = cmd.ExecuteReader();
    while (r1.Read())
    {
        Console.WriteLine($"{r1["CourseID"].ToString()} {r1["Title"].ToString()} {r1["Credits"].ToString()}");
    }
    r1.Close();
    conn.Close();
    

    I suspect that something is happening in code that you do not show here. You can do something (for diagnostic purposes at least) to look at the ConnectionState; it is probably not Closed but the connection is not open. I am not sure it would work but perhaps you just need to use conn.State != ConnectionState.Open instead of conn.State == ConnectionState.Closed. You can also add a check that ensures it is open before proceeding.

    0 comments No comments