C# “过程或函数指定了太多参数”但它没有
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19641849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Procedure or function has too many arguments specified" But It Doesn't
提问by Noah Crowley
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:
由于我收到“过程或函数指定的参数太多”的原因，在搜索 Stack Overflow 和其他 Google 结果两个小时后，我没有找到任何有用的帮助。这样做的原因是，在任何地方，我都读到我可能指定了太多参数、参数名称不正确或类型不正确。这些都不符合我的情况。这是我的代码：
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:
这是 C# 方面的事情：
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();
}
采纳答案by maplemale
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)
此外，当您从代码执行查询时，在服务器上运行跟踪：（方法如下：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.
告诉我们执行结果是什么样的（你的 sproc 是如何实际执行的）以及它是否被调用。
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! :)
您没有执行您认为连接到错误数据库或服务器的相同版本的 sproc。检查 sproc 的架构。你确定你正在执行的 sproc 是 dbo。并且没有其他版本的参数更少而不是 dbo？这已经让我好几次了。我会不小心在错误的模式下修改/创建一个 sproc，当我的代码没有指定哪个模式时，我登录的用户的默认模式将被执行。真的，您必须运行跟踪。这是此类事情的基本故障排除。您需要实际查看 SQL 试图执行的内容。将该代码复制/粘贴到此处的评论部分。该代码是否给您同样的错误？我敢打赌这是架构混乱，并且您有多个版本。说起来很不礼貌，但要么是这样，要么你不在你认为的同一个服务器/数据库上。发生在我们最好的人身上！:)
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.
另一件事可能正在发生。主数据库中是否有该 sproc 的版本？如果是这样，请记住它将被执行，而不是您连接到的数据库中的任何 sproc。检查主数据库。如果您在那里创建它，并且它的参数较少，那么这可能是您的问题。
回答by Hammad Khan
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.
真正的问题是 select 和 update 存储过程必须有相同的参数，否则你会得到这个错误。真的不直观，我猜是微软的典型。
回答by Colin
In my case, I was making changes to a WebForms project which hadn't been changed for over 4 years.
就我而言，我正在对一个已超过 4 年未更改的 WebForms 项目进行更改。
I was making changes to the database projects within Visual Studio:
我正在对 Visual Studio 中的数据库项目进行更改：
But the changes actually needed to be made within SQL Server Manager Studio:
但实际上需要在 SQL Server Manager Studio 中进行更改：
And then Executed within SQL Server Manager Studio!
然后在 SQL Server Manager Studio 中执行！
