I have a dynamic query that works just fine if the user doesn't put any apostrophes in the input form fields.
I need to be able to allow apostrophe's in the form fields and have SQL Server process them as text instead of identifiying them as a delimiter that separates strings.
Can someone tell me how I can accomplish that?
The query is:
<cfset WhereClause = " 1=1 ">
<cfif isDefined("FORM.txtUIDs") AND FORM.txtUIDs NEQ "">
<cfset WhereClause = WhereClause & " AND a.UID IN ("&FORM.txtUIDs&")" >
<cfif isDefined("FORM.txtCenterName") AND FORM.txtCenterName NEQ "">
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&FORM.txtCenterName&"%'" >
<cfif isDefined("FORM.txtStreetNum") AND FORM.txtStreetNum NEQ "">
<cfset WhereClause = WhereClause & " AND a.BldgStNum = '"&FORM.txtStreetNum&"'" >
<cfif isDefined("FORM.txtStreetName") AND FORM.txtStreetName NEQ "">
((( a bunch more of these if statements... you get the idea. ( i hope) )))
<CFQUERY NAME="GetRecords" DATASOURCE="#dsn#">
SELECT a.UID, a.SN, a.Sec, a.[ID], a.ID_numeric, a.Status, a.KM, a.BldgName,
a.BldgStNum, a.BldgStName, a.BldgCity, a.BldgState, a.BldgZip,
a.Contiguous, a.YrRenov, a.Census, a.Anchor, a.[Type], a.preferedListing,
a.rate1, a.rate2, a.avgRent, a.parkingSpaces, a.commission, a.commissionPercent
FROM RET_tblData a
LEFT JOIN RET_tblSector h ON a.Sec = h.Sector
I cut a bunch of stuff out of the query for brevity but the concept is there.
The problem is with the field for "FORM.txtCenterName". This is just a regular textbox and I must allow the use of single quotes. The sql statement is chocking on it when a single quote is encountered.
Any ideas on how I can fix this?