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

Question

0.00/5 (No votes)

See more:

C#

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["database_con"].ConnectionString);
       con.Open();
       SqlTransaction trans = con.BeginTransaction();
        try
       {
           SqlCommand cmd = new SqlCommand("select userid,amount from user_detail", con, trans);
           SqlDataReader dr = cmd.ExecuteReader();
           while (dr.Read())
           {
               SqlCommand cmd6 = new SqlCommand("insert into payout(user_id,amount)values('" + dr[0].ToString() + "','" + dr[0].ToString() + "')", con, trans);
               cmd6.ExecuteNonQuery();
           }
       }

i am using above code which is giving following error in cmd6.ExecuteNonQuery():
There is already an open DataReader associated with this Command which must be closed first.

i know the reason of error is datareader is not closed.. But i can't close datareader because i am fetching records from datareader. If i close it then while loop will not work.. And i also cant create new connection for second command because of transactions...

Suggest me the way of solve this problem...

Posted 24-Feb-14 19:43pm

Harpreet_125

Updated 24-Feb-14 19:44pm

v2

Add a Solution

5 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Answer 1 · 2014-02-24T20:04:00

C#

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["database_con"].ConnectionString);
        con.Open();
        SqlTransaction trans = con.BeginTransaction();
         try
        {
            SqlCommand cmd = new SqlCommand("select userid,amount from user_detail", con, trans);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                SqlCommand cmd6 = new SqlCommand("insert into payout(user_id,amount)values('" + dr[0].ToString() + "','" + dr[0].ToString() + "')", con, trans);
                cmd6.ExecuteNonQuery();
            }
        }

catch (exception ex)
{

}
finally
{
dr.close();
con.close();

}

Tom Marvolo Riddle · Answer 2 · 2014-02-24T19:52:00

Solution 1

Try this:

C#

while (dr.Read())
          {
               string user_id = (dr[0].ToString());
              int amount =int.Parse(dr[1].ToString());
                 dr.Close();
              SqlCommand cmd6 = new SqlCommand("insert into payout(user_id,amount)values('" + user + "','" + amount  + "')", con, trans);
              cmd6.ExecuteNonQuery();
          }

Posted 24-Feb-14 19:52pm

Tom Marvolo Riddle

Updated 24-Feb-14 20:06pm

v2

Comments

Harpreet_125 25-Feb-14 1:55am

if i do like this then loop will not work for the second time.. it works fine only for the first time with this..

Harpreet_125 25-Feb-14 1:55am

Am i right???

Tom Marvolo Riddle 25-Feb-14 1:56am

yes you're right

Harpreet_125 25-Feb-14 1:56am

even it will not insert record for the single time because datareader is used in the second command..

bbirajdar 25-Feb-14 1:57am

You are closing the connection even before you call the ExecuteNonQuery() function -

while (dr.Read())
{
dr.Close();

Tom Marvolo Riddle 25-Feb-14 2:00am

First the data should assign to some string.then it must be close before executing another query.

Harpreet_125 25-Feb-14 2:06am

let i do something like this..
while
{
string user_id = dr[0].Tosring();
dr.close();

}

But then the loop will not work for the second time.. Hope you understand..

Tom Marvolo Riddle 25-Feb-14 2:09am

Get the result in the array and save it using the loop.

King Fisher 25-Feb-14 4:54am

yes.it won't read second time ,dr cant hold the bulk row on it.use dataset,reader can hold only first Row.u got it

bbirajdar · Answer 3 · 2014-02-24T19:54:00

Solution 2

You need to close the connection. I have corrected your code below

SQL

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["database_con"].ConnectionString);
        con.Open();
        SqlTransaction trans = con.BeginTransaction();
         try
        {
            SqlCommand cmd = new SqlCommand("select userid,amount from user_detail", con, trans);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                SqlCommand cmd6 = new SqlCommand("insert into payout(user_id,amount)values('" + dr[0].ToString() + "','" + dr[0].ToString() + "')", con, trans);
                cmd6.ExecuteNonQuery();
            }
        }
catch()
{

}
finally()
{
con.Close();--This is required
}

Posted 24-Feb-14 19:54pm

bbirajdar

Updated 24-Feb-14 19:55pm

v2

Comments

Harpreet_125 25-Feb-14 2:01am

i dont think your code can solve my problem..

bbirajdar 25-Feb-14 2:03am

okay

Tom Marvolo Riddle 25-Feb-14 2:07am

Hi aspnet-i,OP should change the logic.Anyway i update the answer.Please see it.If i'm wrong please let me know

bbirajdar 25-Feb-14 2:11am

I checked your solution. Actually he gets the error when he runs the code for the second time. Because he has not closed the connection in his previous execution of this method. It does not matter how many records he fetches. He can open data reader once and close it after reading the records. Then he should close the connection as well. Solution 4 by KingFisher is correct. Please check.

Tom Marvolo Riddle 25-Feb-14 2:25am

I checked it.As far as i know, we cannot execute insert command when the datareader is open.In this answer the datareader is still open.How can we execute another query?
out of curiosity i'm asking this.I tried at my end. if you know then please explain.

Tom Marvolo Riddle 25-Feb-14 2:45am

when the datareader is still reading the data how can we execute another query in the same connection without closing the datareader?

bbirajdar 25-Feb-14 3:47am

You have got the point... I agree.. Let me think over..

bbirajdar 25-Feb-14 3:50am

Better way.. He shouls rewrite his code using "SELECT INTO table1 ......".. That fixes his problem. And you are right. The OP is following a very naive approach.. He is pulling records from one table and inserting into another. No need to have two queries for it. SELECT INTO will suffice

Tom Marvolo Riddle 25-Feb-14 3:57am

just want to make sure that my understanding regarding this is correct or not.That's why i'm asking this.Thanks a lot for the kind reply

bbirajdar 25-Feb-14 4:19am

Yes.. I agree.. You were absolutely right.. It was my mistake I overlooked :) .. Balancing between work and fun on CP ... :)

Tom Marvolo Riddle 25-Feb-14 4:27am

:)

Peter Leow · Answer 4 · 2014-02-24T20:04:00

Solution 3

Check this out: Another Way Out.[^]

Posted 24-Feb-14 20:04pm

Peter Leow

King Fisher · Answer 5 · 2014-02-24T22:46:00

C#

cmd = new SqlCommand("select userid,amount from user_detail", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            for (int  i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string a, b = "";
                a = ds.Tables[0].Rows[i]["userid"].ToString();
                b = ds.Tables[0].Rows[i]["amt"].ToString();

               
                SqlCommand cmd6 = new SqlCommand("insert into payout(user_id,amount)values('" + a.ToString() + "','" + b.ToString() + "')", con);
                cmd6.ExecuteNonQuery();
            }

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

5 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

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

5 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us