Stored procedure expects a parameter I am already

问题:

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:

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.

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.

回答1:

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

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.

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

Update

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

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