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
johnclarkeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.