SQL error

[manicleek]

I have the following code

thenum = 0
rsSQL="SELECT userreviews.RestuarantId, dataqfol.Company, dataqfol.Address1, dataqfol.Address2, dataqfol.Address3, dataqfol.City, dataqfol.County, dataqfol.Postcode, dataqfol.ID, dataqfol.Phone, dataqfol.Fax, dataqfol.Cuisine, dataqfol.URL, dataqfol.email, dataqfol.Description, dataqfol.IsClient, dataqfol.details, dataqfol.datamenu FROM dataqfol"
if TheRName <> "" then
rsSQL = rsSQL & " WHERE Company LIKE '%" & TheRName & "%'"
thenum = 1
end if

if TheTown <> "" and thenum = 0 then
rsSQL = rsSQL & " WHERE City LIKE '%" & TheTown & "%'"
thenum = 1
else if TheTown <> "" and thenum = 1 then
rsSQL = rsSQL & " AND City LIKE '%" & TheTown & "%'"
end if
end if

if TheCounty <> "" and thenum = 0 then
rsSQL = rsSQL & " WHERE County LIKE '%" & TheCounty & "%'"
thenum = 1
else if TheCounty <> "" and thenum = 1 then
rsSQL = rsSQL & " AND County LIKE '%" & TheCounty & "%'"
end if
end if
if ThePostcode <> "" and thenum = 0 then
rsSQL = rsSQL & " WHERE Postcode LIKE '" & ThePostcode & "%'"
thenum = 1
else if ThePostcode <> "" and thenum = 1 then
rsSQL = rsSQL & " AND Poscode LIKE '" & ThePostcode & "%'"
end if 
end if

rsSQL = rsSQL & " INNER JOIN userreviews ON (dataqfol.web_ID = userreviews.RestuarantId) ORDER BY IsClient ASC, Company ASC LIMIT " & limitvalue & ", " & numresults & ""

set rs = server.CreateObject("ADODB.recordset")

rs.open rsSQL, objConn

and I keep getting the error

[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN userreviews ON (dataqfol.web_ID = userreviews.Restua

but I can't see the problem

 

[ChrisHirst]

print the sql to screen before your rs.open usually it will be obvious.

assuming this, " AND Poscode LIKE " is just a typo in the post of course.







Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[Tarwn]

SELECT fieldlist FROM table, tables, or JOIN WHERE equivalence checks ORDER BY field(s) [optional ASC/DESC]

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[spod]

If you're using SQL92 (which it looks like you are)
then the INNER JOIN part of the SQL should be in FROM

ie

select a.dateid, 
b.STORE, 
sum(a.sales)  
from SALES_TABLE a 
join STORE_TABLE b 
on (a.STORE= b.STORE) 
group by a.date, 
b.STORE

if you use SQL89 then the join would appear in the where clause:

select a.dateid, 
b.STORE, 
sum(a.sales)  
from SALES_TABLE a ,STORE_TABLE b 
where  a.STORE= b.STORE
group by a.date, 
b.STORE