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

Question

I have below code and I am getting exception

"There is already an open DataReader associated with this Connection which must be closed first".

I am using Microsoft Visual C# 2010 Express and Microsoft Access 2007 for this project.

namespace Database1
{
    public partial class Form1 : Form
    {
        OleDbConnection connection;
        public void connect()
        {
            connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=|DataDirectory|\PBName1.accdb;Data Source=C:\Users\bvino_000\Downloads\PBName1.accdb");
            connection.Open();
        }
        public void close_connection()
        {
            connection.Close();
        }

    public Form1()
    {

        InitializeComponent();
       connect();

        OleDbDataReader reader = null;
        OleDbCommand command = new OleDbCommand("SELECT * from  PBInfo", connection);
        reader = command.ExecuteReader();


        while (reader.Read())
        {
            listBox1.Items.Add(reader[1].ToString());
        }
        close_connection();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        listBox2.Items.Add(listBox1.SelectedItem);
        string s = "";
        s = listBox1.SelectedItem.ToString();
        connect();
        string sql = "SELECT PBSize FROM PBInfo where PBName=" + " '" + s + "' ";

        try
        {
            OleDbDataReader reader = null;
            OleDbCommand command = new OleDbCommand(sql, connection);
            reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                command.ExecuteReader();
            }

            reader.Close();
            command.Dispose();
            close_connection();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            label2.Text = command.ExecuteReader().ToString();
            listBox1.Items.Remove(listBox1.SelectedItem);
        }
        catch(Exception ex)
        {
            ex.GetBaseException();
        }
        finally
        {
            close_connection();
        }          
    }

} }

check this http://stackoverflow.com/questions/5440168/c-sharp-mysql-there-is-already-an-open-datareader-associated-with-this-connectio — Deepu Madhusoodanan, Jun 24 '13 at 06:06
You call ExecuteReader two times in the Button_Click event. And the next `label2.Text = command.ExecuteReader().ToString()` makes no sense — Steve, Jun 30 '13 at 20:06

score 4 · Answer 1 · answered Jun 24 '13 at 06:06

4

The reader in the Form's constructor is not closed. You should consider working with the using construct to avoid this:

using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
   ... 
}

answered Jun 24 '13 at 06:06

alzaimar

4,572
1
16
30

Thankz for reply, but actually i am new to this. Where should i use this "using construct"?? – Lany Jun 24 '13 at 06:29
replace the declaration and assignment of the reader with the `using` construct and enclose the reading part (i.e. the loop) with braces so that the `using` block is active for that part. Also consider reading about `using` on MSDN online. BTW: I am quite new as well. – alzaimar Jun 24 '13 at 06:31
Sir, i have tried a lot but um getting no way near in getting the error removed. – Lany Jun 24 '13 at 06:48

score 0 · Answer 2 · answered Jun 24 '13 at 06:05

You have forgotten to close reader on form contructor. When you execute button1_Click a reader is allready open

 public Form1()
{

    InitializeComponent();
   connect();

    OleDbDataReader reader = null;
    OleDbCommand command = new OleDbCommand("SELECT * from  PBInfo", connection);
    reader = command.ExecuteReader();


    while (reader.Read())
    {
        listBox1.Items.Add(reader[1].ToString());
    }
      **reader.Close();
      command.Dispose();**
    //close_connection();
}

Soner Gönül · Answer 3 · 2013-06-24T06:15:44.343

0

From Retrieving Data Using a DataReader

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

Either you should close the current OleDbDataReader or define different OleDbConnection for each OleDbDataReader.

edited Jun 24 '13 at 06:15

answered Jun 24 '13 at 06:07

Soner Gönül

97,193
102
206
364

Sir, i have tried but um not getting that. Plzz help with the code. – Lany Jun 24 '13 at 07:14

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

3 Answers3