Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Coldfusion Syntax Error Using "Contains"

Posted on 2004-10-22
3
Medium Priority
?
210 Views
Last Modified: 2013-12-24
Here is the error (actual code down below error):

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Contains(debt_sale.last_name, 'cannon') OR Contains(debt_sale.address_1, 'cannon') OR Contains(debt_sale.address_2, 'cannon') OR Contains(debt_sale.address_3, 'cannon')'.  
 
The error occurred in C:\Collections\Kaleidoscope\debt_sale\debt_sale_search.cfm: line 32
 
30 :       <cfelse>
31 :       WHERE Contains(debt_sale.address_2, '#strAddress#') OR Contains(debt_sale.address_1, '#strAddress#') OR Contains(debt_sale.address_3, '#strAddress#');      
32 : </cfif>
33 : </cfquery>
34 :

--------------------------------------------------------------------------------
 
SQL    SELECT debt_sale.acct_no, debt_sale.first_name, debt_sale.last_name, debt_sale.ssn, debt_sale.address_1, debt_sale.address_2, debt_sale.address_3, debt_sale.city, debt_sale.state, debt_sale.zipcode, debt_sale.home_phone, debt_sale.business_phone, debt_sale.disconnect_date, debt_sale.total_bal, debt_sale.principal_amt, debt_sale.charge_off_date, debt_sale.last_pay_amt, debt_sale.last_pay_date, debt_sale.acct_open_date, debt_sale.cell_no, debt_sale.comments, debt_sale.reviewed_by, debt_sale.date_reviewed FROM debt_sale WHERE Contains(debt_sale.last_name, 'cannon') OR Contains(debt_sale.address_1, 'cannon') OR Contains(debt_sale.address_2, 'cannon') OR Contains(debt_sale.address_3, 'cannon');  
DATASOURCE   DebtSale
 
Here is the actual code:

<cfquery name="qryDebtSale" datasource="DebtSale">
SELECT debt_sale.acct_no, debt_sale.first_name, debt_sale.last_name, debt_sale.ssn, debt_sale.address_1, debt_sale.address_2, debt_sale.address_3, debt_sale.city, debt_sale.state, debt_sale.zipcode, debt_sale.home_phone, debt_sale.business_phone, debt_sale.disconnect_date, debt_sale.total_bal, debt_sale.principal_amt, debt_sale.charge_off_date, debt_sale.last_pay_amt, debt_sale.last_pay_date, debt_sale.acct_open_date, debt_sale.cell_no, debt_sale.comments, debt_sale.reviewed_by, debt_sale.date_reviewed
FROM debt_sale
<cfif strAcctNo neq ''>
      WHERE debt_sale.acct_no = '#strAcctNo#');
      <cfelseif strLastName neq ''>
      WHERE Contains(debt_sale.last_name, '#strLastName#') OR Contains(debt_sale.address_1, '#strLastName#') OR Contains(debt_sale.address_2, '#strLastName#') OR Contains(debt_sale.address_3, '#strLastName#');
      <cfelseif strSSN neq ''>
      WHERE debt_sale.ssn = '#strSSN#';
      <cfelse>
      WHERE Contains(debt_sale.address_2, '#strAddress#') OR Contains(debt_sale.address_1, '#strAddress#') OR Contains(debt_sale.address_3, '#strAddress#');      
</cfif>
</cfquery>
0
Comment
Question by:makila
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 12382963
i do nto know how you can use contains you should have used IN

llike
where debt_sale.address_2 in ('#strAddress#')

something like that...
0
 
LVL 8

Accepted Solution

by:
sigmacon earned 200 total points
ID: 12383391
I am assuming you're trying to write a query that selects rows based on whether a string (such as an address) can be found within the value of a column in a row. The SQL function CONTAINS is only applicable to FULL-TEXT INDEXED COLUMNS ON SQL SERVER. You are talking to a MS Access database (according to the error output). AFAIK, Access does not have this function (at least not the Access 2000 that I just checked. You may want to try

WHERE
  debt_sale.address_2 like '%#strAddress#%'
  OR debt_sale.address_1 like '%#strAddress#%'
  OR debt_sale.address_3 like '%#strAddress#%'

and if that blows, give the old JET-style SQL a try

WHERE
  debt_sale.address_2 like '*#strAddress#*'
  OR debt_sale.address_1 like '*#strAddress#*'
  OR debt_sale.address_3 like '*#strAddress#*'
0
 

Author Comment

by:makila
ID: 12383976
Thanks! Using the wildcards worked like a charm. Have a great weekend :)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question