new sql searching

garyww · #1 (**permalink**) 10-20-04, 00:26

Anybody know how to resolve this sophiscated program.

Now the URL is : http://internal.quickentextiles.com.hk/sampleroom/byweight.asp?searchtype='Alph'

If I input "10" at the by weight and "white" at the by color. After I input
this two criteria on the textbox. I suppose to have a button "criteria search".
I press this criteria search button. It should query the result with 10 weight and with the white color criteria searching result.
However, I wrote the program below but unable to implement the above
process. Anyone know how it work out and the problem of the syntax.
The criteria search program is as follows:

<%@ Language="VBSCRIPT" %>
<% Option Explicit %>
<% Server.ScriptTimeout = 300 %>
<% response.buffer = true %>


<%
Const Field1 = "productname"
Const Field2 = "tradename"
Const Field3 = "weightname"
Const Field4 = "colorname"
Const Field5 = "twillname"
Const Field6 = "supplierID"
Const Field7 = "suppliername"

Const thisURL= "byall.asp"
Const PageSize = 25
%>

<HTML><HEAD><TITLE>Sample Room Display</TITLE>
<META content="text/html; charset=big5" http-equiv=Content-Type>
<STYLE type=text/css>A:hover {
COLOR: #ff3300
}
</STYLE>

<META content="Microsoft FrontPage 5.0" name=GENERATOR></HEAD>
<BODY aLink=#000099 bgColor=#ffffff leftMargin=0 link=#000099 text=black
topMargin=0 vLink=#000099 marginheight="0" marginwidth="0">



<%
'----------------------------------------------------------
'Sub Funcion : PageView
'Desc : Display SQL result as pages
'Param
' oRecordSet : object for output recordset object
' nPageNum : current pagenum
' nPageSize : item numbers in one page
' sQueryURL : query process url
'----------------------------------------------------------
Sub PageView(oRecordSet , nPageNum,nPageSize, sQueryURL)
Dim nPageCount,i,j

oRecordSet.PageSize = nPageSize
nPageCount = oRecordSet.PageCount
IF (nPageCount < 1) THEN
Exit Sub 'The query result is empty
End IF

oRecordSet.AbsolutePage = nPageNum

For i=1 to oRecordSet.PageSize
Response.Write "<TR align=left vAlign=top>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field1) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field2) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field3) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field4) & "</TD>"
Response.Write "<TD width=200>" & oRecordSet.fields(Field5) & "</TD>"
Response.Write "<TD width=300>"
Response.Write "<a href=""displaydetail.asp?Searchby=Products&SID=" & oRecordSet.fields(Field6) & """>"& oRecordSet.fields(Field7) & "</a>"

Response.Write "</TD>"
Response.Write "</TR>"
oRecordSet.MoveNext
IF oRecordSet.EOF THEN Exit For
Next

Response.Write ("<TR><td colspan=3> ")
IF (nPageCount > 1) THEN
IF (nPageNum = 1) THEN
Response.Write "<A HREF=" & sQueryURL & "&PageNum=2>Next Page</A>"
ELSEIF (nPageCount = nPageNum) THEN
Response.Write "<A HREF=" & sQueryURL & "&PageNum=" &(nPageNum-1) & ">Prev Page</A>"
ELSE
Response.Write "<A HREF=" & sQueryURL & "&PageNum=" &(nPageNum-1) & ">Prev Page</A>"
Response.Write "    "
Response.Write "<A HREF=" & sQueryURL & "&PageNum=" &(nPageNum+1) & ">Next Page</A>"
END IF
END IF
Response.Write ("</td></tr>")

End Sub

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, twill.name as twillName, " & _
" tradename.name as TradeName, suppliers.ID as SupplierID " & _
" from products, tradename, suppliers, weight, color, twill, pointers where " & _
" products.name = suppliers.name and color.name = weight.name " & _
" and (color.name IN ('indigo','Dark Coffee','Blue-Green','Blue-Black','Blue-Yellow','white')) "
& _
" and (weight.name IN (5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12, 12.5,13,13.5,14,14.5,15)
"

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

searchtradenameAZSQL = searchtradenameAZSQL & " order by weight.name, tradename.name, color.name,
twill.name, products.name;"

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

thanks!
mania

urquel · #2 (**permalink**) 10-20-04, 11:54

You are joining 7 tables and you only have 2 predicate clauses that match rows on any of these tables. You are likely to recieve a lot more rows than you need.