过程或函数'UserManagement'需要参数'@CallType'，但未提供[重复]

This question already has an answer here:

这个问题在这里已有答案:

Error: Procedure or function expects parameter which was not supplied 2 answers

错误:过程或函数需要未提供2个答案的参数

String strConnString = ConfigurationManager.ConnectionStrings["CallcenterConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();

cmd.Parameters.Add("@CallType", SqlDbType.VarChar).Value = ddlCalltype.SelectedValue.ToString();
cmd.Parameters.Add("@Format", SqlDbType.VarChar).Value = ddlFormat.SelectedValue.ToString();
cmd.Parameters.Add("@disposition", SqlDbType.VarChar).Value = ddlDisp.SelectedValue.ToString();
cmd.Parameters.Add("@SubDisposition", SqlDbType.VarChar).Value = ddlSubdisp.SelectedValue.ToString();
cmd = new SqlCommand("UserManagement", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();

da.SelectCommand = cmd;
da.Fill(dt);
gvDetails.DataSource = dt;
gvDetails.DataBind();
gvDetails.Visible = true;

2 个解决方案

#1

This line assigned new instance of SqlCommand to cmd for second time instead of using existing SqlCommand which contains declared parameters, therefore removing all parameters already declared above:

此行第二次将新的SqlCommand实例分配给cmd,而不是使用包含声明参数的现有SqlCommand,因此删除上面已声明的所有参数:

cmd = new SqlCommand("UserManagement", con);

The correct way to manage SqlCommand with parameterized stored procedure should be like this, with using statement to manage system resources during execution (better to use try...catch...finally block in case you want to handle SqlException):

使用参数化存储过程管理SqlCommand的正确方法应该是这样的,使用using语句在执行期间管理系统资源(最好使用try ... catch ... finally块以防你想要处理SqlException):

String strConnString = ConfigurationManager.ConnectionStrings["CallcenterConnectionString"].ConnectionString;
var dt = new DataTable();

using (var con = new SqlConnection(strConnString))
{
    con.Open();
    using (var cmd = new SqlCommand("UserManagement", con))
    {
        cmd.Parameters.Add("@CallType", SqlDbType.VarChar).Value = ddlCalltype.SelectedValue.ToString();
        cmd.Parameters.Add("@Format", SqlDbType.VarChar).Value = ddlFormat.SelectedValue.ToString();
        cmd.Parameters.Add("@disposition", SqlDbType.VarChar).Value = ddlDisp.SelectedValue.ToString();
        cmd.Parameters.Add("@SubDisposition", SqlDbType.VarChar).Value = ddlSubdisp.SelectedValue.ToString();
        cmd.CommandType = CommandType.StoredProcedure;

        // using SqlDataAdapter
        using (var da = new SqlDataAdapter)
        {
            da.SelectCommand = cmd;
            da.Fill(dt);
        }

        // using DataTable.Load directly
        // dt.Load(cmd.ExecuteReader());
    }
    con.Close();
}

// other stuff

NB: In short you can replace the second assignment of cmd in question using cmd.CommandText = "UserManagement";, hence it just supplying stored procedure name instead assigning another instance of SqlCommand. Also instead of ExecuteNonQuery() try using ExecuteReader() with DataTable.Load() method.

注意:简而言之,您可以使用cmd.CommandText =“UserManagement”;替换有问题的cmd的第二个分配,因此它只提供存储过程名称,而是分配另一个SqlCommand实例。而不是ExecuteNonQuery()尝试使用DataTable.Load()方法的ExecuteReader()。

#2

Your code is a big mess. To me it looks like you've just used copy and paste from a bunch of other places and don't quite really understand what's going on in there.

你的代码很乱。对我而言,你似乎只是使用了来自其他地方的复制和粘贴,并且不太了解那里发生了什么。

It should be something like this:

它应该是这样的:

String strConnString = ConfigurationManager.ConnectionStrings["CallcenterConnectionString"].ConnectionString;
DataTable dt = new DataTable();
using(var con = new SqlConnection(strConnString))
{
    using(var cmd = new SqlCommand("UserManagement", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@CallType", SqlDbType.VarChar).Value = ddlCalltype.SelectedValue.ToString();
        cmd.Parameters.Add("@Format", SqlDbType.VarChar).Value = ddlFormat.SelectedValue.ToString();
        cmd.Parameters.Add("@disposition", SqlDbType.VarChar).Value = ddlDisp.SelectedValue.ToString();
        cmd.Parameters.Add("@SubDisposition", SqlDbType.VarChar).Value = ddlSubdisp.SelectedValue.ToString();
        using(var da = new SqlDataAdapter())
        {
            da.SelectCommand = cmd;
            da.Fill(dt);
        }
    }
}
gvDetails.DataSource = dt;
gvDetails.DataBind();
gvDetails.Visible = true;

#1