即使提供了参数，过程也需要参数

I saw that some others faced this similar problem. I have read and checked the question titled Procedure expects parameter which was not supplied. I thought it would solve my problem but I was wrong :( I did check the steps that were advised there with no luck. Here is my code:

我看到其他一些人遇到了类似的问题。我已阅读并检查了题为“过程预期未提供的参数”的问题。我认为它可以解决我的问题,但我错了:(我确实检查了那里建议没有运气的步骤。这是我的代码:

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;

Stored Procedure is:

存储过程是:

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

While debugging, i found the value of nBillID = 1.0 and sSerialNo='B201200001'. But when trying to execute the ExecuteNonQuery command, it gives the exception:

在调试时,我发现nBillID = 1.0和sSerialNo ='B201200001'的值。但是在尝试执行ExecuteNonQuery命令时,它会给出异常:

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

“程序'usp_PettyCash_AddBillInfo'需要参数'@BillID',这是未提供的。”

I can't find any workarounds. Please help. Sorry for re-asking the question.

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

As a workaround, I used this:

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

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() + "')";

Although I did not like this type of coding, but this works.

虽然我不喜欢这种类型的编码,但这种方法有效。

1 个解决方案

#1

Try adding:

oOleDbCommand.CommandType = CommandType.StoredProcedure;

I believe without this, your parameter values are supposed to be added inline (like an inline SQL statement). E.g:

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

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

So your adding your parameters to the command, but they are not in the CommandText, so they're not being applied anywhere.

因此,您将参数添加到命令中,但它们不在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.

要调用存储过程,请将Command对象的CommandType设置为StoredProcedure。将CommandType设置为StoredProcedure后,可以使用Parameters集合来定义参数,如以下示例所示。

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

#1