replace
strSQLwhere
with
preserveSingleQuotes(strSQLwhere)
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Snyder, Jason [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 15, 2002 9:57 AM
To: CF-Talk
Subject: weird CFQuery problem
I am generating an SQL statement partially inside of a CFQuery tag and
partially in CFScript. When I try to execute the query w/ a WHERE
clause,
it bombs out. If I try w/o a WHERE clause it is happy. If I try w/o a
WHERE clause and w/ an ORDER BY clause it is happy. The error message
I get
when it bombs is:
-------------------------------------
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
expression
'((qryShipSearch.SalesNumber LIKE ''%01A778%''))'.
The error occurred while processing an element with a general
identifier of
(CFQUERY), occupying document position (101:1) to (101:73).
-------------------------------------
If I use CFOutput and copy/paste to print the whole SQL statement to
the
screen, go into source view of the output, copy that SQL statement into
another query and then run it, it works. Here is the query that bombs:
-------------------------------------
<cfquery name="GetResults" datasource="#session.DSN_Ship#"
dbtype="ODBC">
SELECT #scrTable#.ActShipDate, #scrTable#.RPO_Num,
#scrTable#.CustPartNum, #scrTable#.TRW_PartNum, #scrTable#.SerialNum,
#scrTable#.SalesNumber, #scrTable#.ShippingRequestNum
FROM #scrTable#
#strSQLWhere#
#strSQLOrder#;
</cfquery>
-------------------------------------
Here is the one that works:
-------------------------------------
<cfquery name="GetResults" datasource="#session.DSN_Ship#"
dbtype="ODBC">
SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num,
qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum,
qryShipSearch.SerialNum, qryShipSearch.SalesNumber,
qryShipSearch.ShippingRequestNum
FROM qryShipSearch WHERE ((qryShipSearch.SalesNumber LIKE
'%01A778%')) ;
</cfquery>
-------------------------------------
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
