Solved

Coldfusion Syntax Error Using "Contains"

Posted on 2004-10-22
204 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
Question by:makila
    3 Comments
     
    LVL 22

    Expert Comment

    by:pinaldave
    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:
    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
    Thanks! Using the wildcards worked like a charm. Have a great weekend :)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
    Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
    The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
    The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now