请教，SqlParameter传参哈希表更新失败的问题！

morliz子轩 2019-11-19 04:27:04



private void btnAction_Click(object sender, EventArgs e)

        {

            string AllPieceCount = textTotalCount.Text,

                DoorFrameCount = textDoorFrameCount.Text,

                DoorFanCount = textDoorFanCount.Text,

                DoorWaist = textDoorWaist.Text,

                DoorFitting = textDoorFitting.Text,

                DoorGlass = textDoorGlass.Text;

            int InvoiceDetailID = Convert.ToInt32(textBox1.Text);



            string updatesql = "update a set a.AllPieceCount= '@AllPieceCount'" +

                ",a.DoorFrameCount = '@DoorFrameCount'" +

                ",a.DoorFanCount = '@DoorFanCount'" +

                ",a.DoorWaist = '@DoorWaist'" +

                ",a.DoorFitting = '@DoorFitting'" +

                ",a.DoorGlass = '@DoorGlass'" +

                " from IMSN AS a" +

                " where SNProjectID = 4" +

                " and a.InvoiceDetailID =" + Convert.ToInt32(textBox1.Text.Trim());



            SqlParameter[] para = new SqlParameter[]

            {

                new SqlParameter("@AllPieceCount",AllPieceCount),

                new SqlParameter("@DoorFrameCount",DoorFrameCount),

                new SqlParameter("@DoorFanCount",DoorFanCount),

                new SqlParameter("@DoorWaist",DoorWaist),

                new SqlParameter("@DoorFitting",DoorFitting),

                new SqlParameter("@DoorGlass",DoorGlass),

                new SqlParameter("@InvoiceDetailID",InvoiceDetailID)

            };



            Hashtable SQLStringList = new Hashtable();

            SQLStringList.Add(updatesql, para);

            try

            {

                ExecuteSqlTran(SQLStringList);      //执行多条sql语句，完成数据库事务

                MessageBox.Show("更新成功！", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            catch

            {

                MessageBox.Show("执行更新失败，请检查订单相关参数信息。", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

                throw;

            }



        }



        public static void ExecuteSqlTran(Hashtable SQLStringList)

        {

            conn = new SqlConnection(ConnStr);

            conn.Open();



            SqlTransaction trans=conn.BeginTransaction();

            SqlCommand cmd = new SqlCommand();

            try

            {

                foreach (DictionaryEntry myDE in SQLStringList)//循环哈希表

                {

                    string cmdText = myDE.Key.ToString();//获取键值（本例中 即，sql语句）

                    SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;//获取键值（本例中 即，sql语句对应的参数）

                    PrepareCommand(cmd, conn, trans, cmdText, cmdParms); //调用PrepareCommand()函数，添加参数

                    int val = cmd.ExecuteNonQuery();//调用增删改函数ExcuteNoQuery()，执行哈希表中添加的sql语句

                    cmd.Parameters.Clear(); //清除参数

                }

                trans.Commit();//提交事务



            }

            catch

            {

                trans.Rollback(); //事务回滚

                throw; //抛出异常

            }

        }



        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)

        {

            if (conn.State != ConnectionState.Open)//如果数据库连接为关闭状态

                conn.Open();//打开数据库连接

            cmd.Connection = conn;//设置命令连接

            cmd.CommandText = cmdText;//设置执行命令的sql语句

            if (trans != null)//如果事务不为空

                cmd.Transaction = trans;//设置执行命令的事务

            cmd.CommandType = CommandType.Text;//设置解释sql语句的类型为“文本”类型（也是就说该函数不适用于存储过程）

            if (cmdParms != null)//如果参数数组不为空

            {





                foreach (SqlParameter parameter in cmdParms) //循环传入的参数数组

                {

                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

                        (parameter.Value == null))

                    {

                        parameter.Value = DBNull.Value; //获取参数的值

                    }

                    cmd.Parameters.Add(parameter);//添加参数

                }

            }

        }

更新失败，请教一下是什么问题？

...全文

93 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

morliz子轩 2019-11-19

打赏
举报

引用 6 楼正怒月神的回复:

搞定了。十分感谢！！！

正怒月神 2019-11-19

打赏
举报

引用 5 楼 morliz子轩的回复:

[quote=引用 4 楼正怒月神的回复:] 现在测试没异常了，但把所有的值，全更新成null了。我查不到问题在哪，请帮忙看看

你可以看一下 sqlprofile。看看生成的语句和实际执行的参数。到底是什么。其实这种问题，就这几个方向。归结起来，最后看sql语句，反过来分析问题，比较方便[/quote] 我刚跟踪了一下，脚本成这样了：不知道这个参数要怎么传进去：


exec sp_executesql 
N'update a set a.AllPieceCount= @AllPieceCount
,a.DoorFrameCount = @DoorFrameCount
,a.DoorFanCount = @DoorFanCount
,a.DoorWaist = @DoorWaist
,a.DoorFitting = @DoorFitting
,a.DoorGlass = @DoorGlass 
  from IMSN AS a where SNProjectID = 4 
  and a.InvoiceDetailID =291088',
  N'@AllPieceCount int,
  @DoorFrameCount int,
  @DoorFanCount int,
  @DoorWaist int,
  @DoorFitting int,
  @DoorGlass int,
  @InvoiceDetailID int',
  @AllPieceCount=NULL,
  @DoorFrameCount=NULL,
  @DoorFanCount=NULL,
  @DoorWaist=NULL,
  @DoorFitting=NULL,
  @DoorGlass=NULL,
  @InvoiceDetailID=NULL

[/quote] 那就说明你的参数没传递过去。

SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@AllPieceCount",SqlDbType.Int,AllPieceCount),
                new SqlParameter("@DoorFrameCount",SqlDbType.Int,DoorFrameCount),
                new SqlParameter("@DoorFanCount",SqlDbType.Int,DoorFanCount),
                new SqlParameter("@DoorWaist",SqlDbType.Int,DoorWaist),
                new SqlParameter("@DoorFitting",SqlDbType.Int,DoorFitting),
                new SqlParameter("@DoorGlass",SqlDbType.Int,DoorGlass),
                new SqlParameter("@InvoiceDetailID",SqlDbType.Int,InvoiceDetailID)
            };

很明显，这里没有值。

public SqlParameter(string parameterName, object value);

不要设置类型了，直接设置值。（这是另一个方法重载） new SqlParameter("@AllPieceCount",111), 这样。

morliz子轩 2019-11-19

打赏
举报

引用 4 楼正怒月神的回复:

现在测试没异常了，但把所有的值，全更新成null了。我查不到问题在哪，请帮忙看看


exec sp_executesql 
N'update a set a.AllPieceCount= @AllPieceCount
,a.DoorFrameCount = @DoorFrameCount
,a.DoorFanCount = @DoorFanCount
,a.DoorWaist = @DoorWaist
,a.DoorFitting = @DoorFitting
,a.DoorGlass = @DoorGlass 
  from IMSN AS a where SNProjectID = 4 
  and a.InvoiceDetailID =291088',
  N'@AllPieceCount int,
  @DoorFrameCount int,
  @DoorFanCount int,
  @DoorWaist int,
  @DoorFitting int,
  @DoorGlass int,
  @InvoiceDetailID int',
  @AllPieceCount=NULL,
  @DoorFrameCount=NULL,
  @DoorFanCount=NULL,
  @DoorWaist=NULL,
  @DoorFitting=NULL,
  @DoorGlass=NULL,
  @InvoiceDetailID=NULL

正怒月神 2019-11-19

打赏
举报

引用 2 楼 morliz子轩的回复:


//我改成了这样：
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@AllPieceCount",SqlDbType.Int,AllPieceCount),
                new SqlParameter("@DoorFrameCount",SqlDbType.Int,DoorFrameCount),
                new SqlParameter("@DoorFanCount",SqlDbType.Int,DoorFanCount),
                new SqlParameter("@DoorWaist",SqlDbType.Int,DoorWaist),
                new SqlParameter("@DoorFitting",SqlDbType.Int,DoorFitting),
                new SqlParameter("@DoorGlass",SqlDbType.Int,DoorGlass),
                new SqlParameter("@InvoiceDetailID",SqlDbType.Int,InvoiceDetailID)
            };

现在测试没异常了，但把所有的值，全更新成null了。我查不到问题在哪，请帮忙看看

你可以看一下 sqlprofile。看看生成的语句和实际执行的参数。到底是什么。其实这种问题，就这几个方向。归结起来，最后看sql语句，反过来分析问题，比较方便

吃瓜农 2019-11-19

打赏
举报

sql语句把单引号去掉

morliz子轩 2019-11-19

打赏
举报


//我改成了这样：
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@AllPieceCount",SqlDbType.Int,AllPieceCount),
                new SqlParameter("@DoorFrameCount",SqlDbType.Int,DoorFrameCount),
                new SqlParameter("@DoorFanCount",SqlDbType.Int,DoorFanCount),
                new SqlParameter("@DoorWaist",SqlDbType.Int,DoorWaist),
                new SqlParameter("@DoorFitting",SqlDbType.Int,DoorFitting),
                new SqlParameter("@DoorGlass",SqlDbType.Int,DoorGlass),
                new SqlParameter("@InvoiceDetailID",SqlDbType.Int,InvoiceDetailID)
            };

现在测试没异常了，但把所有的值，全更新成null了。我查不到问题在哪，请帮忙看看

正怒月神 2019-11-19