There is already an open DataReader associated with this Command which must be closed first

Question

I've got a question. Could you tell me what could be wrong in this code? So case looks like this: program shows me this error but not every time.Sometimes it does show it some times it doesn't.

Error:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: There is already an open DataReader associated with this Command which must be closed first.

Code:

 private void loadlist() 
    {
        ItemList.Items.Clear();
        NumberList.Items.Clear();

        newConnection.connection();
        cmd.CommandText = "select * from Items";
        dr = cmd.ExecuteReader();
        
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                ItemList.Items.Add(dr[0].ToString());
                NumberList.Items.Add(dr[1].ToString());
            }
        }
        dr.Close();
        newConnection.cnOff();
        
    }

I can delete "dr.Close()" and it worked and after i try run and i must edit some code(write back "dr.Close()" ) and it worked back. Btw. sorry for my english :<

Answer 1

Use a local SqlDataReader instance for your method. Also, If you use a using block, It will take care of calling the Dispose method and cleaning up the memory.

using(SqlDataReader dr= cmd.ExecuteReader())
{
     while (dr.Read())
     {
       //read from reader now
        ItemList.Items.Add(dr[0].ToString());
     }
}

You may use the using block on your connection and command objects as well.

var connStr = "ReplaceYourConnectionStringHere"
using (var c = new SqlConnection(connStr))
{
    using (var cmd = new SqlCommand("SELECT * FROM ITEMS",c))
    {
        c.Open();
        using (var dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                //read from reader now
                //ItemList.Items.Add(dr[0].ToString());
            }
        }
    }
  //No need to explicitly close connection :) Thanks to "using"
}

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object