RE: weird CFQuery problem
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
RE: weird CFQuery problem
You don't need those double parens around your where clause. Try this. 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 -Original Message- From: Snyder, Jason [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 10: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 - __ Why Share? Dedicated Win 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=coldfusionc 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
RE: weird CFQuery problem
Bingo! -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 10:11 AM To: CF-Talk Subject: RE: weird CFQuery problem 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 - __ 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=coldfusiona 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
