Quick look: Building SQL expressions...

[Morgan Senkal]

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]

---