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

    Unanswered: two criteria asp

    hi, Anyone know if I want two criteria including tradename and weight these two criteria to search.
    Then you can see the line syntax of
    " tradename.name like '"& inparam &"' and " & _
    Now, I want to add one more let say
    " tradename.name, weight.name like '"& inparam &"' and " & _
    for the sql syntax. Then I created a single search button called by
    criteria search. Once I input tradename on the by item category and
    I also input the weight on the by weight textbox and press the single
    search button on the bottomneath. What will be the correct snipple code
    The url is : http://internal.quickentextiles.com.hk/sampleroom/byall.asp?searchtype='Alph'



    searchtradenameAZSQL = "select products.name as ProductName, suppliers.name as SupplierName, weight.name as WeightName, color.name as ColorName, twill.name as twillName, " & _
    " tradename.name as TradeName, suppliers.ID as SupplierID " & _
    " from products, tradename, suppliers, weight, color, twill, pointers where " & _
    " tradename.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.twillid = twill.id and " & _
    " pointers.tradenameid = tradename.id order by weight.name, tradename.name, color.name, twill.name, products.name"


    thanks

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Gary,

    You've asked this question about half a dozen times, and I keep trying to work with you but you insist on sending a personal email with your response. People can't help you, nor can anyone learn from your example, if I work privately with you on this.

    As I said before, you need a SINGLE search page which has a SINGLE form tag that points to a SINGLE results page. That results page needs some code in it to check every input from the form tag and dynamically builds the SQL string. Do this with a series of "If...Then" statements:

    Code:
    searchtradenameAZSQL = "select products.name as ProductName, suppliers.name as SupplierName, weight.name as WeightName, color.name as ColorName, twill.name as twillName, " & _
    " tradename.name as TradeName, suppliers.ID as SupplierID " & _
    " from products, tradename, suppliers, weight, color, twill, pointers where " & _
    " pointers.Productid = products.id and " & _
    " pointers.supplierid = suppliers.id and " & _
    " pointers.weightid = weight.id and " & _
    " pointers.colorid = color.id and " & _
    " pointers.twillid = twill.id and " & _
    " pointers.tradenameid = tradename.id order by weight.name, tradename.name, color.name, twill.name, products.name and " 
    
    Dim bCriteriaMatched
    bCriteriaMatched = False
    
    If Request.Form("tradename") <> "" Then
      bCriteriaMatched = True
    
      searchtradenameAZSQL = searchtradenameAZSQL & " tradename.name like '"& Request.Form("tradename") &"' "
    End If
    
    If Request.Form("color") <> "" Then
      If bCriteriaMatched Then
        searchtradenameAZSQL = searchtradenameAZSQL & " AND "
      End If
    
      bCriteriaMatched = True
    
      searchtradenameAZSQL = searchtradenameAZSQL & " color.name like '"& Request.Form("color") &"' "
    End If
    
    If Request.Form("twill") <> "" Then
      If bCriteriaMatched Then
        searchtradenameAZSQL = searchtradenameAZSQL & " AND "
      End If
    
      bCriteriaMatched = True
    
      searchtradenameAZSQL = searchtradenameAZSQL & " twill.name like '"& Request.Form("twill") &"' "
    End If
    
    '''Wash, Rinse, Repeat...
    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
  •