"过程或函数指定的参数太多"不过，这并不 [英] "Procedure or function has too many arguments specified" But It Doesn't

问题描述

感谢您的时间提前对您有所帮助。这是大加赞赏。

两个长小时冲刷堆栈溢出和其他谷歌结果的原因，我得到一个过程或函数指定的参数太多之后， 我没有发现任何有用的帮助。这样做的原因是，无处不在，我读，我可能要么指定的参数太多，我的论点，或不正确的类型不正确的名称。这些都不是真正的我的情况。这里是我的代码：

  CREATE PROCEDURE dbo.sproc_UpdateInfo 
 
（
 @Name为nvarchar （40），
 @UserId VARCHAR（50），
 @Password VARCHAR（50），
 @address为nvarchar（120）
）
 
 AS 
更新tbl_Users集名称= @名称，密码= @密码，地址= @地址其中userid = @用户ID 
 
 
返回
  

这是事物的C＃端：

 的SqlCommand SQLCMD =新的SqlCommand（）; 
 sqlCmd.CommandText =sproc_UpdateInfo; 
 sqlCmd.CommandType = CommandType.StoredProcedure; 
 
 sqlCmd.Parameters.Add（@名称，SqlDbType.NVarChar，40）.value的=名称; 
 
 sqlCmd.Parameters.Add（@用户ID，SqlDbType.VarChar，50）.value的用户ID =; 
 sqlCmd.Parameters.Add（@密码，SqlDbType.VarChar，50）.value的=密码; 
 sqlCmd.Parameters.Add（@地址，SqlDbType.NVarChar，120）.value的=地址; 
 
的SqlConnection sqlConn =新的SqlConnection（的connectionString）; 
 
 sqlCmd.Connection = sqlConn; 
 
试
 {
 sqlCmd.Connection.Open（）; 
 
 INT rowaffected = sqlCmd.ExecuteNonQuery（）; //这里出现错误
返回rowaffected; 
} 
赶上（SQLEXCEPTION SE）
 {
抛出新的异常（的SQLException：sqlstr =+ sqlCmd.CommandText，SE）; 
} 
终于
 {
 sqlCmd.Dispose（）; 
 sqlConn.Close（）; 
} 
  

解决方案

切换到这一点，并告诉我如果错误消失...

  sqlCmd.Parameters.Add（@名称，SqlDbType.NVarChar，40）。值=名字？ ; 
 
 sqlCmd.Parameters.Add（@用户ID，SqlDbType.VarChar，50）.value的用户ID =？ ; 
 sqlCmd.Parameters.Add（@密码，SqlDbType.VarChar，50）.value的=密码？ ; 
 sqlCmd.Parameters.Add（@地址，SqlDbType.NVarChar，120）.value的=地址？ ; 
  

此外，在服务器上运行跟踪，当你从代码执行查询：
（这里是如何： http://databases.about.com/od/sqlserver/ht/trace.htm）

告诉我们的执行结果是什么样子（实际上是如何执行你的存储过程），如果它被称为的。

这里没有魔法。只有几件事情可以回事：

您不执行你认为你是或连接到错误的数据库服务器或存储过程的相同版本。检查存储过程的架构。你确定你正在执行的存储过程是DBO。并没有它的另一个版本与不DBO参数少？这已经得到了我几次。我会不小心修改/创建错误的架构下，一个存储过程，当我的代码没有指定哪些模式，一来就是默认我的用户，我登录时会转而执行。真的，你必须运行跟踪。这是这样的事情拍摄的基本困境。你需要真正看到哪些SQL被试图执行。复制/粘贴在这里的代码到注释部分。这是否代码给你同样的错误？我敢打赌，这是一个模式的混乱，你有多个版本。听起来很不客气的说，但如果不是这样，你不是在同一台服务器/数据库，你以为你是。偏偏我们最好的！ ：）

另外一件事情可能会发生。有没有在主数据库存储过程的一个版本？如果是这样，请记住，它会被执行，而不是不管你的存储过程是在您连接到数据库。检查主数据库。如果你创造了它在那里，它有参数少那么这可能是你的问题。

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();
}

解决方案

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.

这篇关于"过程或函数指定的参数太多"不过，这并不的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！