Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Web Programming > Adobe Web Programming > Dreamweaver (all versions)
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 10th, 2006, 04:51 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Advance search recordset giving error message?

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
%>
Reply With Quote
  #2 (permalink)  
Old January 10th, 2006, 03:26 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old January 10th, 2006, 04:36 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
%>
Reply With Quote
  #4 (permalink)  
Old January 11th, 2006, 02:59 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old January 11th, 2006, 01:06 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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%'

Reply With Quote
  #6 (permalink)  
Old January 11th, 2006, 02:34 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old January 11th, 2006, 02:35 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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%"))
Reply With Quote
  #8 (permalink)  
Old January 11th, 2006, 02:45 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #9 (permalink)  
Old January 11th, 2006, 02:49 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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, "'", "''") + "%"))"
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Reply With Quote
  #10 (permalink)  
Old January 11th, 2006, 02:54 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help me by giving code for this search button csateesh ASP.NET 1.0 and 1.1 Professional 1 August 7th, 2007 07:40 AM
Recordset Error Message Joemenator Classic ASP Databases 5 January 16th, 2007 04:47 AM
mysql_num_rows() => Giving Error? Cybot MySQL 4 September 12th, 2006 12:07 PM
This piece of code is giving an error sourik C# 2 July 15th, 2006 09:18 AM
advance search with stored procedure harpua Classic ASP Databases 1 December 29th, 2004 04:02 AM



All times are GMT -4. The time now is 09:26 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.