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

    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:
    http://internal.quickentextiles.com.hk/sampleroom/byweight.asp?searchtype='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

    Application.LOCK
    'Create connection
    Set DbConn = Server.CreateObject ("ADODB.Connection")
    DbConn.Connectiontimeout=3
    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,"'","") & "%"
    ELSE
    inparam = "%" & replace(getparam,"'","") & "%"
    END IF

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

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


    thanks
    gar

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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
  •