database results mix and match

[robert]

I have set up a separate asp page to retrieve records and display only the results within the specified town ( I set that up via Field, Equal, Value “specified town†) That works great, it only calls up the records with the specified towns. Now I also wish to retrieve a category such as antiques, but only the antiques located in the above towns.
Is this even possible, if so, any idea where to begin and what to research?
Thanks

 

[Thomas A. Rowe]

If you were hand coding your ASP, you would create your Query similar to the following:

<%
Dim Twn
Dim Cat
Twn = Request.QueryString ("Town")
Cat = Request.QueryString ("Category")
%>

sql = "Select * From tablename WHERE Town = '" & Twn & "' And Category = '" & Cat & "' "

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

I have set up a separate asp page to retrieve records and display only the results within the

specified town ( I set that up via Field, Equal, Value "specified town" ) That works great, it only
calls up the records with the specified towns. Now I also wish to retrieve a category such as
antiques, but only the antiques located in the above towns.

 

[robert]

That’s great, you know Greek… unfortunately, I’m not hand coding. Can I insert that code with the necessary adjustments (of course) into FP DBW region? Probably not, nothing is ever easy and it has been one of “those†days, make that “those†weekends. Every time I think I’m getting close to finishing I hit a wall bigger than the last.

 

[Thomas A. Rowe]

Robert, it just may be time to think about learning to hand coding, as you will be able to
accomplish a lot more outside of using the FP database components and in many cases a lot less code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

That's great, you know Greek. unfortunately, I'm not hand coding. Can I insert that code with the

necessary adjustments (of course) into FP DBW region? Probably not, nothing is ever easy and it has
been one of "those" days, make that "those" weekends. Every time I think I'm getting close to
finishing I hit a wall bigger than the last.

 

[Jon Spivey]

Hi,

First of all assuming your search form looks something like this

<form method="get" action="results.asp">
enter your town <input type="text" name="town">
enter your category<input type="text" name="cat">
<input type="submit">
</form>

Now insert a database results wizard onto results.asp and in step 2 choose
custom query type
select * from table where town='::town::' and category='::cat::'

set the rest of the wizard options to suit. In a perfect world you'd spend
ages learning how to hand code but for a job like this the DRW can meet your
needs quickly and reasonably well.

Jon
Microsoft MVP - FP

That's great, you know Greek. unfortunately, I'm not hand coding. Can I

insert that code with the necessary adjustments (of course) into FP DBW
region? Probably not, nothing is ever easy and it has been one of "those"
days, make that "those" weekends. Every time I think I'm getting close to
finishing I hit a wall bigger than the last.

 

[robert]

Yes, actually I do plan on learning asp.net because I would like to (eventually) incorporate photos into my records.
But, for now I just need to get this done as I planned. I’m already 12 days late finishing and it now absolutely must be done by Friday. So as you can see I’m on a tight schedule.
Having a hard time trying to understand or even get information about the comparisons within the more options criteria. The following descriptions and what they mean and relate to other Ids or categories or whatever. This information is not to be found, perhaps that’s because it’s comprehensible to most, but not me. The descriptions and what they mean have no meaning to me. But you don’t use the DBW and that is where these options are. Perhaps you can refer this to somebody who does, and is working the discussions group now like you are.
Operator Description
= Equal

Greater than < Less than
= Greater than or equal

<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern

Frontpage does not explain these options.
The greater and less than.. is that for numeric strings, i'm assuming
I cant find explanations to these descriptions anywhere
Help!

 

[Thomas A. Rowe]

Jim and Kathleen have both replied with info about using the Equal, Not Equal, etc. operators when
using the FP database components.

The Operators are basic VBScript operators, they are not unique to the FP Database Component, anyone
using ASP will have to use them at some point.

For a reference on the VBScript language visit:
http://www.microsoft.com/frontpage/downloads/addin/searchdetail.asp?a=149
Download and install this FP Help menu add-in.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Yes, actually I do plan on learning asp.net because I would like to (eventually) incorporate photos into my records.
But, for now I just need to get this done as I planned. I'm already 12 days late finishing and it

now absolutely must be done by Friday. So as you can see I'm on a tight schedule.

Having a hard time trying to understand or even get information about the comparisons within the

more options criteria. The following descriptions and what they mean and relate to other Ids or
categories or whatever. This information is not to be found, perhaps that's because it's
comprehensible to most, but not me. The descriptions and what they mean have no meaning to me. But
you don't use the DBW and that is where these options are. Perhaps you can refer this to somebody
who does, and is working the discussions group now like you are.

 

[robert]

Thanks for helping
Gave it a try, but it didn’t work.
My SQL statement currently shows like this:
SELECT * FROM Results WHERE (Neighborhood = 'OAHU_Central' OR Neighborhood = 'OAHU_Honolulu' OR Neighborhood = 'OAHU_Leeward' OR Neighborhood = 'OAHU_North_Shore' OR Neighborhood = 'OAHU_Southeast' OR Neighborhood = 'OAHU_Southwest' OR Neighborhood = 'OAHU_Windward' OR Category = 'Antiques/Collectibles')

I also tried putting in the :: like you had and removing the spaces and ).
Isn’t there a way to do this without the custom query option and dealing with the more options criteria Comparisons and all of that?
Cannot get info on comparisons or how to use them, it seems that comparisons are a mystery but I have a feeling it’s the key to this puzzle.
For instance: I want the results page to retrieve the Antiques/Collectibles records from just the Island of Oahu towns, (no other island, other islands are in my submittal form)
my brain hurts.............

 

[Kathleen Anderson [MVP - FP]]

Robert:
I would suggest trying this:

SELECT * FROM Results WHERE ((Neighborhood = 'OAHU_Central') OR
(Neighborhood = 'OAHU_Honolulu') OR (Neighborhood = 'OAHU_Leeward') OR
(Neighborhood = 'OAHU_North_Shore') OR (Neighborhood =
'OAHU_Southeast') OR (Neighborhood = 'OAHU_Southwest') OR (Neighborhood =
'OAHU_Windward')) AND Category = 'Antiques/Collectibles')

I would also suggest that you read some of the resource links given to you
in other posts to help you understand what works, and why.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/
Connect to the Newsgroups with Outlook Express
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.html

 

[Kathleen Anderson [MVP - FP]]

SELECT * FROM Results WHERE ((Neighborhood = 'OAHU_Central') OR
(Neighborhood = 'OAHU_Honolulu') OR (Neighborhood = 'OAHU_Leeward') OR
(Neighborhood = 'OAHU_North_Shore') OR (Neighborhood =
'OAHU_Southeast') OR (Neighborhood = 'OAHU_Southwest') OR (Neighborhood =
'OAHU_Windward')) AND (Category = 'Antiques/Collectibles'))


--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/
Connect to the Newsgroups with Outlook Express
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.html

 

[Jon Spivey]

You could probably simplfiy this down to

SELECT * FROM Results
WHERE Neighborhood = LIKE 'OAHU%'
AND Category= 'Antiques/Collectibles'

Sounds like any neighbourhood that starts with Oahu is acceptable - If
there's some OAHU neighbourhoods we dont want to return there's no need for
all those brackets in the query. This would accomplish the same thing
SELECT * FROM Results
WHERE
(Neighborhood = 'OAHU_Central' OR Neighborhood = 'OAHU_Honolulu' OR
Neighborhood = 'OAHU_Leeward' OR
Neighborhood = 'OAHU_North_Shore' OR Neighborhood =
'OAHU_Southeast' OR Neighborhood = 'OAHU_Southwest' OR Neighborhood =
'OAHU_Windward') AND Category = 'Antiques/Collectibles'

Robert - I think we need to know your business requirements. What does your
site do and what would you like your users to search on? For the moment
forget about how you accomplish what you want to just tell us what you want
to acconmplish. Please dont use the phrase "it didn't work" thats useless -
tell us what you wanted to happen and what actually did happen

Jon
Microsoft MVP - FP

 

[robert]

Kathleen,
You did it!!
The secret was in the (( and ))
I did print and read your earlier post, but could not find info about what the comparisons mean and how to apply them. I guess it's just me, I'm clueless when it comes to this stuff, but i have learned alot along the way.
What ever you guys & gals get paid it's not enough!
Thank you.
Robert

 

[robert]

Hello Jon,
Kathleen found the key with (( and ))
Yes, i read about the wildcard % as this could save alot of typing
Thanks for helping me get through this
robert

 

[Andrew Murray]

In your query you would use the AND clause as in

select from TABLE where Furniture contains "Dining Room Chairs" AND Town contains
"Chicago".

so that would list all antique dining room chairs in Chicago.


Redo the results wizard (if that's how you did it, or did you code your asp from
scratch?).

and include a second 'results criteria Town Contains *town*.

Your search form can have two fields and a match on both fields has to be made
before any positive results are returned, if using OR then it would return
results for either field.

I believe you can also use the query string tacked on the end of the URL as in
..../search.asp?Town=Chicago&furniture="dining room chairs"

(similar thing anyway).




*select from TABLE <whatever>

I have set up a separate asp page to retrieve records and display only the

results within the specified town ( I set that up via Field, Equal, Value
"specified town" ) That works great, it only calls up the records with the
specified towns. Now I also wish to retrieve a category such as antiques, but
only the antiques located in the above towns.

 

[Andrew Murray]

it helps to know a little about SQL query language, boolean alegbra and
relational databases as well.

That's great, you know Greek. unfortunately, I'm not hand coding. Can I insert

that code with the necessary adjustments (of course) into FP DBW region?
Probably not, nothing is ever easy and it has been one of "those" days, make that
"those" weekends. Every time I think I'm getting close to finishing I hit a wall
bigger than the last.