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

    Unanswered: SQL to search with multiple criteria

    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It appears to me that your problem is viewing the page as presenting six different searches, each one of which is based on a single column. What you seem to want is one search, with zero (or maybe one) to six columns.

    The way to get to that point is to have a single search button, that triggers code to look at the six different values that the user can specify, and generates a query based on those specifications. You can use the following snippet of code as a start, but you'll have to add some conditional processing to manage how you want the user's selections handled:
    Code:
    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 pointers" & _
    " JOIN products ON (products.id = pointers.Productid)" & _
    " JOIN tradename ON (tradename.id = pointers.tradenameid)" & _
    " JOIN suppliers ON (suppiers.id = pointers.supplierid)" & _
    " JOIN weight ON (weight.id = pointers.weightid)" & _
    " JOIN color ON (color.id = pointers.colorid)" & _
    " WHERE " & _
    " weight.name like '"& inparam &"' and " & _
    " order by weight.name"
    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    40

    RE to -PatP

    hi -PatP,
    Thanks you very much for your reply.
    What will be the code to create a big single search button by different criteria.

    What will be the code of "ASP" Program. So, the big single search button by different criteria can call up on the dreamweaver or html or asp code.

    Now the byweight.asp, byproduct.asp, by tradename.asp, by supplier.asp, by color.asp, bytwill.asp, bylocation.asp. All of this 7 sorting is individual sorting.


    Please help. Thank you very very much.
    thanks
    gar

Posting Permissions

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