CODEWITHSUNDEEP
asp.netsqloracle-databaseinsert
user3784531
user3784531

Reputation: 13

Oracle ORA-01036 illegal variable name/number for no obvious reason

I've created an INSERT statement being called from a Web Page in a ASP.NET Web Form

I'm getting this: ORA-01036 illegal variable name/number when I try to execute my query. I unfortunately only have Read-Only access to the database itself so I can't test they query by it's lonesome. Below is my entire method, because I'm not sure if the problem is in the query or in the parameters.

 public static String InsertIntoMiscReceiptCashTable(
    String Payeedetail,String LocationCode, 
    String TransactionDate, String CashBookstoreCode, 
    String CashBookstoreSalesTaxCode, 
    String CashOther1DDL, String CashOther2DDl, 
    String CashOther1CommentTxt, String CashOther2CommentTxt,
    String CashCommentTxtBx, String RecapCashCommentTxtBx, 
    String RecapCheckNumCommentTxtBx,
    String RecapTotalCommentTxtBx, String RecapPymtRcvdCommentTxtBx, 
    String RecapChangeCommentTxtBx,
    String SysUserName, Double CashBookstoreAmountTxtBx, 
    Double CashBookstoreSalesTaxAmountTxtBx,
    Double CashOther1AmountTxtBx, Double CashOther2AmountTxtBx, 
    Double RecapCashAmountTxtBx,
    Double RecapCheckNumAmountTxtBx, Double RecapPymtRcvdAmountTxtBx, 
    Double RecapChangeAmountTxtBx,
    Double RecapTotalAmountTxtBx, Double CashTotalPaymentAmountTxtBx
    )
{
        DbLastKeySequence.addLastKeySequence("MISCRECEIPTSCASH.MISCRECEIPTID");
        int MiscReceiptID_lastKeySequence = 
          Convert.ToInt32(DbLastKeySequence.findLastKeySequence("MISCRECEIPTSCASH.MISCRECEIPTID"));

        String NextBatchNumber = ""; String NextReceiptNumber = "";
        DataTable NextValues = 
         DbCashMiscReceiptPg.Get_Description_NextBatchNum_NextReceiptNum(LocationCode);
        DataTableReader reader = NextValues.CreateDataReader();
        while (reader.Read())
        {
            NextBatchNumber = (reader["NEXTBATCHNUMBER"].ToString());
            NextReceiptNumber = (reader["NEXTRECEIPTNUMBER"].ToString());
        }
        reader.Close();

        String CommandText = "INSERT INTO MISCRECEIPTSCASH ( " + 
            "MISCRECEIPTID, " +
            "PAYEENAME, " +
            "TRANSACTIONDATE, " +
            "BATCHNUMBER, " +
            "RECEIPTNUMBER, " +
            "VOIDINDICATOR, " +
            "CASHTYPEPMTCODE1, " +
            "CASHCODE1, " +
            "CASHBOOKSTORECODE, " +
            "CASHSALESTAXCODE, " +
            "CASHOTHERCODE1, " +
            "CASHOTHERCODE2, " +
            "CASHOTHERCOMMENT1, " +
            "CASHOTHERCOMMENT2, " +
            "CHARGEBOOKSTORECODE, " +
            "LOCATIONCODE, " +
            "CASHCOMMENT, " +
            "RECAPCASHCOMMENT, " + 
            "RECAPCHECK1COMMENT, " +
            "RECAPTOTALCOMMENT, " +
            "RECAPPYMTRCVDCOMMENT," +
            "RECAPCHANGECOMMENT, " +
            "INITIALS, " +
            "CASHPAYMENTONACCOUNT1, " +
            "CASHPAYMENTONACCOUNT2, " +
            "CASHBOOKSTORE, " +
            "CASHSALESTAX, " +
            "CASHOTHERAMOUNT1," + 
            "CASHOTHERAMOUNT2, " +
            "CHARGEBOOKSTORE, " +
            "CHARGESALESTAX, " +
            "CHARGEOTHERAMOUNT, " +
            "RECAPCASHAMOUNT, " +
            "RECAPCHECK1AMOUNT,  " +
            "RECAPPYMTRCVDAMOUNT, " +
            "CHANGEAMT, " +
            "TOTALCHARGES, " +
            "TOTALRECAP, " +
            "TOTALPAYMENT, " +
            "GENERATEASCII, " +
            "GENERATEDDATE" +
            ")";

        CommandText += "VALUES ( " +
            ":MISCRECEIPTID, " +
            "':PayeeDetails', " +
            "TO_DATE(:TransactionDate,'yyyy-mm-dd HH24:MI:SS'), " +
            "':BatchNumber', " +
            "':ReceiptNumber', " +
            "'N', " +
            "'01', " +
            "'00012500', " +
            "':CashBookstoreCode', " +
            "':CashBookstoreSalesTaxCode', " +
            "':CashOther1DDL', " +
            "':CashOther2DDL', " +
            "':CashOther1CommentTxt'," +
            "':CashOther2CommentTxt'," +
            "':CashBookstoreCode', " +
            "':LocationCode', " +
            "':CashCommentTxtBx'," + 
            "':RecapCashCommentTxtBx'," + 
            "':RecapCheckNumCommentTxtBx'," + 
            "':RecapTotalCommentTxtBx'," +
            "':RecapPymtRcvdCommentTxtBx'," +
            "':RecapChangeCommentTxtBx'," +
            "':SysUserName'," +
            "0," +
            "0," +
            ":CashBookstoreAmountTxtBx," + 
            ":CashBkstreSalesTaxAmountTxtBx," + 
            ":CashOther1AmountTxtBx," +
            ":CashOther2AmountTxtBx," +
            "0, " +
            "0, " +
            "0, " +
            ":RecapCashAmountTxtBx, " +
            ":RecapCheckNumAmountTxtBx, " +
            ":RecapPymtRcvdAmountTxtBx," +
            ":RecapChangeAmountTxtBx," +
            "0, " +
            ":RecapTotalAmountTxtBx, " +
            ":CashTotalPaymentAmountTxtBx, " +
            "'N', " +
            "TO_DATE(:TransactionDate,'yyyy-mm-dd HH24:MI:SS') " +
            ")";

        cmd = new OracleCommand(CommandText, con);
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.Clear();

        cmd.Parameters.AddWithValue(":MISCRECEIPTID", MiscReceiptID_lastKeySequence);
        cmd.Parameters.AddWithValue(":PayeeDetails", Payeedetail);
        cmd.Parameters.AddWithValue(":TransactionDate", TransactionDate);
        cmd.Parameters.AddWithValue(":BatchNumber", NextBatchNumber);
        cmd.Parameters.AddWithValue(":ReceiptNumber", NextReceiptNumber);
        cmd.Parameters.AddWithValue(":CashBookstoreCode", CashBookstoreCode); // Same as down there
        cmd.Parameters.AddWithValue(":CashBookstoreSalesTaxCode", CashBookstoreSalesTaxCode);
        cmd.Parameters.AddWithValue(":CashOther1DDL", CashOther1DDL);
        cmd.Parameters.AddWithValue(":CashOther2DDL", CashOther2DDl);
        cmd.Parameters.AddWithValue(":CashOther1CommentTxt", CashOther1CommentTxt);
        cmd.Parameters.AddWithValue(":CashOther2CommentTxt", CashOther2CommentTxt);
        //cmd.Parameters.AddWithValue(":CashBookstoreCode", CashBookstoreCode); // Same as up there 
        cmd.Parameters.AddWithValue(":LocationCode", LocationCode);
        cmd.Parameters.AddWithValue(":CashCommentTxtBx", CashCommentTxtBx);
        cmd.Parameters.AddWithValue(":RecapCashCommentTxtBx", RecapCashCommentTxtBx);
        cmd.Parameters.AddWithValue(":RecapCheckNumCommentTxtBx", RecapCheckNumCommentTxtBx);
        cmd.Parameters.AddWithValue(":RecapTotalCommentTxtBx", RecapTotalCommentTxtBx);
        cmd.Parameters.AddWithValue(":RecapPymtRcvdCommentTxtBx", RecapPymtRcvdCommentTxtBx);
        cmd.Parameters.AddWithValue(":RecapChangeCommentTxtBx", RecapChangeCommentTxtBx);
        cmd.Parameters.AddWithValue(":SysUserName", SysUserName);
        cmd.Parameters.AddWithValue(":CashBookstoreAmountTxtBx", CashBookstoreAmountTxtBx);
        cmd.Parameters.AddWithValue(":CashBkstreSalesTaxAmountTxtBx", CashBookstoreSalesTaxAmountTxtBx);
        cmd.Parameters.AddWithValue(":CashOther1AmountTxtBx", CashOther1AmountTxtBx);
        cmd.Parameters.AddWithValue(":CashOther2AmountTxtBx", CashOther2AmountTxtBx);
        cmd.Parameters.AddWithValue(":RecapCashAmountTxtBx", RecapCashAmountTxtBx);
        cmd.Parameters.AddWithValue(":RecapCheckNumAmountTxtBx", RecapCheckNumAmountTxtBx);
        cmd.Parameters.AddWithValue(":RecapPymtRcvdAmountTxtBx", RecapPymtRcvdAmountTxtBx);
        cmd.Parameters.AddWithValue(":RecapChangeAmountTxtBx", RecapChangeAmountTxtBx);
        cmd.Parameters.AddWithValue(":RecapTotalAmountTxtBx", RecapTotalAmountTxtBx);
        cmd.Parameters.AddWithValue(":CashTotalPaymentAmountTxtBx", CashTotalPaymentAmountTxtBx);

        con.Open();
        cmd.ExecuteNonQuery();

        cmd.Parameters.Clear();

        con.Close();
        return NextReceiptNumber;

}// end db insert query

I've checked the names of all of the columns - they're all correct. I've also checked all of the parameter names (copy and pasted) and they're all correct (unless I'm going crazy). I've also checked the datatypes of all of them - again all fine. I've also taken the colons (:) off of all of the parameters when setting their values and still get the error.

If anyone has any idea as to why I keep getting this error, help would be graciously appreciated.

Upvotes: 1

Views: 12936

Answers (1)

Steve
Steve

Reputation: 216293

Not sure if this is the reason of your error, but surely the parameter placeholders should not be enclosed in single quotes. In that way they become literal strings

For example (but not only this)

 "':BatchNumber', "

should be

 ":BatchNumber, "

Upvotes: 5

Related Questions