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

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