过程或函数指定的参数太多 [英] Procedure or function has too many arguments specified
问题描述
我在过程或函数myStoreProcNameHere有指定的参数太多。
下面是我的商店proc和SQL数据源$ C $ C。我使用表格视图编辑数据。
帮助? (
存储过程
ALTER PROCEDURE UpdateTwoTable
(
@ID INT,
@UserID VARCHAR(10)
@Pass VARCHAR(50)
@enabled INT,
@Permission INT,
@rank INT,
@fname VARCHAR(50)
@LName VARCHAR(50)
@phone VARCHAR(50)
@ EMAIL1 VARCHAR(50)
@电子邮件2 VARCHAR(50)
)ASBEGIN TRANSACTIONUPDATE tbl_user_login设置用户ID = @UserID,通= @Pass,启用= @enabled,权限= @Permission,排名= @rank WHERE ID = @IDIF @@ ERROR<> 0
开始
ROLLBACK
返回
结束UPDATE tbl_user_profile SET FName参数= @fname,LName的= @LName,手机= @phone,EMAIL1 = @ EMAIL1,电子邮件2 = @电子邮件2 WHERE ID = @IDIF @@ ERROR<> 0
开始
ROLLBACK
返回
结束承诺
ASP.NET SQL数据源
< ASP:SqlDataSource的ID =SqlDataSource1=服务器
的ConnectionString =下;%$的ConnectionStrings:DBConnString%>中
的SelectCommand =SELECT tbl_user_login.ID,tbl_user_login.UserID,tbl_user_login.Pass,tbl_user_login.Enabled,tbl_user_login.Permission,tbl_user_login.Rank,tbl_user_profile.ID AS表达式1,tbl_user_profile.FName,
tbl_user_profile.LName,tbl_user_profile.Phone,tbl_user_profile.Email1,tbl_user_profile.Email2 FROM tbl_user_login INNER JOIN tbl_user_profile ON tbl_user_login.ID = tbl_user_profile.ID
更新命令=UpdateTwoTableUpdateCommandType =StoredProcedure的>
< UpdateParameters>
< ASP:参数名称=ID/>
< ASP:参数名称=用户名/>
< ASP:参数名称=通行证/>
< ASP:参数名称=已启用/>
< ASP:参数名称=权限/>
< ASP:参数名称=等级/>
< ASP:参数名称=FName参数/>
< ASP:参数名称=LName的/>
< ASP:参数名称=电话/>
< ASP:参数名称=EMAIL1/>
< ASP:参数名称=电子邮件2/>
< / UpdateParameters>
< / ASP:SqlDataSource的>
我跑进此错误信息,当我从调用循环(的foreach)内的存储过程之前。
这是我收到的异常的原因是由于这样的事实,我没有清除参数的循环每次迭代后。
例如:
这code抛出:
使用(CMD的SqlCommand =新的SqlCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText =MySproc;
的foreach(在myClasses MyClass的三)
{
cmd.Parameters.AddWithValue(@ VAL1,c.val1);
cmd.Parameters.AddWithValue(@ val2的c.val2);
VAR的结果= MyDbManager.instance.ExecuteScalarQuery(CMD);
}
}
此修复程序是明确的参数:
使用(CMD的SqlCommand =新的SqlCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText =MySproc;
的foreach(在myClasses MyClass的三)
{
cmd.Parameters.AddWithValue(@ VAL1,c.val1);
cmd.Parameters.AddWithValue(@ val2的c.val2);
VAR的结果= MyDbManager.instance.ExecuteScalarQuery(CMD);
cmd.Parameters.Clear();
}
}
I'm having Procedure or function "myStoreProcNameHere" has too many arguments specified.
Below is my Store proc and SQL Data Source Code. I'm using Grid View to edit data.
Help? :(
Store Procedure
ALTER PROCEDURE UpdateTwoTable
(
@ID int,
@UserID varchar(10),
@Pass varchar(50),
@Enabled int,
@Permission int,
@Rank int,
@FName varchar(50),
@LName varchar(50),
@Phone varchar(50),
@Email1 varchar(50),
@Email2 varchar(50)
) AS
BEGIN TRANSACTION
UPDATE tbl_user_login SET UserID = @UserID, Pass = @Pass, Enabled = @Enabled, Permission = @Permission, Rank = @Rank WHERE ID = @ID
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
UPDATE tbl_user_profile SET FName = @FName, LName = @LName, Phone = @Phone, Email1 = @Email1, Email2 = @Email2 WHERE ID = @ID
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
COMMIT
ASP.NET SQL Data Source
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DBConnString %>"
SelectCommand="SELECT tbl_user_login.ID, tbl_user_login.UserID, tbl_user_login.Pass, tbl_user_login.Enabled, tbl_user_login.Permission, tbl_user_login.Rank, tbl_user_profile.ID AS Expr1, tbl_user_profile.FName,
tbl_user_profile.LName, tbl_user_profile.Phone, tbl_user_profile.Email1, tbl_user_profile.Email2 FROM tbl_user_login INNER JOIN tbl_user_profile ON tbl_user_login.ID = tbl_user_profile.ID"
UpdateCommand="UpdateTwoTable" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="UserID"/>
<asp:Parameter Name="Pass"/>
<asp:Parameter Name="Enabled"/>
<asp:Parameter Name="Permission"/>
<asp:Parameter Name="Rank"/>
<asp:Parameter Name="FName"/>
<asp:Parameter Name="LName"/>
<asp:Parameter Name="Phone"/>
<asp:Parameter Name="Email1"/>
<asp:Parameter Name="Email2"/>
</UpdateParameters>
</asp:SqlDataSource>
I've ran into this error message before when I was calling a stored procedure from within a loop (foreach).
The cause of the exception that I received was due to the fact that I wasn't clearing the Parameter's after each iteration of the loop.
For example: This code throws:
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "MySproc";
foreach (MyClass c in myClasses)
{
cmd.Parameters.AddWithValue("@val1", c.val1);
cmd.Parameters.AddWithValue("@val2", c.val2);
var result = MyDbManager.instance.ExecuteScalarQuery(cmd);
}
}
The fix was to clear the parameters:
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "MySproc";
foreach (MyClass c in myClasses)
{
cmd.Parameters.AddWithValue("@val1", c.val1);
cmd.Parameters.AddWithValue("@val2", c.val2);
var result = MyDbManager.instance.ExecuteScalarQuery(cmd);
cmd.Parameters.Clear();
}
}
这篇关于过程或函数指定的参数太多的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!