Syntax error (missing operator) in query expression

[Bob Lehmann]

What Steve means is - Why aren't you using it in your IF blocks...
<%
Dim rsResults__A
rsResults__A = "%"
If (Request("artist") <> "") Then
rsResults__A = ReplaceThis(Request("artist"))
End If
%>

Then just use rsResults__A when you build your string fir the SQL, which
will make that code much more readable.

Does GENKTITLES.TITLE contain a " ' "? If it does, then why would you expect
a match?

WTF is this all about? AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND

Bob Lehmann

 

[Steven Burn]

What Steve means is - Why aren't you using it in your IF blocks...

hehe, cheers Bob ;o) (think I'm having a blonde day today)

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[shank]

But surely, if it's to be an effective search, you don't want a match on
all variations<<
Yes! Absolutely I want to match on all variations! There are too many
variations in the data plus what the user may search upon. I have to replace
those common characters in BOTH the data and criteria.

I can use the function on the IF blocks - no problem. But I still have to
replace the common characters in the data. Hence, the syntax problem.
These are more fields that can be searched upon to narrow the search
results. Dreamweaver generates the code using the SQL wildcard % and of
course the + signs. Like it or not, it all works well, except for this
Replacing of the common characters.

thanks

 

[Bob Barrows [MVP]]

This is my code to build the recordset...
<%
Dim rsResults
Dim rsResults_numRows

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
GenKStock.SoftHard, GenKStock.Category, GenKStock.Type,
GenKStock.Label, GenKStock.Description, GenKStock.Manuf,
GenKTitles.ItemNumber, GenKTitles.Title, GenKTitles.Artist,
GenKTitles.mp3Files FROM GenKStock INNER JOIN GenKTitles ON
GenKStock.OrderNo = GenKTitles.ItemNumber WHERE ( " &
ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
ReplaceThis(rsResults__T) + "' + '%') AND (GenKTitles.Artist LIKE
'%' + '" + Replace(rsResults__A, "'", "''") + "' + '%') AND
(GenKStock.Category LIKE '" + Replace(rsResults__C, "'", "''") + "')
AND (GenKStock.Manuf LIKE '" + Replace(rsResults__M, "'", "''") + "')
AND (GenKStock.Type LIKE '" + Replace(rsResults__TY, "'", "''") + "')
AND (GenKStock.SingleArtist LIKE '" + Replace(rsResults__SA, "'",
"''") + "') ORDER BY " + Replace(rsResults__sql_orderby, "'", "''")
+ ""
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>

Then, down in the HTML I have this code: SQL = <%=rsResults.Source%>

Which gives me this: SQL = SELECT GenKStock.OrderNo,
GenKStock.SingleArtist, GenKStock.SoftHard, GenKStock.Category,
GenKStock.Type, GenKStock.Label, GenKStock.Description,
GenKStock.Manuf, GenKTitles.ItemNumber, GenKTitles.Title,
GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock INNER JOIN
GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE (
GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND (GenKTitles.Artist LIKE
'%' + '%' + '%') AND (GenKStock.Category LIKE '%') AND
(GenKStock.Manuf LIKE '%') AND (GenKStock.Type LIKE '%') AND
(GenKStock.SingleArtist LIKE '%') ORDER BY OrderNo ASC

Response.write SQL gives me nothing because there is no variable
named SQL. This is response.write SQL: <%=Response.Write(SQL)%>
(gives me no results) Sorry if I don't understand what you want.

Well then you need to create a variable called SQL. (I usually call it sSQL,
but you can do what you want: it's your code) It is good programming
practice to assign the result of string concatenations to a variable so you
can inspect the variable (using Response.Write in server-side code). You can
use the variable in your recordset's Source statment instead of the string
concatenation code:

rsResults.Source = sSQL


However, having said that, I now see that you've response.written the
recordset's Source property, you has given us what I asked for. My mistake.

The other thing you've never told us is what database you are using. If you
are using Access, this

GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND

should be this:

GENKTITLES.TITLE LIKE '%' & 'AINT' & '%') AND

I am going to refrain from further analysis until I find out what database
you are using.

I will say that you seem to have an inordinate number of comparisons to '%'.
Do you have any idea what this sql statement should really look like? Have
you used your database's query tool to test the query to make sure it gives
you what you want? This is another good programming practice. You can't run
a query unless you know what the query is supposed to look like.

Bob Barrows

 

[shank]

I'm using Access 2002. I mentioned that somewhere, but not sure where now.
Yes I ran the query in Access and viewed the query in SQL mode. The query
works fine. The problem comes into play when I try using the Replace on both
sides of the coin: data and criteria. The replace for criteria works well. I
just can't get the syntax for the data side correct.
thanks!

 

[Aaron [SQL Server MVP]]

Thanks to all, but if I can't get past a few syntax issues, I'll never get

past creating a function.

You'll never even get TO creating a function if you insist on using crapware
like DreamWeaver to do your work for you.

Sorry to be blunt, but it's your own reliance on these tools that is causing
the "few syntax issues" in the first place.

 

[Bob Barrows [MVP]]

What you need to do is use the query tool in Access to create the query with
the Replace function on the data, so you can see what it's supposed to look
like when it works (PS. It is a really bad idea to do this. Your query will
not perform very well if you use pass columns to functions in your WHERE
clause. Also, you cannot use user-defined function in your queries when you
call them via ADO. You must use only built-in VBA functions. So that
ReplaceThis function will not work when you call the query from ASP).

Your task will be made much simpler if, when you get a query that works in
the query tool (you have to use * instead of % in the WHERE clause when you
run the query using the query tool - change the * back to % when you run it
via ADO), you parameterize and save the query, which can more easily be run
from ASP than all this concatenation business. Do a quick Google for posts
by me containing the words "saved parameter query" to see how easy this can
be. You will no longer have to worry about all the stupid "quote" issues.

Bob Barrows