Syntax Error in INSERT INTO or SELECT statement
Posted by zz-James Moir on 21 June 2016 04:06 PM
You receive a syntax error when performing an INSERT or SELECT, but the syntax appears correct. An example of the error you may receive is shown below:
Error Occurred While Processing Request Error Executing Database Query. Syntax error in INSERT INTO statement. The error occurred in D:\vhosts\domain.co.nz\httpdocs\insertdb.cfm: line 97 95 : <cfelse> 96 : NULL 97 : </cfif> 98 : ) 99 : </cfquery> SQL INSERT INTO table (category, type, address , password) VALUES ( 'Health and Fitness' , 'Company Name', 'Address', 'password123') DATASOURCE datasource VENDORERRORCODE 3092
ColdFusion has a list of reserved keywords, which are typically part of the SQL language and are not normally used for names of columns or tables. In the example error above, password is a reserved keyword, so the insert fails. If you use these reserved keywords you must escape them. You may not have encountered this issue with earlier versions of ColdFusion, which allowed the use of some non-escaped reserved keywords.
A list of reserved keywords can be found at:
Escape reserved keyword for a column name or table name by enclosing them in square brackets.
INSERT INTO table (category, type, address, [password]) VALUES ( 'Health and Fitness', 'Company Name', 'Address', 'password123')
However a better solution is to avoid using reserved keywords, by using a naming convention for tables and columns which avoid conflicts.
The following link discusses naming conventions for database objects: