ExecuteNonQuery requires the command to have a transaction
28,936
Solution 1
when using transaction, you should use it everywhere.
cmd.Transaction = transaction;
Solution 2
using Connection String transaction not popular so far.you can delete every things that related to SqlTransaction
and then wrap your code with TransactionScope
Author by
pothios
Updated on July 12, 2022Comments
-
pothios almost 2 years
I am receiving this error message when i try 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 advice where the problem is? I guess the root of the problem is the part where i try to execute a stored procedure.
The stored procedure is creates its own transaction when execute
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(); }
-
pothios over 12 yearsdoes it matter when the stored procedures itself contains its own transaction?
-
pothios over 12 yearsso the question is how can i achieve running a transaction that will call a stored procedure with its own transaction :(
-
Afshin over 12 yearsyou do not need to do anything specific, just let .net do it's job, try above code and hopefully all will be good :)
-
Chris Rogers about 12 yearsBe very careful with TransactionScope. It seems quite nice when it works. But I have found it to be very fragile when targeting non-local SQL servers. You need things to be setup "just right".