title: ExecuteNonQuery requires the command to have a transaction
body: <p>I am receiving this error message when i try to execute the following code.</p>
  
  <pre><code>ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction
  </code></pre>
  
  <p>Can anyone advice where the problem is? I guess the root of the problem is the part where i try to execute a stored procedure.</p>
  
  <p>The stored procedure is creates its own transaction when execute</p>
  
  <pre><code> using (SqlConnection conn = new SqlConnection(connStr))
              {
                  conn.Open();
  
                  SqlCommand command = conn.CreateCommand();
                  SqlTransaction transaction;
  
                  // Start a local transaction.
                  transaction = conn.BeginTransaction("createOrder");
  
                  // Must assign both transaction object and connection
                  // to Command object for a pending local transaction
                  command.Connection = conn;
                  command.Transaction = transaction;
  
                  try
                  {
                      command.CommandText = "INSERT INTO rand_resupply_order (study_id, centre_id, date_created, created_by) " +
                          "VALUES (@study_id, @centre_id, @date_created, @created_by) SET @order_id = SCOPE_IDENTITY()";
  
                      command.Parameters.Add("@study_id", SqlDbType.Int).Value = study_id;
                      command.Parameters.Add("@centre_id", SqlDbType.Int).Value = centre_id;
                      command.Parameters.Add("@date_created", SqlDbType.DateTime).Value = DateTime.Now;
                      command.Parameters.Add("@created_by", SqlDbType.VarChar).Value = username;
  
                      SqlParameter order_id = new SqlParameter("@order_id", SqlDbType.Int);
                      //study_name.Value = 
                      order_id.Direction = ParameterDirection.Output;
                      command.Parameters.Add(order_id);
  
                      command.ExecuteNonQuery();
                      command.Parameters.Clear();
  
                      //loop resupply list 
                      for (int i = 0; i &lt; resupplyList.Count(); i++)
                      {
                          try
                          {
                              SqlCommand cmd = new SqlCommand("CreateOrder", conn);
                              cmd.CommandType = CommandType.StoredProcedure;
  
                              cmd.Parameters.Add("@study_id", SqlDbType.Int).Value = study_id;
                              cmd.Parameters.Add("@centre_id", SqlDbType.Int).Value = centre_id;
                              cmd.Parameters.Add("@created_by", SqlDbType.VarChar).Value = username;
                              cmd.Parameters.Add("@quantity", SqlDbType.VarChar).Value = resupplyList[i].Quantity;
                              cmd.Parameters.Add("@centre_id", SqlDbType.Int).Value = centre_id;
                              cmd.Parameters.Add("@depot_id", SqlDbType.VarChar).Value = depot_id;
                              cmd.Parameters.Add("@treatment_code", SqlDbType.Int).Value = centre_id;
                              cmd.Parameters.Add("@order_id", SqlDbType.Int).Value = (int)order_id.Value;
                              cmd.ExecuteNonQuery();
                          }
                          catch (SqlException ex)
                          {
                              transaction.Rollback();
                              ExceptionUtility.LogException(ex, "error");
                              throw ex;
                          }
                          catch (Exception ex)
                          {
                              transaction.Rollback();
                              ExceptionUtility.LogException(ex, "error");
                              throw ex;
                          }
                          finally
                          {
                              conn.Close();
                              conn.Dispose();
                          }
  
                      }
  
                      return (int)order_id.Value;
  
                  }
                  catch (Exception ex)
                  {
                      transaction.Rollback();
                      ExceptionUtility.LogException(ex, "error");
                      throw ex;
                  }
                  finally
                  {
                      // Attempt to commit the transaction.
                      transaction.Commit();
  
                      conn.Close();
                      conn.Dispose();
                      command.Dispose();
                  }
  </code></pre>