I'm getting an Error in my WHERE clause - seems sosimple.

[CarlMarket]

Maybe because I'm new to Cold Fusion, but I'm getting the following error when
running this query for a report...

"Error Executing Database Query.
Line 11: Incorrect syntax near 'APPROVED'.

Here's the query.... (NOTE: form.cbo_InventoryStatus = "APPROVED")

<cfquery name="MyQuery" datasource="Database">
SELECT * FROM dbo.tableName
<cfif form.cbo_InventoryStatus IS NOT "">
<cfset WhereClause = WhereClause & " WHERE Asset_Status_Feature = '"
& #form.cbo_InventoryStatus# & "'>
</cfif>
<cfoutput>#WhereClause# </cfoutput>
</cfquery>


The above doesn't work - however what's weird is that if I pass a Integer it
works - such as below.
<cfif form.cbo_Client NEQ 0>
<cfset WhereClause = WhereClause & " WHERE ClientID = " & #form.cbo_Client#>
</cfif>


HELP!!!

[The ScareCrow]

CF will automatically escape the single quotes, so you need to use
PreserveSingleQuotes(string_variable)
Ken


<cfquery name="MyQuery" datasource="Database">
SELECT * FROM dbo.tableName
<cfif form.cbo_InventoryStatus IS NOT "">
<cfset WhereClause = WhereClause & " WHERE Asset_Status_Feature = ' " &
#form.cbo_InventoryStatus# & " ' ">
</cfif>
<cfoutput>#PreserveSingleQuotes(WhereClause)# </cfoutput>
</cfquery>

[CarlMarket]

NEW ERROR
As a test I just hard coded the SQL Query and now I get this error...

The value "" cannot be converted to a number

See code (Thanks!)

<cfcomponent>
<cffunction name="ReportQuery" returntype="query" output="no" access="public">
<cfset var MyQuery="">
<cfset var WhereClause="">

<!---
Main.cfr expects the query passed into it to contain the following column
names:
Field: Asset_Status DataType: String
--->
<cfquery name="MyQuery" datasource="DatabaseName">
SELECT * FROM dbo.TableName WHERE Asset_Status = 'APPROVED'
</cfquery>
<cfreturn MyQuery>
</cffunction>
</cfcomponent>

[The ScareCrow]

What data type is Asset_Status, the error would indicate it is either a bit column or an integer ??

Ken

[CarlMarket]

That's what I thought too - but CF displays the datatypes above and it shows it's a string
<!--- Field: Asset_Status DataType: String --->

Thanks

[The ScareCrow]

I would be checking the actual database.
I would also test this query against the database outside of CF

Ken

[paross1]

So what is the actual field name that you want to use? Your intial post
referred to Asset_Status_Feature but your more recent posts refer to
Asset_Status. Could it be possible that your database has both fields, where
Asset_Status_Feature is a string and Asset_Status is numeric?

Phil

[CarlMarket]

Here's what I ended up using which worked (thanks for all the help)

<cfif Form.cbo_InventoryStatus IS NOT "">
WHERE ((Asset_Status_Feature = <cfqueryPARAM value =
"#Form.cbo_InventoryStatus#"
CFSQLType = "CF_SQL_VARCHAR">) OR (Asset_Status_Episode = <cfqueryPARAM
value = "#Form.cbo_InventoryStatus#"
CFSQLType = "CF_SQL_VARCHAR">) OR (Asset_Status_VAM = <cfqueryPARAM value
= "#Form.cbo_InventoryStatus#"
CFSQLType = "CF_SQL_VARCHAR">) OR (Asset_Status_Audio = <cfqueryPARAM
value = "#Form.cbo_InventoryStatus#"
CFSQLType = "CF_SQL_VARCHAR">) OR (Asset_Status_Subtitle = <cfqueryPARAM
value = "#Form.cbo_InventoryStatus#"
CFSQLType = "CF_SQL_VARCHAR">) OR (Asset_Status_Menu = <cfqueryPARAM value
= "#Form.cbo_InventoryStatus#"
CFSQLType = "CF_SQL_VARCHAR">))
</cfif>