Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0

    Help With Specific Search Filter


    Hey guys,

    I'm essentially done with a little project I've been working on and I have just one thing that isn't quite working like I'd hoped.

    I have a search that gets run from a drop down selection (using a jumpmenu....my way of filtering thru the SQL DB) and I'm not getting the specific search result that I want. I'd like to have my search string look for an exact match to a recordset, whether it be using quotes (not working) or some other way.

    Here's some of the code I'm using:

    Code:
    <option value=search.asp?searchTerm=Level1&Submit=Search>1</option>
    <option value=search.asp?searchTerm=Level2&Submit=Search>2</option>
    ...and so on

    The above line of code is one of many items that appear in a drop down menu, so when you select this item "1" it is searching the SQL DB for the term "Level1". However, when it does this, it also returns other items like "Level11" or anything else that has a number "1" in it. So rather than getting just the actual records for "Level1" it returns "Level14", Level10"...etc.

    Is there a way I can structure this search term to look for an exact term, "Level1" and only get the recordsets that are just "Level1"?

    Thanks in advance!


    Jerome
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    yes you can do that. publish your SQL statement it looks like your sql needs some adjustments
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    Here's my search string:

    Code:
    Set myRecordSet = Connect.Execute ("SELECT * FROM Data WHERE Picture LIKE '%" & searchInput & "%' OR PP LIKE '%" & searchInput & "%' OR Level LIKE '%" & searchInput & "%' OR Year LIKE '%" & searchInput & "%' OR Make LIKE '%" & searchInput & "%' OR Model LIKE '%" & searchInput & "%' OR Price LIKE '%" & searchInput & "%' OR Drivetrain LIKE '%" & searchInput & "%' OR Power LIKE '%" & searchInput & "%' OR Weight LIKE '%" & searchInput & "%' OR Torque LIKE '%" & searchInput & "%' OR Country LIKE '%" & searchInput & "%' OR Condition LIKE '%" & searchInput & "%' OR Tradable LIKE '%" & searchInput & "%' ORDER BY Model ASC;")
    So if I'm understanding you right, perhaps I should change the search string for 'Level' to something other than 'LIKE'?

    If I need to delete the table to change the 'Level' column data type or whatever, that's totally fine as I only have 2 records in it so far.


    Jerome
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    it is simple you have LIKE '%" & searchInput & "%' ....
    just change to LIKE '%" & searchInput & "%'
    add space between % and " and between " and % space is a character, so your sql will serach from spaceLevel1space and etc.
    so your sql shohuld be like that

    Code:
    Set myRecordSet = Connect.Execute ("SELECT * FROM Data WHERE Picture LIKE '% " & searchInput & " %' OR PP LIKE '% " & searchInput & " %' OR Level LIKE '% " & searchInput & " %' OR Year LIKE '% " & searchInput & " %' OR Make LIKE '% " & searchInput & " %' OR Model LIKE '% " & searchInput & " %' OR Price LIKE '% " & searchInput & " %' OR Drivetrain LIKE '% " & searchInput & " %' OR Power LIKE '% " & searchInput & " %' OR Weight LIKE '% " & searchInput & " %' OR Torque LIKE ' %" & searchInput & " %' OR Country LIKE '% " & searchInput & " %' OR Condition LIKE '% " & searchInput & " %' OR Tradable LIKE '% " & searchInput & " %' ORDER BY Model ASC;")
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    That didn't seem to work at all and actually made none of my filtering work, bummer. Any other suggestions? Is there another term I can use besides 'LIKE'? I'm still kinda new to asp. Maybe I can use the term 'EXACT' or 'IS' instead of 'LIKE'? Thank you for your help so far GK!


    Jerome
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    your searchInput probably first word in side field...
    so, remove space before searchInput and leave after... like
    %Level1space%
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    That didn't seem to work either GK : / Any other suggestions buddy?


    Jerome
  14. #8
  15. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    What result did you get?
  16. #9
  17. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    It either searches for the whole string and returns anything with the term level and any of the numbers specified, rather than just the numbers specified. Or, if I make your changes, it won't give me a search result at all (blank). I can post my search.asp page if you think it may help.


    Jerome
  18. #10
  19. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    yes it will help. And you on SQL server database?
  20. #11
  21. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    Yea it's being hosted at www.somee.com as a SQL 2005 Express DB. Like I mentioned before, I'm still pretty new at this so I'm sure I haven't done things the most efficient way, but I'm always open to suggestions You can download the search page from my dropbox (and default.asp page too) here as the text is too big to post using code tags:

    Search: https://dl.dropbox.com/u/7037100/search.asp

    Default: https://dl.dropbox.com/u/7037100/default.asp


    Jerome
  22. #12
  23. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    Lets start from the beginning...
    default.asp you have only one sql statement and open 24 connections to server... That can kill your SQL server... you need just one connection.
    Open one recordset and pull all your data from that recordset. Only one do while loop to print your table.
    All your dropdowns like jumpmenuX hard coded and it is looks like should come from database...
    So your page could be at least 10 times smaller and faster.
    in first part duplicates default.asp all dropdowns. The question do you need them?, or why not to use only one page instead of 2?
    In search.asp you again have 24 recordsets which you do not need. You need just one for search results. All of them identical...
    on top of that you have form around almost each dropdown box, so your search criteria only one at the time... it mean you can simplify your query.
    Can you publish your sql table structure, so I can adjust your SQL
  24. #13
  25. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    I thought I only need one connection string as well, however, I couldn't seem to get multiple data pulls from the DB with just the one connection, I had to keep adding connections for each recordset

    Is this the info you need? If not, how would I properly publish it (please excuse my noobness):

    TABLE Data
    (Picture varchar(255),PP varchar(255),Level varchar(255),Year int,Make varchar(255),Model varchar(255),Price int, Drivetrain varchar(255),Power int,Weight int,Torque int, Country varchar(255),Condition varchar(255),Tradable varchar(255))
  26. #14
  27. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    do you have any unique field in your table?
  28. #15
  29. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2012
    Posts
    21
    Rep Power
    0
    Besides a '-' there won't be any special characters or unique fields. Here's a screenshot of my database:

    https://dl.dropbox.com/u/7037100/SQL.jpg


    Jerome
Page 1 of 3 123 Last
  • Jump to page:

Similar Threads

  1. Search/Filter table by specific characters
    By King John in forum Microsoft Access Help
    Replies: 4
    Last Post: March 31st, 2009, 09:29 AM
  2. Search specific word
    By Dilruba in forum SQL Development
    Replies: 2
    Last Post: December 1st, 2008, 03:48 AM
  3. Cannot search for specific date
    By craigchalmers in forum Microsoft Access Help
    Replies: 3
    Last Post: February 22nd, 2006, 08:22 AM
  4. Looking for a specific asp search engine..??
    By Cheops@Collecto in forum Microsoft Access Help
    Replies: 0
    Last Post: January 25th, 2004, 05:45 PM

IMN logo majestic logo threadwatch logo seochat tools logo