C# ExecuteNonQuery 要求命令在我的代码中出现事务错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10648718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ExecuteNonQuery requires the command to have a transaction error in my code
提问by Rania Umair
I get the following error on cmd.ExecuteNonQuery.
我收到以下错误cmd.ExecuteNonQuery。
"ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."
“当分配给命令的连接处于挂起的本地事务中时，ExecuteNonQuery 要求命令具有事务。命令的事务属性尚未初始化。”
Here is my code:
这是我的代码：
//if (hdRefresh.Value.Length > done.Value.Length || done.Value == "1")
//{
// //Write Your Add Customer Code here > Response.Write("true")
// done.Value = hdRefresh.Value;
//}
//else
//{
// Response.Redirect("~/Cashier/BTBill.aspx");
// return;
//}
if (IsClosedToDay())
{
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "Warning", "<script>alert('Day Closing has been Performed ')</script>", false);
return;
}
DateTime dateFeomDB = getdate();
// by atizaz
if (HDD.Value == "" || HDD.Value == null)
{
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "Warning", "<script>alert('No Transaction Found')</script>", false);
return;
}
//
SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ToString());
Common.BillTransaction bill1 = new Common.BillTransaction();
ProcessUpdateBalandUnAuthBal insertBalance = new ProcessUpdateBalandUnAuthBal();
Common.Currency currencyy = new Common.Currency();
ProcessAuthorizeTokenByBillNo authorize = new ProcessAuthorizeTokenByBillNo();
BillTransaction bill = new BillTransaction();
scon.Open();
SqlTransaction sqlTrans = scon.BeginTransaction();
try
{
string strforxml = HDD.Value;
XmlDocument docXml = new XmlDocument();
#region Read In To Sender Controlls
#region Common Information
Contact con = new Contact();
con.Title = ddlTitle.SelectedItem.Text;
con.FirstName = TextBox1.Text.Trim();
con.LastName = TextBox9.Text.Trim();
con.ConTactNo = txtCell.Text == "" ? SqlString.Null : txtCell.Text;
con.Country = ddlCountry.SelectedItem.Text;
con.CustomerType = ddlCustomerType.SelectedItem.Text;
con.CustTypeID = int.Parse(ddlCustomerType.SelectedValue);
con.CountryID = Int32.Parse(ddlCountry.SelectedValue);
con.sqlTransaction = sqlTrans;
if (Scitytxt.Value != "")
{
try
{
con.City = Scitytxt.Value;
con.CityID = Int32.Parse(Scityval.Value);
}
catch (Exception)
{ }
}
else
{
con.City = SqlString.Null;// Scitytxt.Value;
con.CityID = SqlInt32.Null;// Int32.Parse(Scityval.Value);
con.Address = "";
}
//con.City = ddlCity.SelectedItem.Text;
//con.CityID = int.Parse(ddlCity.SelectedValue);
con.Address = TextBox10.Text;
#endregion
#region Check For NIC and Passport
if (txtNIC.Text != "" || txtPassport.Text != "")
{
SqlDataReader rdrsender;
if (txtNIC.Text != "")
{
con.NIC = txtNIC.Text;
}
else
{
con.NIC = SqlString.Null;
}
if (txtPassport.Text != "")
{
con.Passport = txtPassport.Text;
}
else
{
con.Passport = SqlString.Null;
}
ProcessSearchContactInContactInfo srchSender = new ProcessSearchContactInContactInfo();
srchSender.Contact = con;
srchSender.Invokewith5parameters();
rdrsender = srchSender.ResultSet;
#region If record Doesnot Exist In response of NIC Passport
if (!rdrsender.Read())
{
rdrsender.Close();
rdrsender.Dispose();
// con.sqlTransaction = sqlTrans;
ProcessAddContact InsertnewSenderInfo = new ProcessAddContact();
// InsertnewSenderInfo.sqlTransaction = sqlTrans;
InsertnewSenderInfo.Contact = con;
InsertnewSenderInfo.Invoke();
// sender1 = InsertnewSenderInfo.ResultSet;
// Sender_ID.Value = sender1[13].ToString();
}
#endregion
#region If Record Exists
else
{
con.CustomerID = Int32.Parse(rdrsender["Customer_ID"].ToString());
rdrsender.Close();
rdrsender.Dispose();
}
#endregion
}
#endregion
#region If Customer Donot Have NIC And/OR Passport
else// this executes when both Pasport and NIC are Null
{
con.NIC = SqlString.Null;
con.Passport = SqlString.Null;
ProcessAddContact InsertnewSenderInfo = new ProcessAddContact();
InsertnewSenderInfo.Contact = con;
InsertnewSenderInfo.Invoke();
DataSet ds = new DataSet();
int a = con.CustomerID;
StringReader inforeader = new StringReader("<CusTable><CusInfo><Relation_Type></Relation_Type><HusbandFather_Name></HusbandFather_Name><Address_Present></Address_Present><Address_Other></Address_Other><Phone_No_Office></Phone_No_Office><Cell_No></Cell_No><Fax_No></Fax_No><Date_Of_Birth></Date_Of_Birth><NTN_No></NTN_No><Nationality></Nationality><Occupation></Occupation><Relation_With_Financial_Institution></Relation_With_Financial_Institution><Other_Relation_With_Financial_Institution></Other_Relation_With_Financial_Institution><Business_Relation></Business_Relation></CusInfo></CusTable>");
ds.ReadXml(inforeader);
ds.GetXml();
SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() + " WHERE Customer_ID=" + a + "", scon);
cmd.ExecuteNonQuery();
// sender1 = InsertnewSenderInfo.ResultSet;
// Sender_ID.Value = sender1[13].ToString();
}
tell me what is problem in my code and how to solve it.
告诉我代码中有什么问题以及如何解决。
采纳答案by Steve
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.
现在，当连接有一个打开的事务时执行的每个 SqlCommand 都需要被告知这一点。事务不是由框架自动设置的。
You can use the SqlCommand constructor, as explained above, or you can set the cmd.Transactionproperty before executing the command.
您可以使用 SqlCommand 构造函数，如上所述，或者您可以cmd.Transaction在执行命令之前设置属性。
WarningThe need to set the transaction for the current command is true even if you create the SqlCommand directly from the connection itself.
警告即使您直接从连接本身创建 SqlCommand，也需要为当前命令设置事务。
SqlCommand cmd = scon.CreateCommand();
cmd.Transaction = sqlTrans; // Required when inside a transaction
Note: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 avoid problems with strange or invalid characters and most important will prevent SqlInjection Attacks
注意：在使用查询文本更新/插入/删除/选择数据库时，不惜一切代价避免使用字符串连接。使用参数。这将避免奇怪或无效字符的问题，最重要的是将防止SqlInjection 攻击
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
此外，另一个建议是不要使用 AddWithValue，虽然很方便，但此方法有很多问题，如我在此处的回答中所述
回答by Romil Kumar Jain
You have started a transaction that is not commited before you called cmd.ExecuteNonQuery().
您已经启动了一个在您调用之前未提交的事务cmd.ExecuteNonQuery()。
Just write down cmd.Transaction = sqlTrans;just before cmd.ExecuteNonQuery();
就cmd.Transaction = sqlTrans;在之前写下来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.
它将确保 NowExecuteNonQuery()将在同一个事务中执行，并且还能够看到在同一个事务中对数据库所做的所有修改。
