ExecuteNonQuery requires the command to have a transaction error in my code
You need to change this line
SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() +
" WHERE Customer_ID=" + a + "", scon);
in this way
SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() +
" WHERE Customer_ID=" + a + "", scon, sqlTrans);
The error message states exactly the problem. Before code reaches that line you have opened a transaction and it is still open at the point of error
.....
scon.Open();
SqlTransaction sqlTrans = scon.BeginTransaction();
.....
Now, every SqlCommand executed when the connection has an opened transaction need to be informed of this. The transaction is not automatically set by the Framework.
You can use the SqlCommand constructor, as explained above, or you can set the cmd.Transaction
property before executing the command.
Warning 1
The need to set the transaction for the current command is true even if you create the SqlCommand directly from the connection itself.
SqlCommand cmd = scon.CreateCommand();
cmd.Transaction = sqlTrans; // Required when inside a transaction
Warning 2
Avoid at all cost the use of string concatenation when using query text to update/insert/delete/select on a database. Use parameters. This will remove problems with strange or invalid characters and, most important, will prevent SqlInjection Attacks
string sqlText = "update Contact_Info set CustInfo=@info WHERE Customer_ID=@id";
SqlCommand cmd = new SqlCommand(sqlText, scon, sqlTrans);
cmd.Parameters.AddWithValue("@info", ds.GetXml());
cmd.Parameters.AddWithValue("@id",a);
cmd.ExecuteNonQuery();
Also, another recommendation is to NOT use AddWithValue, while handy, this method has many problems as explained in my answer here
You have started a transaction that is not commited before you called cmd.ExecuteNonQuery()
.
Just write down cmd.Transaction = sqlTrans;
just before cmd.ExecuteNonQuery();
it will ensure that Now ExecuteNonQuery()
will be executed in same transaction and also will be able to see all the modification done to database in the same transaction.