Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Apr 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    struggling with asp search - 2 entry fields!

    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

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,063
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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") & "')"

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    I am the luckiest man in the world

  • #4
    New Coder
    Join Date
    Apr 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #5
    New Coder
    Join Date
    Apr 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    New Coder
    Join Date
    Apr 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #7
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,063
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.

  • #8
    New Coder
    Join Date
    Apr 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    I am the luckiest man in the world

  • #10
    New Coder
    Join Date
    Apr 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll try this later in the week. Thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •