getting the correct code for project

[gcapp]

Ok, if there is anyone out there who can help me with this one, I would appreciate it.

Let me explain what I am trying to do.

I have a database that contains listings of businesses that have the name, address, city, etc - and the category that the business belongs in. The categories are restaurants, camping, shopping, etc.

What I am trying to do is have it where you click on a link for a category (restaurant, camping, etc) on the main page, then it takes you to a page that will give you a list of the cities for the businesses in that category - then you click on a city and you get a listing of the businesses for that city, in that particular category.

It's not as complicated as it sounds. I can't seem to come up with the correct asp code for doing this. If anyone knows the code for accomplishing this - it would be much appreciated.

Thanks,
Gary

[raf]

I know the code for doing this. (my problem is telling you how to do it, because you need to deal with some datadesign, creating a few form (whith listboxes that are populated from the database) and some sql (and a tiny bit of asp)

it quite a lot to explain in one post. it's probably best if i take a chunk out of one of my apps and moderate it a bit for you, so you'll be hearing from mee (soon).

[raf]

ok. check the file in attach. contains database + html + asp files.
startpahe = select.html

rereading your post i realised you wanted to limit the based on the category. in my mini app, they are independent. i'll change it if neccessary.

you'll see that i added a "select" option in the resultpage. the customerID is passed along via the querystring (move pointer above link and check statusbar) when a user is selected. With that key, you then can select/insert other info for that client.

[gcapp]

Raf,
I appreciate you taking the time to help me. What you sent me is a start to what I am trying to do. The only change that I would need to this point is when you go to the select.html page. I would need it where the cities for a particular category to be in the drop-down list and not give people the choice to choose a category.

What I mean is on my home page there will be links to camping, restaurants, shopping, etc. When someone clicks on one of those links, they are taken to a page that gives them a drop-down list of the cities for that category. They can't choose another category from there - they would have to click on the links that will be on the page.

Then I was hoping that when someone chose a city, the listing of businesses would show with the details. The way you have it now, each business is listed, but you have to click on View Info to get more information. I was hoping to avoid that and just have a list of the businesses show with their addresses, ciyt, phone, etc.

But what you have so far is a good start for me. Like I said I appreciate you helping me. If you can come up with something closer to what I am asking that would be great. If not, then that is fine too.

Thank you.
Gary

[raf]

I can make exactly what you want. just enter your visacard number :)

i'll try to "customize" it for you tomorow. (only have access to PC and internet on my job). It's not such a big deal to make something like this.

About the business-listing of mine. I always do it lake this, because it goes a lott quicker. maybe not in your cas, buth whan you are selecting on a database with 2 miljon customers, and you want to show data that is spread over 10 tables ...
(those are choices you must make, depending on datastructure, #connections you want to make, #users ...)

[raf]

in attach you'll find a customized version.
startpage = start.asp. you'll see their are two options to choose a categorie. check them out both, because i've used different selectquery's (the results are the same buth they are ordered different. the city's orderded by # of businesses within the category could be usefull. if you display the resulting citys in a table with a "select"-link (cfr result.asp from OK1.zip), then you could aso display the count -numbers. of course, you could also use other sorting criteria : distance, rating, ... buth then you have to specify these things in the business-table)

what you want to show as a result, and the layout for that, is easy to customize. just modify the select-statement to include other variables.

jut let us know if you have further question or run into problems (from 07/09 to 30/09 i wount be answering, buth their are plenty of others that will !)

[gcapp]

Raf,
First of all I'd like to thank you for your help to this point. I'm at a point that I can't get your scripts to work. This what I have done.

First I have a table that I already created some time ago for Businesses that I can't really alter too much or it won't work with my other asp pages. I'm guessing that things aren't working maybe because of it.

What I have done is attach the files that I am trying to work with. I couldn't attach the database as it is too big, so I have a text document describing how my database structure is.

Now I can't alter the Business_category table as I am using it with another asp page.

What I tried to do is look at your code and substitute your names for tables and fields for what I have. Well it didn't work.

I renamed the pages and I have attached them as well.

Maybe you can see the mistake I have made.

I didn't use you start.asp page. I just used the links that I attached to the images I am using.

I am stuck and i have looked at this over and over.

If you can help that would be terrific.

Thank you.
gary

[raf]

Gary,

I looked at your database description and files and see some problems and reasons why it doesn't work.

The code is no problem. Just a few bugs that I can remove.
The database is something else. Now, I can get it working (i presume) buth i 'd suggest you change at least the datatype for Businesslistings.Category and Business_category.Category_ID to number. (I also spotted some other 'text' variables that better would be changed.)

So, i'll try to straighen out your files, based on your actual database. If you change the datatypes, you'll have to modify the sql in the asp-pages (text values should be inclosed in '' , numbers not. so 5 will work just fine if the Category_ID is of datatype number, buth woun't work if you've gat datatype text. then it should be '5' )

Also, about your code. If you wnat to change it : first try to see the html code (like you would write it manually), and the replace the things you want to be created dynamically using asp.

i mean : if you have a form, you'll have a <form> and </form> in your code, with all th form info in between. When the formelements, like a listbox, or populated using asp, all the aspcode for this 'populating' should be between the two tags. If you look at your code, you'll see were you went wrong.)

i hope i'll be able to corret it this morning.

[raf]

Version number 3.

I little change off plans : i set up a database with the same structure as yours and modifyed the files from ok2.zip. (i couldn't do a lot with your code. sorry. to much includes, to much chaos because you copyed and deleted and ...)
if you'd change the connectionlines (read the word-file) in the 4 asp's then you should be abble to view the results you want.

then you can start modifying (read the word-file !)

i used to think that i didn't have to know how to write html-code (i'd use a script-generator), until i started working with asp. maybe it's the same with you ;)

buth hey ! you'll pick it up, if you work a bit with it. so try this one on, and let us know if you've got any problems.

[gcapp]

Raf,
Again I appreciate you trying to help me but I am running into a wall. I made the changes you suggested and I get nothing. All I get is a blank screen.
So to make this easier, I'll give you the link, so you can see what is happening and again I will attach the pages in question and a text file explaining exactly what I did.


Here is the initial link to my page:
www.enchantedmountains.info/asp/home.asp (http://www.enchantedmountains.info/asp/home.asp)

Again I'm really sorry to bother you, but I am stuck and i don'tknow what to do.

Thank you very much,
gary

[gcapp]

Raf,
Well I discovered an error and I don't understand why it is happening. The error is:

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'UPLOAD_DSN'

/asp/citysearch.asp, line 10


Now this line of error I don't understand because I have the same definitions for all my asp pages and they work. So why is this happening?

Again if you have any solutions for me I'd appreciat eit.

Gary

[gcapp]

Raf,
Ok, with the first error I listed on the last post, I got rid of the <% option explicit%> tag and then it comes up with this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/asp/citysearch.asp, line 30

which is the rsCity.Open sql, Conn line.

So now I really don't know what to do.

Help!

Gary

[whammy]

I should be able to help you with that if you post the code (I see that error all the time at work, not necessarily with my code ;) ).

Usually someone is trying to use a variable in place of an array, or the wrong datatype (which is the same thing). Sometimes it boils down to a simple syntax error.

[raf]

gcapp,

been away on vacation.
about "undefined variable problem" : the 'option explicit' on top of the page means that you must declare ('dim') all the variables used on that page. (prevents typos) i always include it, because it prevents errors. (if you put it back, declaring the undefined variable will solve the problem. exmp : dim UPLOAD_DSN

about type mismatch:
now, the errocode points to line 30, buth that's not were the error is. The error wille be in the sql string (few lines higher. sql string is read in line 30)
i've tryed to explain the problem in the previous post +in word doc + in source of files.
the problem lyes in the datatypes in your database. If a variable is from datatype text, then the value need to be enlosed in quotes, for datatype number there shouldn't be quotes.
by the looks of it, you need to omit the quotes from acat ('acat' to acat ) or change the datatype for businesslistings.Category in your database

[raf]

gcapp,

i checked the link to your site, an as far as I can see, it works.
do you still encounter problems ?

[gcapp]

Hey Raf,
Yes it does!!!! I knew you were away so I Wanted to wait before I got back to you. Thank you so much for helping me with that.
It's nice for beginners like me to get such great help!

Thank you again!
Gary

[raf]

your welcome.

good luck with your site.