• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

A question about the CFQUERY tag

I am currently using ColdFusion Express and have created a form with a large editbox on it to allow a user to enter an SQL query.  This form passes the query to a CFM file called query.

In QUERY.CFM I use the following code :

<CFQUERY NAME="UserQuery" DATASOURCE="HRExpress">
   #Form.SQLQuery#
</CFQUERY>

It works fine until I enter the following query :

     SELECT FirstName, LastName, StartDate, Salary, Contract
     FROM Employees
     WHERE LastName= 'Allaire'

When I do this I get the following error :

Error Diagnostic Information

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression 'LastName= ''Allaire'''.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (16:1) to (16:49).

Date/Time: 11/05/99 11:48:56
Browser: Mozilla/4.04 [en] (Win95; I)
Remote Address: 127.0.0.1
HTTP Referer: http://127.0.0.1/john/query.htm
Template: c:\program files\apache group\apache\htdocs\john\query.cfm

However when It works if I write the following into the CFM file :

<CFQUERY name="EmpList" datasource="HRApp">
     SELECT FirstName, LastName, StartDate, Salary, Contract
     FROM Employees
     WHERE LastName= 'Allaire'
</CFQUERY>

Is there anyway to get it to work when a form passes an SQL query to the CFM file ?

I would be grateful for all help offered regarding this.

Thanks in advance

John Clarke
0
johnclarke
Asked:
johnclarke
1 Solution
 
BrigidCommented:
You'll want to add in the ending semicolon after the WHERE statement. I don't know if it'll solve your problem,(hey, stranger things have happened!) but it'll make the SQL code more correct.

     SELECT FirstName, LastName, StartDate, Salary, Contract
     FROM Employees
     WHERE LastName= 'Allaire';
0
 
johnclarkeAuthor Commented:
Already tried that.  Unfortunately it does not work but thanks for trying to help!

John Clarke
0
 
dapperryCommented:
Just use the PreserveSingleQuotes function to keep the quotes in the string.  So your code would be like:

<CFQUERY NAME="UserQuery" DATASOURCE="HRExpress">
#PreserveSingleQuotes(Form.SQLQuery)#
</CFQUERY>

Let me know if you have any questions.

:) dapperry
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now