|
| |
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
 |
Using <, > = in search form fields. - 2/8/2004 20:32:20
I'm going nuts trying to figure this out.
I have an Access DB field with numbers (hiking trails-miles one way). I'm trying to create a search form which includes that field with options such as less than 3 miles, less than 6 miles, greater than 12, etc.
In the DBR wizard (according to an MS document) I put "1 OR 1" (no qoutes) in the default value. In the search field which I converted to a drop box, I have the values < #, > #, etc for each option (ie: Option is 0-3, value is < 4)
When I do a test I keep getting a syntax error that I don't have an operator.
Am I doing something wrong or is it just not doable this way. Can I only put in a # and not be able to use <, >. If so, is there an alternative way of doing this?
Thanks! I'm so close to have a real search form and this is the last piece of the puzzle.
here is the test page to show the error message:
http://www.akhs.atfreeweb.com/fpdb/TestPages/test14.asp
(doesn't matter = 1 OR 1 which is supposed to bring up all values which it does but if I put in any other option, it gives the error message)
Here is my site in which I'm trying to get this info on. If you go to Search Hikes, you can see the simple ASP pages I created that does not string together criteria.
www.akhs.atfreeweb.com
|
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 2/9/2004 10:59:44
What you are trying to do will likely require that you customize the SQL. Since FrontPage will want to undo your changes, you will first want to apply the Spooky Diet. Learn more here
Then, I would change the values for your dropdown so they are exactly like this:
Between 0 AND 3
Between 4 AND 6
Between 7 AND 10
Between 11 and 15
Between 15 AND 1000
Then, you would change your SQL to something like:
SELECT * FROM YourTable WHERE MilesField ::YourDropDownName::
Of Course you need to change "YourTable" to the name of your table, "MilesField" to the actual name of the field that holds the miles and "YourDropDownName" to the name of your drop down field in your form.
When a value is selected from the dropdown the SQL will translate to something like:
SELECT * FROM YourTable WHERE MilesField Between 4 AND 6
Good luck!
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 2/9/2004 11:10:20
THANK YOU! THANK YOU!
I've been posting this message everywhere and you were the only one who had an answer for me. I can't wait to come home from work and try this.
I checked Spooky's other webpages and this is now my number one place to go!
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 2/10/2004 1:10:08
http://www.akhs.atfreeweb.com/fpdb/TestPages/mowtest_backup.asp
YEEEEHAWWW! It works...(so that macro for the diet..)
One small glitch.
Some hiking trails I describe on my site have numerous trails so I leave the miles one way (MOW) field blank.
What is the value to bring up blank fields for the "Numerous trails" and also to include those trails for "All Hikes"
I tried for the Numerous Hikes
= Null
=""
= NOT Between 0 AND 1000
For All Hikes I tried
= Between 0 AND 1000 OR Null (which only brings up hikes with a number)
If I have to, I will but I really don't want to use 0 or say 100.
Thanks again for the help. I have all the different criteria sections ready to go. Now I just have to figure how to string them together
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 2/10/2004 8:23:23
I believe you need IS NULL, with no "=".
To get all, try BETWEEN 0 AND 1000 OR IS NULL.
Think that should work.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 2/10/2004 20:34:19
http://www.akhs.atfreeweb.com/fpdb/TestPages/mowtest_backup.asp
For Numerous trails, "Is Null" did the trick. However for All Hikes, what finally worked was going back to the tried and true "1 OR 1" (for other people reading this, just don't put it in the default value box in the DBRW like it says in the MS article).
Thanks Duane! Like I said before, this was the finally piece. I hope to put the entire query together soon but next week as I need to get away from this pute, put on a backpack and head out on the trails.
If you're ever in Alaska, give me an E and I'll take you on a great hike.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/3/2004 19:35:41
The obstacles never end.
I'm working on my Miles from Anchorage Field. I want the search criteria to include, say, "between 0 AND 30" Miles in the drop down menu value as well as the "Is Null" to include trails that are in town. I'm assuming it should be
Name is: MFA
Value is: between 0 AND 30 OR Is Null
but I get an error message.
I could make the null values 0 in the database but would like to avoid it.
here is the test page: http://www.akhs.atfreeweb.com/search_hikes_newasp.htm
Duane: as you can see, the whole search is slowly getting there. I fixed the bug with distance and decided to go a different way with search. This is how I'd like the search to be for now (with some aesthetic changes), then later when work isn't so busy to take it to the next level. Your opinion would be greatly appreciated.
PS: Check the thanks below!
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 3/3/2004 20:59:26
The problem with the above is this:
The WHERE clause seems to only work like this:
WHERE MFA BETWEEN 0 AND 30 OR MFA Is Null
With your dropdown value you get a WHERE clause like this:
WHERE MFA BETWEEN 0 AND 30 OR Is Null
See the difference?
What should work is putting the entire WHERE clause in the value of your dropdown and changing your SQL from:
WHERE MFA ::DropdownName::
to
WHERE ::DropdownName::
So for example, the dropdown value for 0 - 30 would become:
MFA BETWEEN 0 AND 30 OR MFA Is Null
That in turn will create a proper WHERE clause for your SQL.
Does that make sense or am I rambling here?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 0:41:14
Duane, you are truly the God of SQL!
I made the changes; unfortunately I can not see the results yet as my server is down (some connection went kaflooey in LA). I'll let you know.
I'm hoping to have the entire page done over the weekend. As soon as work mellows out, I'd like to take it to the next level and turn all the searches into one query.
Thanks again!!!!!!!!!!!!!!
Bill
| |
|
|
|
alansmith
Posts: 105
Joined: 2/22/2004
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 11:50:22
Have you ever tried the following on sql server based web sites?
<edit>
We are here to promote awareness, not methods
< Message edited by Spooky -- 3/4/2004 15:15:44 >
_____________________________
Website Design in Devon.:.Web Design Portfolio.:.Seaton Website
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 13:08:43
I;m not understanding what you're saying here.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 13:20:13
quote:
Have you ever tried the following on sql server based web sites?
Are you hinting at SQL Injection? If so, it should not be a problem here. All his search options are dropdowns and checkboxes, not text fields. However, if I am missing a potential vulnerability, please elaborate.
Chugachman, for more info. on SQL injection attacks, see here:
http://www.4guysfromrolla.com/webtech/061902-1.shtml
< Message edited by Spooky -- 3/4/2004 15:07:44 >
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 14:23:34
Do I really need to worry about this? My database is a collection of Hiking Trails in Alaska, not a bunch of credti card numbers.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 14:41:23
In my opinion, no you are fine. As I said above, you have no text boxes that would allow a user to input whatever they want. However, it is best to be aware of this issue and how to deal with it. As I look at sites I have created in the past year, I see several holes. Even though your site is simply trails in Alaska, a malicious user could concievably try to delete records using a querystring as outlined in the article I referenced above. As I said, it is best to be aware, but your "should" be fine. In reality, Alansmith's post is a little cryptic so I am not sure that is even what he is hinting at. Don't lose sleep over it.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 16:09:51
Hopefully, there won't be any Al Queda sleeper cell waiting to do damage to American Hiking websites :-)
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 3/4/2004 16:28:06
Thanks Spooky. Guess the DRW can on occassion be a better choice.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/7/2004 0:11:20
Have I crossed the line into annoying?
Arrggghhhh!
OK As you probably know, I want to comine in one search query trail miles one way AND miles from Anchorage.
For some reason, the miles from Anchorage seems to overide the miles one way.
Here is the ASP code.
<%
fp_sQry="SELECT * FROM AKHS WHERE (::MOW:: AND ::MFA::) ORDER BY ::soco:: ASC,Trail ASC"
fp_sDefault="MOW=&MFA=&soco=Trail"
fp_sNoRecords="<tr><td colspan=16 align=left width=""100%"">I'm sorry. No trails match your criteria. Please broaden your search.</td></tr>"
fp_sDataConn="akhs"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
or is the problem in the search form.
<form method="POST" action="fpdb/Distance.asp">
<p align="left"><b><font face="Arial" size="2" color="#800000"><select size="1" name="MOW">
<option selected value="MOW = 1 OR 1">All</option>
<option value="MOW Between 0 AND 3">Less Than 3</option>
<option value="MOW Between 0 AND 6">Less Than 6</option>
<option value="MOW Between 0 AND 9">Less Than 9</option>
<option value="MOW Between 0 AND 12">Less Than 12</option>
<option value="MOW Between 12 AND 1000">Greater Than 12</option>
<option value="MOW Is Null">Numerous Trails</option>
</select><br>
Miles One Way<br>
<br>
and<br>
<br>
<select size="1" name="MFA">
<option selected value="MFA = 1 OR 1">All</option>
<option value="MFA Is Null">In or Near Town</option>
<option value="MFA Between 0 AND 30 OR MFA Is Null">Less Than 30</option>
<option value="MFA Between 0 AND 60 OR MFA Is Null">Less Than 60</option>
<option value="MFA Between 0 AND 120 OR MFA Is Null">Less Than 120</option>
<option value="MFA Between 120 AND 1000">Greater Than 120</option>
</select><br>
Miles From Anchorage<br>
<br>
</font><font face="Arial" size="2">Sort
by Trail and:<br>
<br>
<select size="1" name="soco">
<option selected value="Trail">No Preference</option>
<option value="MOW">Miles One Way</option>
<option value="MFA">Miles From Anchorage</option>
<option value="DA">Difficulty</option>
<option value="Hoof">Moose Hoof Rating</option>
</select></font></b><br>
<br>
<input type="submit" value="Submit" name="B1"></p>
</form>
Soooo close and yet so far.........
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 3/7/2004 12:42:46
Do you have a link to this latest endeavor? I am wondering if the nulls are causing the problem. Technically, null is not a viable option here. Every trail is either in Anchorage, which is zero miles or it isn't, 1-1,000,000. You also may have to play with the parenthesis a little, maybe:
fp_sQry="SELECT * FROM AKHS WHERE ((::MOW::) AND (::MFA::)) ORDER BY ::soco:: ASC,Trail ASC"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/7/2004 12:49:48
I try the code thing but I believe you're right.
Changing the null to 0 wouldn't be a big deal
Thanks!
| |
|
|
|
Chugachman
Posts: 63
Joined: 2/8/2004
From: Alaska
Status: offline
|
RE: Using <, > = in search form fields. - 3/7/2004 12:58:36
THE CODE WORKS!
Duane: You are the best!
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using <, > = in search form fields. - 3/7/2004 19:16:55
Kind of reminds me of learning Spanish. Can't tell you how many times I guessed at words thinking there was no way they could be right, but yet they were. Same seems to hold true here.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
