I get this error message when trying to execute the following code.
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction
Can anyone advise where the problem is? I think the root of the problem is the part where I try to execute the stored procedure.
The stored procedure creates its own transaction when executed.
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 < 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(); }
source share