How to troubleshoot 'Procedure or function has too many arguments specified' in asp.net 2.0

原文链接: How to troubleshoot 'Procedure or function has too many arguments specified' in asp.net 2.0

How to troubleshoot 'Procedure or function has too many arguments specified' in asp.net 2.0

 

If you are using a sqlDataSource to handle updates, deletes or inserts to an underlying database from a gridview control, then you may run across this message:

Procedure or function <stored procedure name> has too many arguments specified

This usually means what it says, that your update, delete or insert command is trying to pass more parameters than the stored procedure is set up to handle. This can happen because each datakeyname specified in the gridview control is automatically being sent to the stored procedure. So if you try to also define extra parameters, you will be passing too many. Also, using the refresh parameters button within the datasource wizard will create a parameter for a return value even if your stored procedure does not define one, so if your stored procedure does not have a return value you should remove this parameter.

If you are getting this error, it is helpful to view the parameters being passed as they happen. So, lets use the delete command as an example. Add this code to your code behind page

Protected Sub sqlDataSource1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sqlDataSource1.Deleting
For x As Integer = 0 To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(e.Command.Parameters(x).Value)
Next

End Sub

 

And at the top of your .aspx page in the page directives add @trace="true" (the line will look something like this):

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false"
CodeFile="default.aspx.vb" Inherits="default" title="Default" Trace="true"%>

Now when you click the delete command, you will see trace entries for the page. Near the top you will see individual lines for each parameter like this

	@EngineerID	0.0120328883259235	0.001463
	8	0.0120643582299198	0.000031
	@original_ServerLogicalID	0.0120823198911414	0.000018
	186	0.0121010365391007	0.000019
	@original_ServerName	0.0121185931160761	0.000018
	SERVER1	0.012136122102174	0.000018

So now you can see each parameter being passed. If the parameter names in your stored procedure do not match, then these values will not make it to your stored procedure. The parameter that is passed is based on the select command, so if you can't alter your stored procedure to change parameter names, then change your select statement to something like

Select ServerName as original_ServerName, .....

Updated 2.14.06

If you are using an objectDataSource instead of a sqlDataSource the syntax to find values and keys is different. The example below is for accessing a value when you know the key name, I haven't gotten around to enumerating through all values yet.

Protected Sub objectDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs) Handles objectDataSource1.Deleting
Dim paramsFromPage As IDictionary = e.InputParameters

Trace.Write("original_AppID:" & paramsFromPage("original_AppID").ToString)
Trace.Write("ServiceID:" & paramsFromPage("ServiceID").ToString)
End Sub

以下为本人的菜鸟英语翻译,适合不喜欢看英语的朋友- -#,如被误导,纯属巧合,高手还是建议看原文

如何解决asp.net2.0中'Procedure or function has too many arguments specified'( 存储过程或函数指定过多参数)的问题

如果你通过gridView 使用sqlDataSource来对数据库处理更新,删除或添加动作,那你可能会遇到以下信息:

Procedure or function <stored procedure name> has too many arguments specified( 存储过程或函数指定过多参数)

这条信息通常指如果你执行更新,删除或添加命令时传递的参数超过存储过程设定的参数.引起这种情况的可能是gridView控件里指定的datakeyName被自动传送给存储过程.因此如果你指定了其它的参数,你将传递过多的参数.而且,在datasource向导中如果使用'刷新参数'按钮将会创建一个return value的参数,即使你的存储过程并没有指定,所以如果你的存储过程并没有定义return value的参数,你应该把它删掉.

如果错误依旧存在,查看参数的传递过程将会有所帮助.让我们来以删除命令举例,添加以下代码到你的.cs页面

Protected Sub sqlDataSource1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sqlDataSource1.Deleting
For x As Integer = 0 To e.Command.Parameters.Count - 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(e.Command.Parameters(x).Value)
Next

End Sub

在你.aspx的页面顶部页面指令添加@trace="true"(像下面的代码)

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false"
CodeFile="default.aspx.vb" Inherits="default" title="Default" Trace="true"%>

当你点击删除按钮,你会看到类似以下代码

 

@EngineerID	0.0120328883259235	0.001463
8	0.0120643582299198	0.000031
@original_ServerLogicalID	0.0120823198911414	0.000018
186	0.0121010365391007	0.000019
@original_ServerName	0.0121185931160761	0.000018
SERVER1	0.012136122102174	0.000018

现在你能够看到每个被传递的参数.如果它们哪些和你的存储过程指定的参数不匹配就不要传递给存储过程,参数的传递是建立在查询命令上的,因此如果你不能修改存储过程的参数,那就像这样修改查询语句:

Select ServerName as original_ServerName, .....

更新于 2.14.06

如果你使用的是objectDataSource,那么查找关键词和值的语法会有所不同.下面的例子是用于查看一个已知名的值.我还没有做过枚举所有的值.

Protected Sub objectDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs) Handles objectDataSource1.Deleting
Dim paramsFromPage As IDictionary = e.InputParameters

Trace.Write("original_AppID:" & paramsFromPage("original_AppID").ToString)
Trace.Write("ServiceID:" & paramsFromPage("ServiceID").ToString)
End Sub

译者注:经过实践研究证明,不只gridView的datakeynames会自动传递参数,在gridView中使用了
<%# bind("xxx") %>也会引起自动传递,所以大家写代码的时候醒目点