CODEWITHSUNDEEP
c#
Sonu_Orai
Sonu_Orai

Reputation: 397

Error when I use SqlDataReader in an another SqlDataReader

I get an error

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

when I use a SqlDataReader in an another SqlDataReader. I don't know where I made a mistake and how can it be solved? The error appears when the second datareader (dr2) is executed.

{
    cmd = new SqlCommand("select agent,comm,primm from comm where a_id = '" + textBox1.Text + "' AND date >= @date1 AND date <= @date2 ", agr);

    cmd.Parameters.AddWithValue("@date1", dateTimePicker1.Value);
    cmd.Parameters.AddWithValue("@date2", dateTimePicker2.Value);
    dr = cmd.ExecuteReader();

    while (dr.Read())
    {      
        aid = dr[0].ToString().Trim();
        comm = double.Parse(dr[1].ToString());
        busi = double.Parse(dr[2].ToString());

        cmd2 = new SqlCommand("select s_id,lvl from agnt where a_id = @a_id", agr);
        cmd2.Parameters.AddWithValue("@a_id", aid);

        // on this line get executed I get a message 
        // There is already an open DataReader associated with this 
        // Command which must be closed first.
        dr2 = cmd2.ExecuteReader();         

        if(dr2.Read())
        {
            abc = dr2[0].Tostring();
        }                  
        dr2.Close();
    }

    dr.Close();
    cmd.Parameters.Clear();
}

Upvotes: 0

Views: 625

Answers (3)

Steve
Steve

Reputation: 216243

When you open a DataReader, the connection is used exclusively by the DataReader and cannot be used to execute other commands (SqlCommand.ExecuteNonQuery, SqlDataAdapter etc..).
See the remarks section on the MSDN reference on SqlDataReader.
You need to add MultipleActiveResultSets=True to your connection string to overcome this limitation

This article on MSDN explain the details of this problem

As an unrelated note, please, avoid to use a string concatenation to build your command text. You are already using parameters, so why do you switch to string concat for just one value?

cmd = new SqlCommand("select agent,comm,primm from comm " + 
         "where a_id = @id AND date >= @date1 AND date <= @date2 ", agr);

cmd.Parameters.AddWithValue("@id", textBox1.Text);
cmd.Parameters.AddWithValue("@date1", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@date2", dateTimePicker2.Value);
dr = cmd.ExecuteReader();

Upvotes: 3

gzaxx
gzaxx

Reputation: 17590

Four ways of fixing is:

  1. Look at @Steve answer
  2. create new connection for second data reader
  3. store data from first reader and then fill it up with second reader

  4. combine your query:

    select c.agent, c.comm, c.primm, a.s_id, a.lvl
from comm c
left join agnt a on a.a_id = c.agent
where a_id = @a_id AND date >= @date1 AND date <= @date2

this one does not need two readers to retrieve all data from DB.

Upvotes: 1

orrollo
orrollo

Reputation: 311

afaik, the single connection can't be used by two datareaders at the same time. you're need to create connection for each command or use second reader after closing the first one.

Upvotes: 0

Related Questions