Using <, > = in search form fields. (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Chugachman -> 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



Spooky -> RE: Using <, > = in search form fields. (2/9/2004 8:16:01)

What does the current SQL statement look like? (its in the red source code)



BeTheBall -> 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!



Chugachman -> 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!



BeTheBall -> RE: Using <, > = in search form fields. (2/9/2004 11:22:17)

We aim to please. [;)]



Chugachman -> 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 -> 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.



Chugachman -> 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 -> 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 -> 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?



Chugachman -> 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 -> 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



Chugachman -> RE: Using <, > = in search form fields. (3/4/2004 13:08:43)

I;m not understanding what you're saying here.



BeTheBall -> 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



Chugachman -> 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 -> 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.



Spooky -> RE: Using <, > = in search form fields. (3/4/2004 15:21:43)

SQL injection is a concern - The DRW does do sufficient checking of input, however check boxes / radio button are no guarantee of safety, as the same form can be posted in a different way.
The DRW method should suffice, but any custom code which uses unvalidated Request("input") as a variable or input into an SQL string is vunerable.

SQL Injection White paper



Chugachman -> 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 :-)



Spooky -> RE: Using <, > = in search form fields. (3/4/2004 16:15:58)

I think youll be safe there ;-)
SQL attacks can be used to leverage attacks on the server, so its not just your website we talk about - it can be the whole network (if using SQL server)



BeTheBall -> RE: Using <, > = in search form fields. (3/4/2004 16:28:06)

Thanks Spooky. Guess the DRW can on occassion be a better choice.



Chugachman -> 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 -> 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"



Chugachman -> 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 -> RE: Using <, > = in search form fields. (3/7/2004 12:58:36)

THE CODE WORKS!

Duane: You are the best!



BeTheBall -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375