“Procedure or function has too many arguments spec

Question

Thank you ahead of time for your help. It is much appreciated.

After two long hours of scouring Stack Overflow and other Google results for the reason that I am getting a "Procedure or Function has too many arguments specified," I have found no useful help. The reason for this is that everywhere, I read that I probably either have too many arguments specified, the incorrect names for my arguments, or the incorrect types. None of these are true to my case. Here is my code:

CREATE PROCEDURE dbo.sproc_UpdateInfo 

(
@Name nvarchar(40),
@UserId varchar(50),
@Password varchar(50),
@Address nvarchar(120)
)

AS
Update tbl_Users SET Name=@Name, Password=@Password, Address=@Address WHERE UserId=@UserId


RETURN

And here is the C# side of things:

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "sproc_UpdateInfo";
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar, 40).Value = name;

sqlCmd.Parameters.Add("@UserId", SqlDbType.VarChar, 50).Value = userID;
sqlCmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = password;
sqlCmd.Parameters.Add("@Address", SqlDbType.NVarChar, 120).Value = address;

SqlConnection sqlConn = new SqlConnection(connectionString);

sqlCmd.Connection = sqlConn;

try
{
    sqlCmd.Connection.Open();

    int rowaffected = sqlCmd.ExecuteNonQuery(); //Error occurs here
    return rowaffected;
}
catch (SqlException se)
{
    throw new Exception("SqlException: sqlstr=" + sqlCmd.CommandText, se);
}
finally
{
    sqlCmd.Dispose();
    sqlConn.Close();
}

Answer 1

In my case, I was making changes to a WebForms project which hadn't been changed for over 4 years.

I was making changes to the database projects within Visual Studio:

But the changes actually needed to be made within SQL Server Manager Studio:

And then Executed within SQL Server Manager Studio!

Answer 2

The error is in fact misleading and incorrect.

procedure or function has too many arguments specified

The real issue is select and update stored procedures must have same parameters, otherwise you will get this error. Really not intuitive, Typical of Microsoft I guess.

Answer 3

Change to this and tell me if the error goes away...

sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar, 40).Value = name ?? "";

sqlCmd.Parameters.Add("@UserId", SqlDbType.VarChar, 50).Value = userID ?? "";
sqlCmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = password ?? "";
sqlCmd.Parameters.Add("@Address", SqlDbType.NVarChar, 120).Value = address ?? "";

Also, run a trace on the server when you execute the query from code: (here's how: http://databases.about.com/od/sqlserver/ht/trace.htm)

Tell us what the execution results look like (how your sproc is actually being executed) and if it's being called at all.

There's no magic here. Only a few things could be going on:

You aren't executing the same version of the sproc you think you are or are connected to the wrong DB or server. Check the schema of the sproc. Are you sure the sproc you are executing is dbo. and there isn't another version of it with less parameters that isn't dbo? This has gotten me a few times. I'll accidentally modify/create a sproc under the wrong schema and when my code isn't specifying which schema, the one that is default for my user I'm logging in as will be executed instead. Really, you must run a trace. This is the basic trouble shooting for things like this. You need to actually see what SQL is trying to execute. Copy/paste that code into the comment section here. Does that code give you the same error? I'm betting this is a schema confusion and you have multiple versions. Sounds rude to say, but either that or you're not on the same server / db that you think you are. Happens to the best of us! :)

One other thing could be happening. Is there a version of that sproc in the master db? If so, keep in mind that it will be executed and not whatever you sproc is in the DB you are connected to. Check the master DB. If you created it there, and it has less parameters then that could be your problem.