Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
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
Updated 24-Feb-14 19:44pm
v2

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

}
 
Share this answer
 
Comments
bbirajdar 25-Feb-14 2:07am    
I agree.. This is the correct answer.. I missed 'dr.close();' in my solution +5
King Fisher 25-Feb-14 2:17am    
:)
Harpreet_125 25-Feb-14 2:20am    
you can try this code... you will not be allow to use datareader in finally block...
bbirajdar 25-Feb-14 2:22am    
Use it before try block ends.... Concentrate on logic.. Syntax is secondary
Harpreet_125 25-Feb-14 2:23am    
how can i use finally block before try block..??
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();
          }
 
Share this answer
 
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();
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
}
 
Share this answer
 
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.
Check this out: Another Way Out.[^]
 
Share this answer
 
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();
            }
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900