FastEddie___
asked on
Using PreserveSingleQuotes in a dynamic WHERE Clause
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>
<cfif isDefined("FORM.txtCenterN ame") AND FORM.txtCenterName NEQ "">
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&FORM.txtCenterName&"%'" >
</cfif>
<cfif isDefined("FORM.txtStreetN um") AND FORM.txtStreetNum NEQ "">
<cfset WhereClause = WhereClause & " AND a.BldgStNum = '"&FORM.txtStreetNum&"'" >
</cfif>
<cfif isDefined("FORM.txtStreetN ame") AND FORM.txtStreetName NEQ "">
((( a bunch more of these if statements... you get the idea. ( i hope) )))
</cfif>
<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
WHERE #PreserveSingleQuotes(Wher eClause)#
</CFQUERY>
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?
-Eddie
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>
<cfif isDefined("FORM.txtCenterN
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&FORM.txtCenterName&"%'"
</cfif>
<cfif isDefined("FORM.txtStreetN
<cfset WhereClause = WhereClause & " AND a.BldgStNum = '"&FORM.txtStreetNum&"'" >
</cfif>
<cfif isDefined("FORM.txtStreetN
((( a bunch more of these if statements... you get the idea. ( i hope) )))
</cfif>
<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
WHERE #PreserveSingleQuotes(Wher
</CFQUERY>
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?
-Eddie
ASKER
Thanks for the suggestion. I tried it but it did not work. I'm getting this DB error:
Incorrect syntax near 's'.
The SQL Where cause that is being returned is this:
WHERE 1=1 AND a.BldgName LIKE 'Kathryn's%' order by a.preferedListing desc, a.[ID]
It's the single quote in the string "Kathryn's" that is causing the problem.
Got any other suggestions?
Incorrect syntax near 's'.
The SQL Where cause that is being returned is this:
WHERE 1=1 AND a.BldgName LIKE 'Kathryn's%' order by a.preferedListing desc, a.[ID]
It's the single quote in the string "Kathryn's" that is causing the problem.
Got any other suggestions?
ASKER
JS, I don't know why preserveSingleQuotes didn't work in tat instance but your suggestion allowed me to come up with a solution.
I just replaced the single quote with two single quotes in the form field prior to setting the where clause.
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&ReplaceNoCase(FORM.txtC enterName, "'","''"," All")&"%'" >
That did the trick.
I just replaced the single quote with two single quotes in the form field prior to setting the where clause.
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&ReplaceNoCase(FORM.txtC
That did the trick.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried using queryparam on the form field and the where clause, both tests failed.
Your function solution is great though. It'll let me do it with alot less typing.
Thank you so much for your help.
-Eddie
Your function solution is great though. It'll let me do it with alot less typing.
Thank you so much for your help.
-Eddie
So for example, change your code to look more like this:
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&PreserveSingleQuotes(FO
See if that helps.