Skip to main content
E
Eric_Dahlenburg
Inspiring
July 21, 2009
Question

That old ORA-00911: invalid character Problem...

  • July 21, 2009
  • 1 reply
  • 4615 views

Hey Folks,

I keep getting the same ORA-00911 error and I have tried everything. Maybe one of the forum members can spot the error.

It appears to be a problem in the WHERE clause. The feilds are as follows:

Part_Number is a VARCHAR2 (20 Byte)

NEMS_Tag is a VARCHAR2 (20 Byte)

Contract_Num is a NUMBER(2,0)

Quantity is a NUMBER(2,0)

Here is the code in question:

<!--- Start of Change Quantity Query Stuff   --->
<cfif IsDefined("form.Change_Quan_PN") AND IsDefined("form.Change_Quan_Con")>
  <cfquery datasource="ilmf_parts_dev">
    Update MAIN_TABLE
    SET Quantity = #FORM.CHANGE_QUAN_QUAN#
    WHERE PART_NUMBER = '#FORM.CHANGE_QUAN_PN#' AND NEMS_TAG = '#FORM.CHANGE_QUAN_NEM#' AND CONTRACT_NUM = #FORM.CHANGE_QUAN_CON# ;
  </cfquery>
  </cfif>
<!--- End of Change Quantity Query Stuff   --->

Error  Executing Database Query.

[Macromedia][Oracle JDBC  Driver][Oracle]ORA-00911: invalid character
The  error occurred in E:\papps\fl\ilmf_parts_dev\Admin.cfm: line  184
Called from E:\papps\fl\ilmf_parts_dev\Admin.cfm: line  1
Called from E:\papps\fl\ilmf_parts_dev\Admin.cfm: line  184
Called from E:\papps\fl\ilmf_parts_dev\Admin.cfm: line  1
182 :     Update MAIN_TABLE 
183 :     SET Quantity = #FORM.CHANGE_QUAN_QUAN#
184 :     WHERE PART_NUMBER = '#FORM.CHANGE_QUAN_PN#' AND NEMS_TAG = '#FORM.CHANGE_QUAN_NEM#' AND CONTRACT_NUM = #FORM.CHANGE_QUAN_CON# ;
185 :   </cfquery>
186 :   </cfif>
SQLSTATE  42000
SQL   Update MAIN_TABLE SET  Quantity = 99 WHERE PART_NUMBER = '05W440' AND NEMS_TAG = 'None' AND  CONTRACT_NUM = 1 ;
VENDORERRORCODE  911
DATASOURCE  ilmf_parts_dev

Here is the complete code if anyone needs it. Thank You in advance for your help,

Eric

<cfset _emp="">
<cfset _emp="#CGI.Auth_User#"> <!--- check user of application ---><!--- Lookup current user in Person Plural Table --->
<cfquery name="q_emp" datasource="person_admin" blockfactor="1">
SELECT PERSON_PLURAL.EMPLID, PERSON_PLURAL.SEARCH_NT_ID, PERSON_PLURAL.FULL_NAME,
PERSON_PLURAL.DEPTID
FROM APPL_WEB.PERSON_PLURAL
WHERE PERSON_PLURAL.SEARCH_NT_ID = <cfqueryparam VALUE="#variables._emp#">
</cfquery>

<cfquery name="role" datasource="ilmf_parts_dev">
SELECT * FROM REGISTRATION
WHERE FULL_NAME = '#q_EMP.FULL_NAME#'
</cfquery>

<CFIF role.User_Group NEQ "Admin">
<!--- If User Group does not contain "Admin", then next line --->
    <cflocation
url="gilmore.cfm"
addtoken="false"
/>
 
</CFIF>
<!--- If valid User Group Data, Then next line --->


<cfquery name="Issue_Log" datasource="ilmf_parts_dev">
SELECT *
FROM ISSUE
ORDER BY "ENTRY_DATE" ASC
</cfquery>

<cfif role.User_Group EQ ""> <!--- Is there Session Data? "No Data"=true  Next line if true--->
    </strong><span class="style1">You are Logged-In as: <strong><cfoutput>#q_EMP.FULL_NAME#</strong></cfoutput> and have <strong>READ ONLY </strong> Privilages. </span>
   
<cfelse>
   
    <p align="left" class="style1">You are logged on as: <strong><cfoutput>"#q_EMP.FULL_NAME#"</strong>  with<strong> #role.User_Group# </strong>Privileges</cfoutput></p>
</cfif><p>


<!--- Start of Add Inventory Query Stuff   --->
<cfif IsDefined("Form.Add_Inv_PN") AND IsDefined("Form.Add_Inv_NEMs")>
<cftry>
  <cfquery datasource="ilmf_parts_dev">
    INSERT INTO MAIN_TABLE (PART_NUMBER, QUANTITY, NEMS_TAG, CONTRACT_NUM, MNFR_NUM, NOMEN_NUM, VENDOR_NUM, IMAGE_FILE, SPECIFIC_DESCRIPTION, LOCATION_ID)
    VALUES (<cfif IsDefined("Form.Add_Inv_PN") AND #Form.Add_Inv_PN# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_PN#" cfsqltype="cf_sql_clob" maxlength="20">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_Quan") AND #Form.Add_Inv_Quan# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_Quan#" cfsqltype="cf_sql_clob" maxlength="2">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_NEMs") AND #Form.Add_Inv_NEMs# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_NEMs#" cfsqltype="cf_sql_clob" maxlength="8">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_Contract") AND #Form.Add_Inv_Contract# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_Contract#" cfsqltype="cf_sql_clob" maxlength="2">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_MNFR") AND #Form.Add_Inv_MNFR# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_MNFR#" cfsqltype="cf_sql_clob" maxlength="2">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_Nomen") AND #Form.Add_Inv_Nomen# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_Nomen#" cfsqltype="cf_sql_clob" maxlength="2">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_Vendor") AND #Form.Add_Inv_Vendor# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_Vendor#" cfsqltype="cf_sql_clob" maxlength="2">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_Image") AND #Form.Add_Inv_Image# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_Image#" cfsqltype="cf_sql_clob" maxlength="20">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_SDes") AND #Form.Add_Inv_SDes# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_SDes#" cfsqltype="cf_sql_clob" maxlength="60">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.Add_Inv_Loc") AND #Form.Add_Inv_Loc# NEQ "">
    <cfqueryparam value="#Form.Add_Inv_Loc#" cfsqltype="cf_sql_clob" maxlength="10">
    <cfelse>
    ''
  </cfif>
    )
  </cfquery>
  <cfcatch type="Database">
    <h2 align="center"><span class="style2">
  </span><span class="style1">You have entered a combination of Part Number, NEMs Tag, and Contract Name that is already entered in the database. If you are attempting to change a value for an existing combination of Part Number, NEMs Tag, and Contract Name, you must first delete the existing record before entering the new record.</span>
  </h2>
    </cfcatch>
    </cftry>
  </cfif>
<!--- End of Add Inventory Query Stuff   --->    

<!--- Get number of record for Vendor_Num Column to establish Add Vendor Index --->
<cfquery name="Vendor_Num" datasource="ilmf_parts_dev">
SELECT MAX(VENDOR_NUM) AS HIGH_NUM FROM VENDOR_TABLE
</cfquery>

<!--- Start of Add Vendor Query Stuff   --->
<cfif IsDefined("FORM.V_NAME") AND IsDefined("FORM.V_ADDRESS")>
  <cfquery datasource="ilmf_parts_dev">
    INSERT INTO VENDOR_TABLE (Vendor_Num, Vendor_Name, Vendor_Address, Vendor_City, Vendor_State, Vendor_Zip, Vendor_Phone, Vendor_Fax, OUR_CUSTOMER_NUM)
    VALUES (#Vendor_Num.HIGH_NUM + 1#,
  <cfif IsDefined("Form.V_Name") AND #Form.V_Name# NEQ "">
    <cfqueryparam value="#Form.V_Name#" cfsqltype="cf_sql_clob" maxlength="30">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_Address") AND #Form.V_Address# NEQ "">
    <cfqueryparam value="#Form.V_Address#" cfsqltype="cf_sql_clob" maxlength="30">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_City") AND #Form.V_City# NEQ "">
    <cfqueryparam value="#Form.V_City#" cfsqltype="cf_sql_clob" maxlength="30">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_State") AND #Form.V_State# NEQ "">
    <cfqueryparam value="#Form.V_State#" cfsqltype="cf_sql_clob" maxlength="2">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_Zip") AND #Form.V_Zip# NEQ "">
    <cfqueryparam value="#Form.V_Zip#" cfsqltype="cf_sql_clob" maxlength="5">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_Phone") AND #Form.V_Phone# NEQ "">
    <cfqueryparam value="#Form.V_Phone#" cfsqltype="cf_sql_clob" maxlength="13">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_Fax") AND #Form.V_Fax# NEQ "">
    <cfqueryparam value="#Form.V_Fax#" cfsqltype="cf_sql_clob" maxlength="13">
    <cfelse>
    ''
  </cfif>
    ,
  <cfif IsDefined("Form.V_CNumber") AND #Form.V_CNumber# NEQ "">
    <cfqueryparam value="#Form.V_CNumber#" cfsqltype="cf_sql_clob" maxlength="15">
    <cfelse>
    ''
  </cfif>
    )
  </cfquery>
  </cfif>
<!--- End of Add Vendor Query Stuff   --->      


<!--- Start of Change Quantity Query Stuff   --->
<cfif IsDefined("form.Change_Quan_PN") AND IsDefined("form.Change_Quan_Con")>
  <cfquery datasource="ilmf_parts_dev">
    Update MAIN_TABLE
    SET Quantity = #FORM.CHANGE_QUAN_QUAN#
    WHERE PART_NUMBER = '#FORM.CHANGE_QUAN_PN#' AND NEMS_TAG = '#FORM.CHANGE_QUAN_NEM#' AND CONTRACT_NUM = #FORM.CHANGE_QUAN_CON# ;
  </cfquery>
  </cfif>
<!--- End of Change Quantity Query Stuff   --->  


<!--- Start of Add User Query Stuff --->

<!--- If valid User Group Data, Then next line --->

    <cfif IsDefined("FORM.FULL_NAME") AND IsDefined("FORM.USER_GROUP")>
        <cftry>
        <cfquery datasource="ilmf_parts_dev">
  INSERT INTO REGISTRATION (FULL_NAME, USER_GROUP)
VALUES (
      <cfif IsDefined("FORM.FULL_NAME") AND #FORM.FULL_NAME# NEQ "">
        <cfqueryparam value="#FORM.FULL_NAME#" cfsqltype="VARCHAR2" maxlength="20">
     <cfelse>
        ''
      </cfif>
   
  , <cfif IsDefined("FORM.USER_GROUP") AND #FORM.USER_GROUP# NEQ "">
          <cfqueryparam value="#FORM.USER_GROUP#" cfsqltype="VARCHAR2" maxlength="20">
     <cfelse>
        ''
      </cfif>
    )
    </cfquery>
    <cfcatch type="Database">
    <h2 align="center"><span class="style2">
  </span><span class="style1">You have used an NT name that is already in use, please return to the "REGISTER USERS" page and use a different NT Name. If you are trying to change the role(User/ Admin) of an existing user, you must delete the user first and recreate the user again with the new role.</span>
  </h2>
    </cfcatch>
    </cftry>
      </cfif>

<!--- End of Add User Query Stuff  --->

<!--- Start of Delete User Query Stuff --->
<cfif IsDefined("FORM.D_Name") AND #FORM.D_Name# NEQ "">
      <cfquery datasource="ilmf_parts_dev">
      DELETE FROM REGISTRATION
    WHERE Full_Name=<cfqueryparam value="#FORM.D_Name#">
      </cfquery>
</cfif>

<!--- End of Delete Query Stuff --->

<table width="600" border="1" align="center" cellpadding="3" cellspacing="1" bgcolor="#DBDBDB">
  <tr>
    <th scope="col"><h3 class="style1">Manage Users </h3></th>
    <th scope="col"><h3><span class="style1">Manage Inventory </span></h3></th>
    <th scope="col"><h3><span class="style1">Manage Warranty </span></h3></th>
    <th scope="col"><h3><span class="style1">Links </span></h3></th>
  </tr>
  <tr>
    <td><div align="center"><a href="Registration.cfm" class="style1">Register Users</a></div></td>
    <td><div align="center"><a href="Add_Inventory.cfm" class="style1">Add Inventory</a></div></td>
    <td><div align="center"><a href="Add_Vendor.cfm" class="style1">Add Vendor</a></div></td>
    <td><div align="center"><a href="Admin_Options.cfm" class="style1">Admin Options</a></div></td>
  </tr>
  <tr>
    <td><div align="center"><a href="Delete_User.cfm" class="style1">Delete Users</a></div></td>
    <td><div align="center"><a href="Inventory_Dump.cfm" class="style1">Inventory Dump </a></div></td>
    <td><div align="center"><a href="Warranty.cfm" class="style1">Enter Warranty Information</a></div></td>
    <td><div align="center"><a href="gilmore.cfm" class="style1">Serialized Parts Home</a></div></td>
  </tr>
  <tr>
    <td><div align="center"><a href="User_Dump.cfm" class="style1">Registration Dump </a></div></td>
    <td><div align="center"><a href="Issue_Dump.cfm" class="style1">Issued Parts Dump</a></div></td>
    <td><div align="center"><a href="Warranty_Dump.cfm" class="style1">Warranty  Dump</a></div></td>
     <td><div align="center"><a href="Index.cfm" class="style1">Portal Home</a></div></td>
  </tr>
</table><h1 align="center" class="style2"><u><span class="style1">Administrator Page</span></u><br />
</h1>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Administration Page</title>
<style type="text/css">
.style1 {font-family: Arial, Helvetica, sans-serif}
.style2 {font-weight: bold}
.style3 {font-family: Arial, Helvetica, sans-serif; font-weight: bold; }
-->
</style>
</head>

<body>

</body>
</html>

    This topic has been closed for replies.

    1 reply

    E
    Eric_DahlenburgAuthor
    Inspiring
    July 21, 2009

    I re-wrote the Query and it now works. For the benifit of others reading this thread, here is the working query:

    <cfquery datasource="ilmf_parts_dev">
        UPDATE Main_Table
        SET Quantity=
      <cfif IsDefined("FORM.CHANGE_QUAN_QUAN") AND #FORM.CHANGE_QUAN_QUAN# NEQ "">
        <cfqueryparam value="#FORM.CHANGE_QUAN_QUAN#" cfsqltype="cf_sql_numeric">
        <cfelse>
        NULL
      </cfif>
        WHERE Part_Number=
      <cfqueryparam value="#FORM.CHANGE_QUAN_PN#" cfsqltype="cf_sql_clob" maxlength="50">
        AND NEMS_Tag=
      <cfqueryparam value="#FORM.CHANGE_QUAN_NEM#" cfsqltype="cf_sql_clob" maxlength="50">
        AND Contract_num=
      <cfqueryparam value="#FORM.CHANGE_QUAN_CON#" cfsqltype="cf_sql_numeric">
      </cfquery>

    I'm not sure why the original query failed, but here it is if anyone wants to comment.

    Thanks, Eric

    <!--- Start of Change Quantity Query Stuff   --->
    <cfif IsDefined("form.Change_Quan_PN") AND IsDefined("form.Change_Quan_Con")>
      <cfquery datasource="ilmf_parts_dev">
        Update MAIN_TABLE
        SET Quantity = #FORM.CHANGE_QUAN_QUAN#
        WHERE PART_NUMBER = '#FORM.CHANGE_QUAN_PN#' AND NEMS_TAG = '#FORM.CHANGE_QUAN_NEM#' AND CONTRACT_NUM = #FORM.CHANGE_QUAN_CON# ;
      </cfquery>
      </cfif>
    <!--- End of Change Quantity Query Stuff   --->

    T
    TheGame!
    Participant
    July 22, 2010

    Bottomline. Dont put "semicolon" after your SQL statement. This worked fine in  SQL window but not in the coldfusion query.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices