syntax error

[gcapp]

Can someone help me with this error?

If you go to this link:

Outdoor Fun (http://www.enchantedmountains.org/asp/outdoor_fun.asp?Parent=1200) and click on any category you will get an error. The error occurs in this line of code:


'Open recordset
sqlMembers = "SELECT Businesslistings.BusinessID, Businesslistings.Image_ID, Businesslistings.Category, Businesslistings.Business "
sqlMembers = sqlMembers & "Businesslistings.Address, City.Citylabel, City.Cityzip, Businesslistings.Phone, Businesslistings.Phone2, Businesslistings.Fax "
sqlMembers = sqlMembers & "Businesslistings.Website, Businesslistings.Email, Businesslistings.Information "
sqlMembers = sqlMembers & "FROM City INNER JOIN Businesslistings ON City.CityID = Businesslistings.City WHERE Category LIKE '%" & sParent & "%' ORDER BY Business;"
Set rsMembers = Server.CreateObject("ADODB.RecordSet")
rsMembers.Open sqlMembers, UPLOAD_DSN

I have the UPLOAD_DSN declared, so could someone tell me why I am getting a missing operator error?

Thanks,
Gary

[gcapp]

Ok here is the true error I am getting so ignore the last post.

If you first go to this link:
Restaurant (http://www.enchantedmountains.org/asp/citysearch.asp?Category=1300) and pick a city, you will get a list of businesses and notice that actual city name is listed.

Now go to this link:
Outdoor Fun (http://www.enchantedmountains.org/asp/outdoor_fun.asp?Parent=1200) and choose Golf. You will get a list of courses and notice that the city is not the name but the cityID that is in my database field.

There are three tables involved here: Businesslistings, City and Category. These tables have INNER JOINS that are as follows:

Businesslistings.City is joined to City.CityID and
Businesslistings.category is joined to Category.CategoryID

Now, I have attached three pages to this note - citysearch.asp and searchresults.asp go with the Restaurant page and outdoor_fun.asp goes with the Outdoor Fun page. These pages work correctly, BUT the outdoor_fun.asp page does not pick up the city name from the city table.

So I used Access and came up with an sql statement and substituted it in the outdoor_fun.asp page and the true error comes out. The error is that it says that it cannot find the City. So I took the sql out and put back the original code.

The sql statement I used is this:
SELECT Businesslistings.BusinessID, Businesslistings.Category, Businesslistings.Business, Businesslistings.Address, City.Citylabel, City.Cityzip, Businesslistings.Phone, Businesslistings.Phone2, Businesslistings.Fax, Businesslistings.Website, Businesslistings.Email, Businesslistings.Information
FROM City INNER JOIN Businesslistings ON City.CityID = Businesslistings.City;

Now on the outdoor_fun.asp page I put ????????????????????? on the page to signify where the sql needs to be changed I think. Further down on that page there is the part where it will list each item: Business, Address, etc and I think a problem might be where it says rsMembers("City") but i don't know.

I'm hoping someone can help me with that sql and figure out how I can get the city name to show instead of the cityID.

If anyone has any ideas, I would appreciate the help. I've been stuck for a week on this!

Gary

[dfrancis]

Maybe I'm just dense... but I can't see where you ever open the city table in the outdoor page. I can see it in the citysearch page...

I even did a text search for the word CityLabel and cam up NULL.

From the citysearch page I can see your SQL access the tables... but there is no attempt in the Outdoor page... is it in an include? Is the sql str somewhere else?

The only reference I can see is rsmembers("city") on the outdoor fun page... is this where you expect to receive the City.Citylabel information. If so, I think you need to redo the sql str to access the table as you did in the citysearch page.

It's kinda hard without knowing all the little details. :)

'????????????????????ERROR IS SOMEWHERE IN THE SQL BELOW??????????????????????????????
'Open recordset
sqlMembers = "SELECT * FROM " & MEMBER_TABLE & " WHERE Category LIKE '%" & sParent & "%' ORDER BY Business"
Set rsMembers = Server.CreateObject("ADODB.RecordSet")
rsMembers.Open sqlMembers, UPLOAD_DSN


'Category Trail
sqlTrail = "SELECT Category_Name FROM " & CATEGORY_TABLE & " WHERE Category_ID = '" & sParent & "' "
Set rsTrail = Server.CreateObject("ADODB.RecordSet")
rsTrail.Open sqlTrail, UPLOAD_DSN
Response.Write("<p> <font face=arial size=5 color=#003366><b><u>" & rsTrail("Category_Name") & "</u></b></font>")
rsTrail.Close
Set rsTrail = Nothing

Where in here did you look for City.Citylabel ?

Perhaps I should not have tried... but in that I reviewed it for the last 30 minutes, I felt I had to say something even if it did make me look like an idiot. :eek:

dfrancis

[gcapp]

dfrancis,
First I appreciate you looking at my code.
What you are asking me is the problem. I'm trying to figure out how I can alter my sql to include the City.Citylabel records. You see this code works fine except that it doesn't tap into the city table. So I'm trying to find out how I can alter the sql so it still grabs everything that it does, but also the Citylabel from the city table.

When I tried to substitute the sql from my previous post for this line of sql:

sqlMembers = "SELECT * FROM " & MEMBER_TABLE & " WHERE Category LIKE '%" & sParent & "%'

It still would not grab the citylabel from city table and I would get an error.

As you said it may have to do with the rsMembers("City") line. Yes, that is where I want the citylabel to come in to.

I included all three pages before, so oyu could see how the sql statements were set up and how that line of sql I want altered, might be changed.

You know when you look at the searchresults.asp page - the results are display through the rsResult.Fields ("Citylabel"), well I don't know if that has anything to do with it or not.

I was hoping that someone may have an idea of what I could do without changing the code in the citysearch.asp and searchresults.asp pages.

If you can help I would appreciate it.

Gary