single quotes inside variables when they are output inside CFQUERY, because
usually variables don't contain SQL, just values. preserveSingleQuotes() is
there for exactly this situation.
Cheers,
barneyb
> -----Original Message-----
> From: Morgan Senkal [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 24, 2004 10:46 AM
> To: CF-Talk
> Subject: Quick look: Building SQL expressions...
>
> I'm trying to build an SQL _expression_. Everything is working
> great, except when I get to the WHERE clause. When I built
> the _expression_, I entered in single quotes where appropriate;
> however CF apparently changed them to double quotes, and a
> Replace() doesn't seem to be working correctly...I've just
> got too many quotes, I'm drowning in them!
>
> Here's the code:
>
> <cfset requestedColumns = "">
> <cfset queryParameters = "">
>
> <cfloop index="loopCount" from="1"
> to="#ArrayLen(SESSION.queryParams)#">
>
> <cfif SESSION.queryParams[loopCount].Order NEQ " ">
>
> <cfif loopCount EQ 1>
> <cfset requestedColumns =
> SESSION.queryParams[loopCount].Name>
> <cfelse>
> <cfset requestedColumns = requestedColumns & ",
> " & SESSION.queryParams[loopCount].Name>
> </cfif>
> </cfif>
>
> <cfif SESSION.queryParams[loopCount].VALUE NEQ "">
>
> <cfset queryParameters = queryParameters & " AND " &
> SESSION.queryParams[loopCount].Name & " = '" &
> SESSION.queryParams[loopCount].Value & "'">
>
> </cfif>
>
> </cfloop>
>
>
> Then build the SQL string:
>
> <cfset mySQLString = "SELECT DISTINCT " & requestedColumns &
> " FROM v_Tribal_All WHERE 0 = 0" & queryParameters>
>
> Then send it out to the dB:
>
> <cfquery datasource="Tribal_Matrix" name="bigQuery">
> #mySQLString#
> </cfquery>
>
> ERROR MESSAGE:
> ODBC Error Code = 37000 (Syntax error or access violation)
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near 'High'.
>
> SQL = "SELECT DISTINCT Tribe, Priority, BusinessUnit,
> Project, IssueCategory, IssueDescription, IssueOutcome,
> ActionItemDescription, ActionItemTargetDate FROM v_Tribal_All
> WHERE 0 = 0 AND Priority = ''High''"
>
>
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
