Hello Everyone,
In trying to implement a multi-search functionality to a current system,
i am running into the following problem:
Once i obtain the list of items i get from my form (which is basically a
bout 12 text fields), i loop through the values assigning them to a
list:
LIST1====
<cfloop list="#form.fieldnames#" index="pointer">
<cfif (pointer neq "SUBMIT") and (len(evaluate(pointer)) gt 0)>
<CFSET ITEMLIST = #ListAppend(ITEMLIST, replace(evaluate(pointer), '
', ''))#>
</cfif>
</cfloop>
Then i make the list SQL compliant by using the listqualify function
LIST2====
<CFSET itemlistforquery=ListQualify(ITEMLIST,chr(39),",","ALL")>
Last, I run the following query:
LIST3====
<cfquery name="INSearch2" datasource="#mydatabase_name#" debug>
SELECT PRODUCTID, PRODUCTNAME
FROM PRODUCTS
WHERE (replace(skunumber, ' ', '') in (#itemlistforquery#))
OR (replace(smartnumber, ' ', '') in (#itemlistforquery#))
</cfquery>
The error i get is the following:
LIST4====
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax
near 'LL507509'.
The 'LL507509' is one of the values in the list. If i were to do a
sample search and print the actual query that would be generated with
the values from the list, this would be it:
LIST5====
SELECT PRODUCTID, PRODUCTNAME
FROM PRODUCTS
WHERE (replace(skunumber, ' ', '') in ('LL507509
','NW410813','027434010436'))
OR (replace(smartnumber, ' ', '') in ('LL507509
','NW410813','027434010436'))
I get the error whether i use the REPLACE functions or not. And what is
most strange of all is that if i paste the exact same end query (LIST5)
into my query analyzer, i get my results fine with no errors. Cold
Fusion seems to be the one having problems reading or posting the query
to SQL.
I am running CF 4.5 SP2 and querying a MSSQL7.0 DB.
Any help will be most appreciated.
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
