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>
2
Replies
AdChoices