存储过程需要的参数，我已经中过 [英] Stored procedure expects a parameter I am already passing in

问题描述

我想这一声明执行存储过程：

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

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

和我打电话在C＃中，如下所示：

And I am calling in C# as follows:

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:

过程或函数'GetByName方法'需要参数'@firstName这是没有提供。

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

更新：

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

和说明：

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

这两个参数，但它仍然抛出异常。

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

推荐答案

我已经看到了这个问题出现很多，多次在两种常见情况：

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

1. 被分配到参数的值是空（或在VB.Net为Nothing）。这是.NET空，而不是数据库空（的DBNull.Value），并发生这种情况最常使用的字符串。

1. 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.

参数正在创建与错误的命令对象相关联。当你在同一类具有相似名称的多个命令对象这通常发生。

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.

请仔细检查code，以确保该字符串变量未设置为null，该参数被添加到正确的命令。

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.

更新

根据被张贴的全部更新code，可能的问题上面1。

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

要避免这个问题，在您的code，添加下面的方法的顶部：

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

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

这篇关于存储过程需要的参数，我已经中过的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！