My code goes like this

SqlDataReader read=command1.ExecuteReader();
while(reader.Read())
{
    // based on each data read from a table1 I want to run an update query on table1 itself
    SqlCommand command2= new SqlCommand();//command with update query and connection is the same as command1

    command2.ExecuteNonQuery();
}//end of while

The error occurs at command2.ExecuteNonQuery().

Is there a way to go around this problem?

I am using the same connection for both commands.

Edit,full code is given below. I read data from shoporder table to a data sqlreader object. Then ago through each record and update LaborCost colmn using update query.

private void button1_Click(object sender, EventArgs e)
{
    string ConnectionString=@"Data Source=DESKTOP-KM9K7OP\SQLEXPRESS;Initial Catalog=TheSinkSQLVersion;Integrated Security=True";

    SqlConnection connection1 = new SqlConnection();
    SqlConnection connection2= new SqlConnection();// As suggested by CAIUS JARD
    connection2.ConnectionString = ConnectionString;
    connection1.ConnectionString = ConnectionString;
    connection1.Open();
    connection2.Open();
    SqlCommand command1 = new SqlCommand();
    SqlCommand command2;
    string SqlString = "Select ShopOrderNo, LaborCost, TireRepairCost From shoporder Where TireRepairCost > '0'";
    command1.Connection = connection1;
    command1.CommandText = SqlString;
            
    using (SqlDataReader reader = command1.ExecuteReader())
    {
        while (reader.Read())
        {
            command2 = new SqlCommand();
            command2.Connection = connection2;
            decimal newLaborCost = (decimal)reader["LaborCost"]   (decimal)reader["TireRepairCost"];
            decimal tireRepairCost = 0.0m;//set tire repair cost to zero

            string sqlString2 = "Update shoporder Set LaborCost= @LaborCost, TireRepairCost = @TireRepairCost Where ShopOrderNo ='"   reader["ShopOrderNo"]   "'";
            command2.CommandText = sqlString2;
            SqlParameter param = new SqlParameter("@LaborCost", newLaborCost); command2.Parameters.Add(param);
            param = new SqlParameter("@TireRepairCost", tireRepairCost); command2.Parameters.Add(param);
            command2.ExecuteNonQuery();
            command2.Dispose();
        }//End While
        reader.Close();
    }//end using reader
                
    connection1.Close();
    connection2.Close();            
} //end btnClick
   

CodePudding user response：

As coded, the entire operation can be collapsed to just executing an SQL of:

UPDATE shoporder 
SET LaborCost = LaborCost   TireRepairCost, TireRepairCost = 0 
WHERE TireRepairCost > 0

Minimally that would look like:

private void button1_Click(object sender, EventArgs e)
{
  using var c = new SqlCommand("UPDATE shoporder SET LaborCost = LaborCost   TireRepairCost, TireRepairCost = 0 WHERE TireRepairCost > 0", ConnectionString);
  c.Connection.Open();
  c.ExecuteNonQuery();
}

Footnote

If you're going to be doing a lot of database work like this, switching to using Dapper will help you out. Dapper code would be:

private async void button1_Click(object sender, EventArgs e)
{
  using var c = new SqlConnection(ConnectionString);
  await c.ExecuteAsync("UPDATE shoporder SET LaborCost = LaborCost   TireRepairCost, TireRepairCost = 0 WHERE TireRepairCost > 0");
}

..but the real magic of Dapper is where you want to run DB queries and get objects:

using var c = new SqlConnection(ConnectionString);
var orders = await c.QueryAsync<ShopOrder>("SELECT * FROM shopOrders WHERE TireRepairCost > @cost", new{ cost = 0 }).ToList();

Just those two lines would give you a list of ShopOrder collection objects to do stuff with. Doing it "the long way" would be more like:

using var c = new SqlCommand("SELECT * FROM shopOrders WHERE TireRepairCost > @cost", ConnectionString);
c.Parameters.AddWithValue("@cost", 0); //btw, google 'addwithvalue is evil'
c.Connection.Open();
var r = c.ExecuteReader();
var orders = new List<ShopOrder>();
while(r.Read()){
  orders.Add(new ShopOrder {
    Id = r.GetInteger("Id"),
    LaborCost = r.GetInteger("LaborCost"),
    TireCost = r.GetInteger("TireCost"),
  });
}

Painful..