CODEWITHSUNDEEP
c#sql-servert-sqlstored-proceduresado.net
jokul
jokul

Reputation: 1339

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

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.

Upvotes: 5

Views: 1543

Answers (3)

Abhijit Ojha
Abhijit Ojha

Reputation: 479

Please add CommandaType to SQLCommand.

e.g: scmd.CommandType = CommandType.StoredProcedure;

Upvotes: 0

user1760979
user1760979

Reputation:

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

Upvotes: 1

competent_tech
competent_tech

Reputation: 44931

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 = "";
}

Upvotes: 7

Related Questions