none
ASP.NET,关于事务的执行问题 RRS feed

  • 问题

  • 请教大家,谢谢。
        protected void btnUPdate_Click(object sender, EventArgs e)
        {
            DataSet cds = (DataSet)Session["ds"];
            if (cds.Tables[0].Rows.Count == 0)
            {
                Page.RegisterStartupScript("", "<script language='javascript'>alert('请添加提单货物信息!');</script>");
            }
            else
            {
                Panel3.Visible = false;
                string strOrgId = Session["OrgID"].ToString();
                string strInterID = labInterID.Text;
                string strContNo = tbContNo.Text.ToUpper();
                string strCntrSize = ddlContSize.SelectedValue;
                string strCntrType = ddlContType.SelectedValue;
                string strLockNo = tbLockNo.Text.ToUpper();
                string strCarrierID = ddlCarrierID.SelectedValue;
                string sState = ddlState.SelectedValue;
                string strFromAddress = ddlFromAddress.SelectedValue;
                string strMotorTroopID = ddlMotorTroopID.SelectedValue;
                string strLorryNo = tbLorryNo.Text.ToUpper();
                string strAgent = ddlAgent.SelectedValue;
                string strType = ddlType.SelectedValue;
                string strTrafficModeID = ddlTrafficModeID.SelectedValue;
                string strContAdmin = ddlContAdmin.SelectedValue;
                string strWard = tbWard.Text.ToUpper();
                string strCustomAdd = ddlCustomAdd.SelectedValue;
                string strCustomerID = ddlCustomerID.SelectedValue;
                string strRemark = tbRemark.Text;
                string strSpecialType = "";
                if (cbSpecialType.Checked)
                {
                    strSpecialType = "1";
                }
                else
                {
                    strSpecialType = "0";
                }
                string strSubCheck = "";
                if (cbSubCheck.Checked)
                {
                    strSubCheck = "1";
                }
                else
                {
                    strSubCheck = "0";
                }
                string strCfsCheck = "";
                if (cbCFS.Checked)
                {
                    strCfsCheck = "1";
                }
                else
                {
                    strCfsCheck = "0";
                }
                string strCreateID = Session["user"].ToString().ToUpper();
                SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
                con.Open();
                SqlTransaction trans = con.BeginTransaction();
                SqlCommand cmd = con.CreateCommand();
                cmd.Connection = con;

                try
                {
                    cmd.Transaction = trans;
                    //更新主表
                    cmd.CommandText = "exec usp_UpEntryCont '" + strOrgId + "','" + strInterID + "','IN','" + strContNo + "','" + strCntrSize + "','" + strCntrType + "','F','" + strLockNo + "','" + sState + "','" + strFromAddress + "','" + strCustomerID + "','" + strCarrierID + "','" + strType + "','" + strTrafficModeID + "','" + strMotorTroopID + "','" + strLorryNo + "','" + strAgent + "','" + strSpecialType + "','" + strSubCheck + "','0','0','0','0','" + strCfsCheck + "','" + strContAdmin + "','" + strRemark + "','" + strCustomAdd + "','" + strWard + "','" + strCreateID + "'";
                    if (con.State.ToString() == "Closed")
                    {
                        con.Open();
                    }
                    //更新从表
                    //下面的先删除,再重新插入,但是报错,跟踪发现违反唯一性错误,如果把删除语句放到事务前的话是可以的,但是这样就不在事务里面,无法保证数据的完整性。
                    cmd.CommandText = "delete contManage_cargo where orgId='" + strOrgId + "' and interID='" + strInterID + "'";
                    for (int i = 0; i < cds.Tables[0].Rows.Count; i++)
                    {
                        string ID = cds.Tables[0].Rows[i][0].ToString();
                        string BL_NO = cds.Tables[0].Rows[i][1].ToString();
                        string trafficNo = cds.Tables[0].Rows[i][2].ToString();
                        string customTransferNo = cds.Tables[0].Rows[i][3].ToString();
                        string sealNo = cds.Tables[0].Rows[i][4].ToString();
                        string shipperID = cds.Tables[0].Rows[i][5].ToString();
                        string cargoName = cds.Tables[0].Rows[i][7].ToString();
                        string netWeight = cds.Tables[0].Rows[i][8].ToString();
                        string pkgs = cds.Tables[0].Rows[i][9].ToString();
                        string pack = cds.Tables[0].Rows[i][10].ToString();
                        string volume = cds.Tables[0].Rows[i][12].ToString();
                        cmd.CommandText = "exec usp_AddEntryContCargo '" + strOrgId + "','" + ID + "','" + strInterID + "','" + BL_NO + "','" + trafficNo + "','" + customTransferNo + "','" + sealNo + "','" + shipperID + "','" + cargoName + "','" + netWeight + "','" + pkgs + "','" + pack + "','" + volume + "'";
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            Page.RegisterStartupScript("", "<script language='javascript'>alert('货物信息修改失败!');</script>");
                        }
                    }

                    trans.Commit();
                    con.Close();
                    Page.RegisterStartupScript("", "<script language='javascript'>alert('修改成功!');</script>");
                    btnCheck.Enabled = true;
                }
                catch
                {
                    con.Close();
                    Page.RegisterStartupScript("", "<script language='javascript'>alert('修改失败!');</script>");
                }
            }


    悠悠自在的鱼
    2009年8月26日 3:11

答案

  • 你更新主表和更新从表只是赋了 CommandText 值,没有执行。使用拼SQL的方式不好,建议使用参数化的方式
    知识改变命运,奋斗成就人生!
    2009年8月26日 3:24
    版主
  • 您好,
    1、请在cmd.CommandText = "delete contManage_cargo where orgId='" + strOrgId + "' and interID='" + strInterID + "'";
    下加一句cmd.ExecuteNonQuery();
    2、如X.X.Y所建议,请改用参数来实现Sql,以提高安全性。或者使用DataAdapter。
    2009年8月26日 5:09
    版主

全部回复

  • 你更新主表和更新从表只是赋了 CommandText 值,没有执行。使用拼SQL的方式不好,建议使用参数化的方式
    知识改变命运,奋斗成就人生!
    2009年8月26日 3:24
    版主
  • 你好,  将CommandText各个语句用;隔开然后连接在一起 建议使用StringBuilder cmd.CommandText += "delete contManage_cargo where orgId='" + strOrgId + "' and interID='" + strInterID + "'";
    cmd.CommandText += "exec usp_AddEntryContCargo '" + strOrgId + "','" + ID + "','" + strInterID + "','" + BL_NO + "','" + trafficNo + "','" + customTransferNo + "','" + sealNo + "','" + shipperID + "','" + cargoName + "','" + netWeight + "','" + pkgs + "','" + pack + "','" + volume + "'";
     你这种方式前边的语句并没有执行的
    Wenn ich dich hab’,gibt es nichts, was unerträglich ist.坚持不懈!http://hi.baidu.com/1987raymond
    2009年8月26日 3:30
    版主
  • 您好,
    1、请在cmd.CommandText = "delete contManage_cargo where orgId='" + strOrgId + "' and interID='" + strInterID + "'";
    下加一句cmd.ExecuteNonQuery();
    2、如X.X.Y所建议,请改用参数来实现Sql,以提高安全性。或者使用DataAdapter。
    2009年8月26日 5:09
    版主
  • 谢谢大家,问题已解决。
    悠悠自在的鱼
    2009年8月26日 7:16