Error when I use SqlDataReader in an another SqlDataReader

Question

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();
}

Please show your connection string (you can mask private parts) you probably did not set up your connection string for multiple connections — Hogan
– Hogan, Commented Aug 8, 2013 at 10:37
Try to use two different connection objects each of data reader — Chirag Vidani
– Chirag Vidani, Commented Aug 8, 2013 at 10:39
@ProgrammersOcean - That is wrong, he needs a change to his connection string — Hogan
– Hogan, Commented Aug 8, 2013 at 11:22

Steve · Accepted Answer · 2013-08-08 10:48:59Z

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();

gzaxx · Accepted Answer · 2013-08-08 10:40:50Z

1

Four ways of fixing is:

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

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.

answered Aug 8, 2013 at 10:40

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Comments

orrollo · Accepted Answer · 2013-08-08 10:37:29Z

0

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.

answered Aug 8, 2013 at 10:37

orrollo

3111 silver badge4 bronze badges

4 Comments

orrollo Over a year ago

here is nothing about used version of ms sql server in the question. MARS is available in 2005 and later. in this case common answer - another connection.

Matías Fidemraizer Over a year ago

Bad argument: we're in year 2013. Common answer: use MARS.

orrollo Over a year ago

yes, more common answer. but mine is more correct - by the text of question. :)

Matías Fidemraizer Over a year ago

Wrong, wrong. By the text of the question you could just change the connection string in the Web.config to allow MARS and that's all. Your answer is valid for .NET 1.x early days ;) But this is StackOverflow, you can provide your own point of view and I suspect that's the reason of your answer having 0 upvotes... you know!

Collectives™ on Stack Overflow

Error when I use SqlDataReader in an another SqlDataReader

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related