?
Solved

Looping over query variables

Posted on 2007-10-05
8
Medium Priority
?
134 Views
Last Modified: 2013-12-24
I'm trying to run a search against MS SQL Database and I am getting an error that seams odd to me and I am not sure why. Here is the error on CF7

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AND'.

Here is the SQL:

      SELECT AssetID,Title,PDGNO,JPGFileName
      FROM AssetSearchView
      WHERE 0=0
    (
    <cfloop list="#searchword#" index="thisword" delimiters=" ">
            AND (Keywords LIKE '%#thisword#%' OR Description LIKE '%#thisword#%' OR Title LIKE '%#thisword#%')
      </cfloop>
    '%%')

I am running a similar query on one of my access databases and it words, so I am assuming it's something unique to MS SQL.

It may or may not be worth mentioning that I will be adding some additional logic with a few more AND statements after this but those havent been writen yet. I am just trying to get the basic script working at this point.
0
Comment
Question by:visual28
  • 4
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 20023215
the 0=0 should be in the expression so that the AND (keywords... is valid

SELECT AssetID,Title,PDGNO,JPGFileName
      FROM AssetSearchView
      WHERE   (0=0
    <cfloop list="#searchword#" index="thisword" delimiters=" ">
            AND (Keywords LIKE '%#thisword#%' OR Description LIKE '%#thisword#%' OR Title LIKE '%#thisword#%')
      </cfloop>
    '%%')
0
 
LVL 53

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20023287
Also, remove the extra '%%') at the end

SELECT AssetID,Title,PDGNO,JPGFileName
      FROM AssetSearchView
      WHERE   0=0
    <cfloop list="#searchword#" index="thisword" delimiters=" ">
            AND (Keywords LIKE '%#thisword#%' OR Description LIKE '%#thisword#%' OR Title LIKE '%#thisword#%')
      </cfloop>


I think description might also be a reserved word. If moving the parenthesis doesn't work, try putting it in square brackets [Description]
0
 
LVL 53

Expert Comment

by:_agx_
ID: 20023308
> description might also be a reserved word

.. though I might be thinking of another database type
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:visual28
ID: 20023311
no, that doesn't  work either. just changes the error to

 [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.

Here is the SQL that CF returns in the error.

SELECT AssetID,Title,PDGNO,JPGFileName FROM AssetSearchView (WHERE 0=0 AND (Keywords LIKE '%man%' OR Description LIKE '%man%' OR Title LIKE '%man%') '%%')

0
 
LVL 53

Expert Comment

by:_agx_
ID: 20023329
You're got an extra '%%' on the end.  Try removing it

SELECT AssetID,Title,PDGNO,JPGFileName
      FROM AssetSearchView
      WHERE   0=0
    <cfloop list="#searchword#" index="thisword" delimiters=" ">
            AND (Keywords LIKE '%#thisword#%' OR Description LIKE '%#thisword#%' OR Title LIKE '%#thisword#%')
      </cfloop>
0
 
LVL 53

Expert Comment

by:_agx_
ID: 20023353
Though you may need to tinker with the AND/OR's depending on what results you want.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 20023697
this is the sql you need to generate...

SELECT AssetID,Title,PDGNO,JPGFileName FROM AssetSearchView WHERE 0=0 AND (Keywords LIKE '%man%' OR Description LIKE '%man%' OR Title LIKE '%man%')

you have a ( before the where which shouldn't be there...
and as stated remove the offending '% %'

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What You Need to Know when Searching for a Webhost Provider
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

609 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