的ExecuteNonQuery要求命令有事务错误在我的代码 [英] ExecuteNonQuery requires the command to have a transaction error in my code
问题描述
我得到 cmd.ExecuteNonQuery 下面的错误。
的ExecuteNonQuery要求命令有交易时分配给该命令的
连接处于挂起本地事务。
尚未初始化命令的Transaction属性。
"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."
下面是我的代码：
//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.
推荐答案
尝试改变这一行
SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() +
" WHERE Customer_ID=" + a + "", scon);
在这样
SqlCommand cmd = new SqlCommand("update Contact_Info set CustInfo=" + ds.GetXml() +
" WHERE Customer_ID=" + a + "", scon, sqlTrans);
完全错误消息指出的问题。
在这些线路你已经打开了交易，你必须仍处于错误点开
The error message states exactly the problem. In these lines you have opened a transaction and you have still open at the point of error
.....
scon.Open();
SqlTransaction sqlTrans = scon.BeginTransaction();
.....
现在，每次当连接时应该执行的SqlCommand一个开放的交易必须了解这一点。结果
你可以使用上面介绍的构造，也可以在执行之前设置 cmd.Transaction 属性。命令
Now, every SqlCommand that should be executed when the connection has an opened transaction need to be informed of this.
You can use the constructor as explained above or you can set the cmd.Transaction property before executing the command.
修改：只要是完整的。避免全部采用查询文本更新/插入/删除/选择一个数据库时，花费了使用字符串连接。使用的参数。这将避免与陌生的或无效字符，最重要的防止 SQL注入攻击的攻击
EDIT: Just to be complete. 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
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();
这篇关于的ExecuteNonQuery要求命令有事务错误在我的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
