Searching data that has an apostrophe

[DeZiner]

A field in the database contains for example, the word Lowe's with the apostrophe. I want to be able to search for that string. When the apost. is used, CF errors out. I can currently sure for lowe becuase I'm using LIKE in the query however lowes returns none and lowe's errors. Using Access database

error:
Syntax error (missing operator) in query expression 'STATE LIKE '%ca%' AND STORENAME LIKE '%Lowe's%''.


Query:
<CFQUERY NAME="GETSEARCH" DATASOURCE="#request.datasourcename#">
SELECT ID, STORENAME, SHOPPINGLOCATION, STREETADDRESS, CITY, STATE, ZIP, REGION, AREACODE, PHONE, EXT, TITLE, FIRSTNAME, LASTNAME, LASTVERIFIED, Notes1, Notes2
FROM My_DB
WHERE #preservesinglequotes(session.sqlHold)#
</CFQUERY>


DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[ecobb]

Use cfqueryparam.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[DeZiner]

Never used it before so I looked it up at macromedia. I'm builing the sql on the fly. So here's what I tried notice the second one below:

<CFIF TRIM(FORM.SELECT2) NEQ "">
<CFSET SQL = "#SQL# AND REGION LIKE '%#TRIM(FORM.SELECT2)#%'">
</CFIF>
<CFIF TRIM(FORM.COMPANY) NEQ "">
<CFSET SQL = "#SQL# AND STORENAME LIKE <cfqueryparam value = '%#TRIM(FORM.COMPANY)#%'>">
</CFIF>

'SQL' contains the where statement and is built on the fly. This didn't change the error. Any other thoughts?

DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[TruthInSatire]

don't build it like a string when you do that you're going to have problems with the single quotes when cf tries to double up on the quotes.

you can build the query dynamicly inside the cfquery tag. example....

<cfquery...>
  SELECT field1, field2, field3
  FROM   tableName
  WHERE  0=0 <!--- get everything --->
  <cfif someCondidtion is true>
    AND  field2 like  <cfqueryparam value = "%#TRIM(FORM.field2)#%">
  </cfif>
  <cfif someOtherCondidtion is true>
    AND field3 = <cfqueryParam value = "#TRIM(form.field3)#">
</cfquery>

*note- I don't use cfqueryparam like i should so the syntax may be a bit off.

Beware of programmers who carry screwdrivers.