Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: Multiple criteria search in asp script

    hi, Anyone know how to solve, I have a program as follows: However, it only can search one by one. However, if I want to do multiple criteria search as according program.

    YOu can see the result page on the url:'Alph'
    Once I input "15" on the textbox under by weight and two more criteria such as I input "streaky" under item category and "blue-yellow" under color column. Now all together 3 criteria. The result will only display
    as follows with very precise search in 3 inputted criteria, it can be type in.

    weight product code item category color twill
    15 S957 Streaky Blue-Yellow 4L

    However, the following code only can do one by one search. For instance,
    I input "15" on the by weight, it sort all of the "15" of the weight of the entire database. It can't be multiple criteria to search. How can I work it out with following source code of SQL.

    Dim DbConn
    Dim searchtradenameAZ
    Dim gettype
    Dim getparam
    Dim inparam
    Dim searchtradenameAZSQL
    Dim sURL

    'Create connection
    Set DbConn = Server.CreateObject ("ADODB.Connection")
    DbConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("sroomsearch.mdb") & ";user=nil;password=nil"
    Set searchtradenameAZ = Server.CreateObject ("ADODB.Recordset")

    gettype = trim(request("searchtype"))
    getparam = trim(request("searchAlpha"))

    ' replace quotes
    IF ( gettype = "'Alph'") THEN
    inparam = replace(getparam,"'","") & "%"
    inparam = "%" & replace(getparam,"'","") & "%"
    END IF

    searchtradenameAZSQL = "select as ProductName, as SupplierName, as WeightName, as ColorName, " & _
    " as TradeName, suppliers.ID as SupplierID " & _
    " from products, tradename, suppliers, weight, color, pointers where " & _
    " like '"& inparam &"' and " & _
    " pointers.Productid = and " & _
    " pointers.supplierid = and " & _
    " pointers.weightid = and " & _
    " pointers.colorid = and " & _
    " pointers.tradenameid = order by"

    Set searchtradenameAZ = Server.CreateObject("ADODB.Recordset")
    searchtradenameAZ.Open searchtradenameAZSQL, DbConn, adOpenStatic


  2. #2
    Join Date
    Jul 2003
    The main problem you have is that you have separate FORM tags for each search type. What you need to do is use a single FORM tag pointing to a single result page. Then you can test each field and determine if it should be included in the SQL statement.
    That which does not kill me postpones the inevitable.

Posting Permissions

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