过程或函数在通知 c# 时指定了太多参数
本文关键字:太多 参数 函数 通知 过程 | 更新日期: 2023-09-27 18:37:07
SqlCommand cmmd = new SqlCommand("INSERT into Vows(MemberID, VowAmount, VowDate, Description) Values (@MemberID, @vowAmount, @vowDate, @vowDesc)", con);
SqlCommand storedprocedure = new SqlCommand("UPDATEINCOME", con);
storedprocedure.CommandType = CommandType.StoredProcedure;
storedprocedure.Parameters.Add(new SqlParameter("@IncomeDate", SqlDbType.Date)).Value = vowdateTime.Text;
SqlCommand insertStoredProcedure = new SqlCommand("InsertIncomeDate", con);
insertStoredProcedure.CommandType = CommandType.StoredProcedure;
insertStoredProcedure.Parameters.Add(new SqlParameter("@IncomeDate", SqlDbType.Date)).Value = vowdateTime.Text;
insertStoredProcedure.Parameters.Add("@IncomeDate", SqlDbType.Date).Direction = ParameterDirection.Input;
insertStoredProcedure.Parameters.Add("@IncomeDate", SqlDbType.Date).Direction = ParameterDirection.Input;
这是我的代码,我收到错误:
过程或函数指定的参数过多
cmmd.Parameters.AddWithValue("@MemberID", vowMemID.Text);
cmmd.Parameters.AddWithValue("@vowAmount", vowAmount.Text);
cmmd.Parameters.AddWithValue("@vowDate", vowdateTime.Text);
cmmd.Parameters.AddWithValue("@vowDesc", vowDescBox.Text);
cmmd.ExecuteNonQuery();
insertStoredProcedure.ExecuteNonQuery(); // error occurs here
insertStoredProcedure.Parameters.Clear();
storedprocedure.ExecuteNonQuery(); // and here
storedprocedure.Parameters.Clear();
这些是存储过程:
ALTER PROCEDURE [dbo].[UPDATEINCOME]
(@IncomeDate date, @VowAmount money)
AS
Declare @TitheAmount money
Declare @OfferingAmount money
Declare @IncomeTotal money
IF NOT EXISTS (Select VowDate from Vows where VowDate = @IncomeDate)
BEGIN
set @VowAmount = '0'
END
ELSE
BEGIN
set @VowAmount = (Select VowAmount from Vows where VowDate = @IncomeDate)
END
IF NOT EXISTS (Select OfferingDate from Offering where OfferingDate = @IncomeDate)
BEGIN
set @OfferingAmount = '0'
END
ELSE
BEGIN
set @OfferingAmount = (Select OfferingAmount from Offering where OfferingDate = @IncomeDate)
END
IF NOT EXISTS (Select TitheDate from Tithe where TitheDate = @IncomeDate)
BEGIN
set @TitheAmount = '0'
END
ELSE
BEGIN
Set @TitheAmount = (Select TitheAmount from Tithe where TitheDate = @IncomeDate)
END
Set @IncomeTotal = @TitheAmount + @VowAmount + @OfferingAmount
UPDATE IncomeReport
SET Amount = @IncomeTotal
WHERE IncDATE = @IncomeDate
UPDATEINCOME
存储过程更新IncomeReport
表。它将此处指定为IncomeDate
的特定日期的所有金额相加,并将总计插入IncomeReports
。
同时,INsertDate
存储过程检查IncomeReport
表是否具有"cmmd"命令打算在其下插入金额的日期。如果没有,它也插入从IncomeDate
获取的日期。然后,这允许UPDATEINCOME
将金额插入到日期@IncomeDate
的表中。
您正在添加两个@IncomeDate
参数。不要那样做;p
storeproc.Parameters.Add(new SqlParameter("@IncomeDate", SqlDbType.Date)).Value = vowdateTime.Text;
...
storeproc.Parameters.Add("@IncomeDate", SqlDbType.Date).Direction = ParameterDirection.Input;
并且(正如斯里拉姆在评论中指出的那样):
storedprocedure.Parameters.Add(new SqlParameter("@IncomeDate", SqlDbType.Date)).Value = vowdateTime.Text;
...
storedprocedure.Parameters.Add("@IncomeDate", SqlDbType.Date).Direction = ParameterDirection.Input;
如果需要,您可以捕获Parameters.Add
的结果:
var p = storeproc.Parameters.Add(new SqlParameter("@IncomeDate", SqlDbType.Date));
p.Value = vowdateTime.Text;
p.Direction = ParameterDirection.Input;
但无论如何Input
是默认设置。
不过,坦率地说,你让这很难。我在这里强烈推荐像"dapper"这样的工具:
DateTime when = ...
decimal amount = ...
connection.Execute("UPDATEINCOME",
new { IncomeDate = when, VowAmount = amount },
commandType: CommandType.StoredProcedure);
工作完成。