...

View Full Version : struggling with asp search - 2 entry fields!



waarbs
03-27-2006, 07:57 PM
I have a html page with one drop down menu and one text entry box.

I would like both results entered in both of these fields to be checked against my database.

I can do this for a single field (i.e. just type in and click 'search'), but for two elements I cannot find any help. The books I have read only cover a single field.

My html page is: http://munzamedia.co.uk/trip/catalogue.htm
-you can see the two fields here

My asp search code is this (for one element only):

SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"

How do I get the second field to work along with the first? I am not after a direct answer, just some advice as to where I can go to find out this information.

Thanks,
Mark

miranda
03-27-2006, 09:05 PM
since you are using a dropdown, is there a field in the database table that you are searching that corresponds to the items listed in the dropdown? if so then it is a matter of

SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"
SQLQuery = SQLQuery & " AND columnname = '"
SQLQuery = SQLQuery & Request.QueryString("location") & "'"

Now if it is not a specific value but possible multiple values in the column that you are searching then it is more like this

SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"
SQLQuery = SQLQuery & " AND columnname IN ('"
SQLQuery = SQLQuery & Request.QueryString("location") & "')"

Roelf
03-27-2006, 09:07 PM
SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"
SQLQuery = SQLQuery & " OR keywords LIKE '"
SQLQuery = SQLQuery & Request.QueryString("location") & "%'"

But you can also use AND instead of OR

Or if you want (and i think you do) to do an exact match for the dropdownvalue, use "=" instead of LIKE and lose the wildcards around the value. Perhaps the location is in another column of your db, then change the columnname after the OR into the name in your db

waarbs
03-28-2006, 07:54 PM
thanks for the input guys. I will give that a go this evening and let you know how i get on.

Will this code also work if the user selects an option (or types) into a single field, thus broadening the search?

mark

waarbs
03-28-2006, 08:15 PM
I tried all three of these codes - and they look good to me, but none of them work!

The code does parse but I never get any matches to the query, even though matches do exist in the dB.

If I just search using:

SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '%"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"

...this part of the code works fine. The problem happens when I add the second line, making:

SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '%"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"
SQLQuery = SQLQuery & " AND locations = '"
SQLQuery = SQLQuery & Request.QueryString("location") & "'"

...There is definately a column in my dB called 'locations'.

Any more ideas?

Thanks,
Mark

waarbs
03-28-2006, 08:28 PM
:thumbsup: FIXED!

this code works:

SQLQuery = "SELECT * FROM researchers WHERE keywords LIKE '%"
SQLQuery = SQLQuery & Request.QueryString("keyword") & "%'"
SQLQuery = SQLQuery & " AND locations = '"
SQLQuery = SQLQuery & Request.QueryString("location") & "'"

The problem was with my form. In 'value' I just had, 1, 2 etc instead of the string of the location name.

Thanks for the leg up...

Mark

miranda
03-28-2006, 10:34 PM
Whenever you have what is good code but you get no results, then before you open/execute the SQL statement use Response.Write to print it to the screen. This is something that you will get very accustomed to doing when you are debugging.

waarbs
03-28-2006, 10:46 PM
That is something I was wondering about, debugging. In actionscript I use the trace command and and I wanted something similar when testing my asp's.

Do you have an example of what you mean Miranda? What should I write to the screen first before executing my SQL statement?

Thanks,
Mark

Roelf
03-29-2006, 07:43 AM
Before executing the Sql in the db, do:
Response.Write SQLQuery
Response.End

That way your sql which is going to the db is displayed in the browser and you can check if it is okay

waarbs
03-29-2006, 10:46 AM
I'll try this later in the week. Thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum