two criteria asp

garyww · #1 (**permalink**) 09-07-04, 04:49

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

Seppuku · #2 (**permalink**) 09-07-04, 11:22

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech