asp.net - 即使提供了参数，过程也需要参数

Question

我看到其他一些人也面临着类似的问题。我已阅读并检查了题为 Procedure expects parameter which was not supplied 的问题.我认为它会解决我的问题，但我错了 :( 我确实检查了那里建议的步骤，但没有运气。这是我的代码:

oOleDbCommand.CommandText = "usp_PettyCash_AddBillInfo";
oOleDbCommand.Parameters.Add("@BillID", OleDbType.BigInt).Value = nBillID;
oOleDbCommand.Parameters.Add("@SerialNo", OleDbType.VarChar).Value = sSerialNo;
oOleDbCommand.Parameters.Add("@UniqueID", OleDbType.VarChar).Value = oInputBill[0].UniqueID.ToString();
oOleDbCommand.Parameters.Add("@BilledWeekDate", OleDbType.Date).Value = oInputBill[0].BilledWeekDate;
oOleDbCommand.Parameters.Add("@BilledWeekNo", OleDbType.VarChar).Value = oInputBill[0].BilledWeekNo.ToString();
oOleDbCommand.Parameters.Add("@SettledWeekDate", OleDbType.Date).Value = oInputBill[0].SettledWeekDate;
oOleDbCommand.Parameters.Add("@SettledWeekNo", OleDbType.VarChar).Value = oInputBill[0].SettledWeekNo;
oOleDbCommand.Parameters.Add("@BillStatus", OleDbType.VarChar).Value = oInputBill[0].BillStatus.ToString();
oOleDbCommand.Parameters.Add("@UpdatedBy", OleDbType.VarChar).Value = oInputBill[0].UpdatedBy;
oOleDbCommand.Parameters.Add("@UpdateDate", OleDbType.Date).Value = oInputBill[0].UpdateDate;

存储过程是:

CREATE PROCEDURE usp_PettyCash_AddBillInfo 
(
    @BillID bigint,
    @SerialNo varchar(50),
    @UniqueID varchar(50),
    @BilledWeekDate datetime,
    @BilledWeekNo varchar(50),
    @SettledWeekDate datetime,
    @SettledWeekNo varchar(50),
    @BillStatus varchar(50),
    @UpdatedBy varchar(50),
    @UpdateDate datetime 
)
AS

BEGIN  
    INSERT INTO t_BillInfo (BillID, SerialNo, UniqueID, BilledWeekDate, BilledWeekNo, SettledWeekDate, SettledWeekNo, BillStatus, UpdatedBy, UpdateDate) 
    VALUES (@BillID, @SerialNo, @UniqueID, @BilledWeekDate, @BilledWeekNo, @SettledWeekDate, @SettledWeekNo, @BillStatus, @UpdatedBy, @UpdateDate) 
END 

GO

在调试时，我发现 nBillID = 1.0 的值和 sSerialNo='B201200001' .但是当试图执行 ExecuteNonQuery 时命令，它给出了异常(exception):

"Procedure 'usp_PettyCash_AddBillInfo' expects parameter '@BillID', which was not supplied."

我找不到任何解决方法。请帮忙。抱歉重新提问。

作为一种解决方法，我使用了这个:

oOleDbCommand.CommandText = "INSERT INTO t_BillInfo (BillID, SerialNo, UniqueID, BilledWeekDate, BilledWeekNo, SettledWeekDate, SettledWeekNo, BillStatus, UpdatedBy, UpdateDate)"+
" VALUES (" + nBillID + ", '" + sSerialNo + "', '" + oInputBill[0].UniqueID.ToString() + "','" + oInputBill[0].BilledWeekDate.ToShortDateString() + "', '" + oInputBill[0].BilledWeekNo + "', '" + oInputBill[0].SettledWeekDate.ToShortDateString() + "', '" + oInputBill[0].SettledWeekNo + "', '" + oInputBill[0].BillStatus.ToString() + "', '" + oInputBill[0].UpdatedBy + "', '" + oInputBill[0].UpdateDate.ToShortDateString() + "')";

虽然我不喜欢这种类型的编码，但这是有效的。

Answer 1

尝试添加:

oOleDbCommand.CommandType = CommandType.StoredProcedure;

我相信没有这个，你的参数值应该被内联添加(就像内联 SQL 语句)。例如:

oOleDbCommand.CommandText = "usp_PettyCash_AddBillInfo @BillID, @SerialNo...";

因此，您将参数添加到命令中，但它们不在 CommandText 中，因此它们不会应用于任何地方。

To call a stored procedure, set the CommandType of the Command object to StoredProcedure. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters, as in the following example.

http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.71).aspx