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

user3784531

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.

Steve

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, "

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

From Dev

Dapper and Oracle parametrized query - ORA-01036: illegal variable name/number

From Dev

Python script to move data from a SQL server db to an oracle db keeps giving 'ORA-01036: illegal variable name/number'

From Dev

ORA-01036: illegal variable name/number - oci_bind_by_name

From Dev

ORA-01036: illegal variable name/number when running query through nodejs

From Dev

python function incrementing variable in list for no obvious reason

From Dev

ORA-01036 after rewriting an Oracle insert command

From Dev

$_GET variable functions differently in two controllers for no obvious reason (Yii)

From Dev

Geeting error ORA-01036 while executing Oracle Procedure using C#

From Dev

such element exception for no obvious reason

From Dev

Is there an obvious reason this loops endlessly?

From Dev

JSF Validation fails for no (obvious) reason

From Dev

QObject::findChild() returns None without obvious reason

From Dev

R expression results in NaN for no obvious reason

From Dev

HashMap keeps returning null value for no obvious reason

From Dev

Systemd stopped docker daemon for no obvious reason

From Dev

Updating Oracle database throws illegal variable name/number exception

From Dev

python cx_Oracle Bind illegal variable name

From Dev

Oracle "ORA-01008" Error. Variable Not Bound

From Dev

"The input line is too long" error in a very basic script for no obvious reason

From Dev

Java 8 stream unpredictable performance drop with no obvious reason

From Dev

Birthday guesser in Java, code lines are skipped for no obvious reason

From Dev

Web application throwing java.net.SocketException for no obvious reason

From Dev

Vagrant provision fails to execute the next script without an obvious reason why

From Dev

Suddenly connection via wpa_supplicant fails without obvious reason

From Dev

OutOfMemoryError: unable to create new native thread with no obvious reason

From Dev

onActivityResult never gets called. No obvious reason why

From Dev

My application chain-reboots for no obvious reason, then crashes

From Dev

Oracle Sql illegal year

Related Related

  1. 1

    cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

  2. 2

    Dapper and Oracle parametrized query - ORA-01036: illegal variable name/number

  3. 3

    Python script to move data from a SQL server db to an oracle db keeps giving 'ORA-01036: illegal variable name/number'

  4. 4

    ORA-01036: illegal variable name/number - oci_bind_by_name

  5. 5

    ORA-01036: illegal variable name/number when running query through nodejs

  6. 6

    python function incrementing variable in list for no obvious reason

  7. 7

    ORA-01036 after rewriting an Oracle insert command

  8. 8

    $_GET variable functions differently in two controllers for no obvious reason (Yii)

  9. 9

    Geeting error ORA-01036 while executing Oracle Procedure using C#

  10. 10

    such element exception for no obvious reason

  11. 11

    Is there an obvious reason this loops endlessly?

  12. 12

    JSF Validation fails for no (obvious) reason

  13. 13

    QObject::findChild() returns None without obvious reason

  14. 14

    R expression results in NaN for no obvious reason

  15. 15

    HashMap keeps returning null value for no obvious reason

  16. 16

    Systemd stopped docker daemon for no obvious reason

  17. 17

    Updating Oracle database throws illegal variable name/number exception

  18. 18

    python cx_Oracle Bind illegal variable name

  19. 19

    Oracle "ORA-01008" Error. Variable Not Bound

  20. 20

    "The input line is too long" error in a very basic script for no obvious reason

  21. 21

    Java 8 stream unpredictable performance drop with no obvious reason

  22. 22

    Birthday guesser in Java, code lines are skipped for no obvious reason

  23. 23

    Web application throwing java.net.SocketException for no obvious reason

  24. 24

    Vagrant provision fails to execute the next script without an obvious reason why

  25. 25

    Suddenly connection via wpa_supplicant fails without obvious reason

  26. 26

    OutOfMemoryError: unable to create new native thread with no obvious reason

  27. 27

    onActivityResult never gets called. No obvious reason why

  28. 28

    My application chain-reboots for no obvious reason, then crashes

  29. 29

    Oracle Sql illegal year

HotTag

Archive