Advance search recordset giving error message?

yts007 · January 10th, 2006, 04:51 AM

Code:

Hi,
I created this recordset using DreamweaverMX2004 advance dialoge box, then generated the below code, 
when I try to insert fields in the page I get error message says:
[Microsoft][ODBC Microsoft Access Driver] syntax error (missing operator) in query experssion.

can you help pls.

----------------the code page---------------------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Recordset1__vtitle
Recordset1__vtitle = "%"
If (RequestQuerystring("vtitle")  <> "") Then 
  Recordset1__vtitle = RequestQuerystring("vtitle") 
End If
%>
<%
Dim Recordset1__vauthorname
Recordset1__vauthorname = "%"
If (RequestQuerystring("vauthorname") <> "") Then 
  Recordset1__vauthorname = RequestQuerystring("vauthorname")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_mctconn_STRING

Recordset1.Source = "SELECT Book_id, title, No_pages, Price, 
Availability, AuthorName, Img_url,   Out_Of_List, tblAuthors.Author_Name, 
tblCategory.Category_Desc,   tblPublishers.Publisher_Name,  tblYears.Year_Desc, tblEditions.Edition_Desc,tblBookSizes.Size_Desc  
FROM tblBooks INNER JOIN tblAuthors ON tblbooks.Author_id = tblAuthors.Author_id AND  
tblBooks INNER JOIN tblCategory ON tblBooks.Category_id = tblCategory.Category_id AND  
tblBooks INNER JOIN tblPublishers ON tblBooks.Publisher_id = tblPublishers.Publisher_id AND  
tblBooks INNER JOIN tblYears ON tblBooks.Year_id = tblYears.Year_id AND  
tblBooks INNER JOIN tblEditions ON tblBooks.Edition_no = tblEditions.Edition_no AND  
tblBooks INNER JOIN tblBookSizes ON tblBooks.Size_no = tblBooksizes.Size_no  
WHERE tblbooks.title like '%" + Replace(Recordset1__vtitle, "'", "''") + "' OR 
tblbooks.AuthorName Like '%" + Replace(Recordset1__vauthorname, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Imar · January 10th, 2006, 03:26 PM

Hi there,

Write out the SQL statement before you execute it like this:

Recordset1.Source = "SELECT Book_id, title, No_pages, Price,
Availability, AuthorName, Img_url, Out_Of_List, .....

Response.Write("SQL is " & Recordset1.Source)
Response.End

What do you get? Does the SQL look alright? If not, can you post it here?

If you post code in this forum, can you please add a line break here and there? Typing in a three foot wide message isn't very easy... ;)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

yts007 · January 10th, 2006, 04:36 PM

I added the 2 lines and it printed the sql statement right.
---------------------
the error message stands:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''.

--------------------------------the code-----------------

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Recordset1__vtitle
Recordset1__vtitle = "%"
If (Request.QueryString("vtitle")   <> "") Then 
  Recordset1__vtitle = Request.QueryString("vtitle")  
End If
%>
<%
Dim Recordset1__vauthorname
Recordset1__vauthorname = "%"
If (Request.QueryString("vauthorname")  <> "") Then 
  Recordset1__vauthorname = Request.QueryString("vauthorname") 
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_mctconn_STRING
Recordset1.Source = "SELECT tblBooks.Book_id, tblBooks.title, tblBooks.No_pages, tblBooks.Price, 
                            tblBooks.Availability, tblBooks.AuthorName, tblBooks.Img_url,   
                            tblBooks.Out_Of_List, tblAuthors.Author_Name, tblCategory.Category_Desc,   
                            tblPublishers.Publisher_Name,  tblYears.Year_Desc, tblEditions.Edition_Desc,   
                            tblBookSizes.Size_Desc  
                    FROM tblBooks INNER JOIN tblAuthors ON tblbooks.Author_id = tblAuthors.Author_id AND  
                         tblBooks INNER JOIN tblCategory ON tblBooks.Category_id = tblCategory.Category_id AND  
                         tblBooks INNER JOIN tblPublishers ON tblBooks.Publisher_id = tblPublishers.Publisher_id AND  
                         tblBooks INNER JOIN tblYears ON tblBooks.Year_id = tblYears.Year_id AND  
                         tblBooks INNER JOIN tblEditions ON tblBooks.Edition_no = tblEditions.Edition_no AND  
                         tblBooks INNER JOIN tblBookSizes ON tblBooks.Size_no = tblBooksizes.Size_no  
                    WHERE tblbooks.title like '" + Replace(Recordset1__vtitle, "'", "''") + "' and 
                            tblbooks.AuthorName Like '" + Replace(Recordset1__vauthorname, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Imar · January 11th, 2006, 02:59 AM

I am sure it printed right, but apparently it contains an error.

Can you post the outcome of the Response.Write() call?

I think the error is caused by the way you use AND between your INNER JOINS. And is not required there.

However, it's hard to say without seeing the actual SQL statement.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

yts007 · January 11th, 2006, 01:06 PM

the 2 lines printed the following:

SQL is SELECT tblBooks.Book_id, tblBooks.title, tblBooks.No_pages, tblBooks.Price, tblBooks.Availability, tblBooks.AuthorName, tblBooks.Img_url, tblBooks.Out_Of_List, tblAuthors.Author_Name, tblCategory.Category_Desc, tblPublishers.Publisher_Name, tblYears.Year_Desc, tblEditions.Edition_Desc, tblBookSizes.Size_Desc FROM tblBooks INNER JOIN tblAuthors ON tblbooks.Author_id = tblAuthors.Author_id AND tblBooks INNER JOIN tblCategory ON tblBooks.Category_id = tblCategory.Category_id AND tblBooks INNER JOIN tblPublishers ON tblBooks.Publisher_id = tblPublishers.Publisher_id AND tblBooks INNER JOIN tblYears ON tblBooks.Year_id = tblYears.Year_id AND tblBooks INNER JOIN tblEditions ON tblBooks.Edition_no = tblEditions.Edition_no AND tblBooks INNER JOIN tblBookSizes ON tblBooks.Size_no = tblBooksizes.Size_no WHERE tblbooks.title like '%1%' OR tblbooks.AuthorName Like '%1%'

Imar · January 11th, 2006, 02:34 PM

Quote:

quote: think the error is caused by the way you use AND between your INNER JOINS. And is not required there.

I still see a lot of AND statements between your JOINs. You don't need them, only use them in your WHERE clause...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

yts007 · January 11th, 2006, 02:35 PM

You are right about the AND thing in my query statement.
I re-did it with help of ms access gui and, the dreamweaver
now accepted the query and show me the list of fields,
but when I run it gives me this error message:-----------------
Microsoft VBScript compilation (0x800A0408)
Invalid character

This is what the query statement looks like now:-------------

SELECT tblBooks.Title, tblBooks.No_pages, tblBooks.Price, tblBooks.Availability, tblBooks.AuthorName, tblBooks.Out_Of_List, tblBooks.Img_url, tblBookSizes.Size_Desc, tblAuthors.Author_Name, tblCategory.Category_Desc, tblEditions.Edition_Desc, tblPublishers.Publisher_Name, tblYears.Year_Desc
FROM tblYears INNER JOIN (tblPublishers INNER JOIN (tblEditions INNER JOIN (tblCategory INNER JOIN (tblBookSizes INNER JOIN (tblAuthors INNER JOIN tblBooks ON tblAuthors.Author_id = tblBooks.Author_id) ON tblBookSizes.Size_no = tblBooks.Size_no) ON tblCategory.Category_id = tblBooks.Category_id) ON tblEditions.Edition_no = tblBooks.Edition_no) ON tblPublishers.Publisher_id = tblBooks.Publisher_id) ON tblYears.Year_id = tblBooks.Year_id
WHERE (((tblBooks.Title) Like "%vtitle%")) OR (((tblBooks.AuthorName) Like "%vauthorname%"))

Imar · January 11th, 2006, 02:45 PM

This error:

Microsoft VBScript compilation (0x800A0408)

seems to suggest your ASP page now has a problem while the query appears to be fine. WHere does the error occur? Can you see any reason for this error?

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

yts007 · January 11th, 2006, 02:49 PM

its says invalid char, checked it over and over again but I cant see
anything wrong

the exact error:--------------
Technical Information (for support personnel)

Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/test/r.asp, line 23, column 786
Recordset1.Source = "SELECT tblBooks.Title, tblBooks.No_pages, tblBooks.Price, tblBooks.Availability, tblBooks.AuthorName, tblBooks.Out_Of_List, tblBooks.Img_url, tblBookSizes.Size_Desc, tblAuthors.Author_Name, tblCategory.Category_Desc, tblEditions.Edition_Desc, tblPublishers.Publisher_Name, tblYears.Year_Desc FROM tblYears INNER JOIN (tblPublishers INNER JOIN (tblEditions INNER JOIN (tblCategory INNER JOIN (tblBookSizes INNER JOIN (tblAuthors INNER JOIN tblBooks ON tblAuthors.Author_id = tblBooks.Author_id) ON tblBookSizes.Size_no = tblBooks.Size_no) ON tblCategory.Category_id = tblBooks.Category_id) ON tblEditions.Edition_no = tblBooks.Edition_no) ON tblPublishers.Publisher_id = tblBooks.Publisher_id) ON tblYears.Year_id = tblBooks.Year_id WHERE (((tblBooks.Title) Like "%" + Replace(Recordset1__vtitle, "'", "''") + "%")) OR (((tblBooks.AuthorName) Like "%" + Replace(Recordset1__vauthorname, "'", "''") + "%"))"
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^

Imar · January 11th, 2006, 02:54 PM

It's a bit hard to see where the line is pointing. It would be helpful if you supplied soke additional information when you post your questions here. You have to help us help you.

Anyway, look at the way you use double quotes. Since you're inside the string for the Source that starts with a " you can't use a single double quote in the code directly.

This will fail:

rs.Source = "Hello" and Bye"

because the second double quote ends the string, so and Bye" is considered rubbish.

Since you're building up a SQL statement, you can use a single quote:

rs.Source = "Hello' and Bye"

Alternatively, if you do need a double quote, you need to escape it with another double quote:

rs.Source = "Hello"" and Bye"

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.