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

Question

public void StockUpdate()
{
    cmd5 = new SqlCommand("select * from SupplierBillSelection where purordentryid=" + txtPurEntryID.Text + "", con);
    var dr1 = cmd5.ExecuteReader();

    if (dr1.HasRows)
    {
        while (dr1.Read())
        {
             cmd2 = new SqlCommand("select * from Stock where ItemName='" + dr1[2].ToString() + "'", con);
             dr5 = cmd2.ExecuteReader();
            if (dr1.HasRows)
            {
                if (dr5.HasRows)
                {

                    dr5.Read();
                    string insert = "Update Stock set Quantity=" + (Convert.ToSingle(dr5[13]) + Convert.ToSingle(dr1[15])) + " ,TotalPrice=" + (Convert.ToSingle(dr5[14])+Convert.ToSingle(dr1[16]))+ " where ItemName='" + dr1[1].ToString() + "'and CompanyName='" + dr1[2].ToString() +"'";
                    cmd3 = new SqlCommand(insert, con);
                    Console.WriteLine(insert);

What is the error you get? Can you include it in your question? — elif, Dec 22 '15 at 10:29
wrap your dr1 and dr5 in a using declaration so it gets disposed properly! And think about better names for your variables please. — Marco Forberg, Dec 22 '15 at 10:35
You can not open second data reader to database unless first one is closed. — Hemal, Dec 22 '15 at 10:38
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — Marco Forberg, Dec 22 '15 at 10:38

score 1 · Answer 1 · edited May 23 '17 at 12:30

The reason you're getting this exception is because you end up having multiple resultsets over a single connection. In other words, you open up second SqlDataReader while the first one is still active.

To fix this you will either have to rewrite your query to use joins or, if available, enable MARS. I'm more than sure, though, that it is quite possible to rewrite this entire code block as a single UPDATE FROM SELECT.

Plus, you have the usual set slew of problems with not disposing of disposable objects properly, using select *, concatenating SQL statements and trusting user input.

score 0 · Answer 2 · answered Dec 22 '15 at 10:38

0

I think you didn't post all the code, but in general if you call ExecuteReader like you do, you have to call Close() on the reader. The only exception is if you use Using-Blocks. The Using-Block calls dispose internally and auto-closes the reader.

answered Dec 22 '15 at 10:38

Sascha

1,210
1
17
33

score 0 · Accepted Answer · answered Dec 22 '15 at 10:49

0

Add This "MultipleActiveResultSets=True;" In Connection String.

answered Dec 22 '15 at 10:49

Sangram Kakade

16

score 0 · Answer 4 · answered Dec 22 '15 at 10:56

0

You should work with your DataReaders this way:

using(DataReader dr1 = cmd5.ExecuteReader(CommandBehavior.CloseConnection)
{
    //do stuff here
}

answered Dec 22 '15 at 10:56

Marco Forberg

2,634
5
22
33

score 0 · Answer 5 · answered Dec 22 '15 at 10:59

Sangram Kakade's answer should allow you to run your code, but I advice against coding your queries like this. Each row in the first query will generate an extra insert or update in the second reader and you will flood the server.

Consider using MERGE, as it does the job much faster and atomically (all inserts and updates are executed by default in an implicit transaction).

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

5 Answers5