存储过程需要一个我已经传入的参数

I am trying to execute a stored procedure with this declaration:

我试图使用此声明执行存储过程:

ALTER PROCEDURE [dbo].[getByName]
    @firstName varchar,
    @lastName varchar
AS
...

And I am calling in C# as follows:

我在C#中调用如下:

public List<Person> GetPersonByName(string first, string last)
{
    var people = new List<Person>();
    var connString = ConfigurationManager.ConnectionStrings["MyDbConnString"].ConnectionString;
    using (var conn = new SqlConnection(connString))
    {
        using (var cmd = new SqlCommand("dbo.getByName",conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar, 50)).Value = first;
            cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar, 50)).Value = last;
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                people = ReadPeopleData(reader);
            }
            conn.Close();
        }
    }
    return people;
}

But I just get back this error:

但我刚刚收到这个错误:

Procedure or function 'getByName' expects parameter '@firstName' which was not supplied.

过程或函数'getByName'需要未提供的参数'@firstName'。

Update:

ALTER PROCEDURE [dbo].[getEmployeesByName]
    @firstName varchar(50),
    @lastName varchar(50)
AS
...

and stating:

cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar, 50)).Value

for both parameters, yet it continues to throw the exception.

对于这两个参数,它继续抛出异常。

2 个解决方案

#1

I have seen this issue occur many, many times in two common scenarios:

我已经看到这个问题在两种常见情况下发生了很多次:

The value being assigned to the parameter is null (or Nothing in VB.Net). This is the .Net null, not the DB null (DBNull.Value) and this happens most commonly with strings.

分配给参数的值为null(或VB.Net中为Nothing)。这是.Net null,而不是DB null(DBNull.Value),这种情况最常发生在字符串中。
The parameter being created is associated with the wrong command object. This commonly occurs when you have multiple command objects in the same class with similar names.

正在创建的参数与错误的命令对象相关联。当您在具有相似名称的同一个类中有多个命令对象时,通常会发生这种情况。

Please double-check the code to ensure that the string variable is not set to null and that the parameter is being added to the correct command.

请仔细检查代码以确保字符串变量未设置为null,并且该参数正在添加到正确的命令中。

Update

Based on the full updated code that was posted, the likely issue is 1 above.

根据发布的完整更新代码,可能的问题是上面的1。

To circumvent this problem in your code, add the following at the top of the method:

要在代码中避免此问题,请在方法顶部添加以下内容:

if (first == null) {
  first = "";
}
if (last == null) {
  last = "";
}

#2

Try this it will work:

试试这个会起作用:

SqlParameter[] sqlParams = new SqlParameter[] { 
            new SqlParameter("@UserName",(object)userName ?? DBNull.Value),
            new SqlParameter("@Password",(object)password ?? DBNull.Value)
};

If parameter is NULL than replace it with DBNull Type using ?? Operator

如果参数为NULL,则用DBNull Type替换它?操作者

#1