Procedure expects parameter even though the parameter is provided

Question

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:

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

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.

Answer 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:

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.

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