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 %>
<!--#include File="adovbs.inc"-->
<%
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">
<!-- Insert HTML here -->
<%
'------------------
'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><font face='Arial, Helvetica, sans-serif' size=-1>" & oRecordSet.fields(Field1) & "</font></TD>"
Response.Write "<TD width=200><font face='Arial, Helvetica, sans-serif' size=-1>" & oRecordSet.fields(Field2) & "</font></TD>"
Response.Write "<TD width=200><font face='Arial, Helvetica, sans-serif' size=-1>" & oRecordSet.fields(Field3) & "</font></TD>"
Response.Write "<TD width=200><font face='Arial, Helvetica, sans-serif' size=-1>" & oRecordSet.fields(Field4) & "</font></TD>"
Response.Write "<TD width=200><font face='Arial, Helvetica, sans-serif' size=-1>" & oRecordSet.fields(Field5) & "</font></TD>"
Response.Write "<TD width=300>"
Response.Write "<a href="http://links.10026.com/?link="displaydetail.asp?Searchby=Products&SID=" & oRecordSet.fields(Field6) & """><font face='Arial, Helvetica, sans-serif' size=-1>"& oRecordSet.fields(Field7) & "</font></a>"
Response.Write "</TD>"
Response.Write "</TR>"
oRecordSet.MoveNext
IF oRecordSet.EOF THEN Exit For
Next
Response.Write ("<TR><td colspan=3> <P>")
IF (nPageCount > 1) THEN
IF (nPageNum = 1) THEN
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=2><font face='Arial, Helvetica, sans-serif' size=-1>Next Page</font></A>"
ELSEIF (nPageCount = nPageNum) THEN
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=" &(nPageNum-1) & "><font face='Arial, Helvetica, sans-serif' size=-1>Prev Page</font></A>"
ELSE
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=" &(nPageNum-1) & "><font face='Arial, Helvetica, sans-serif' size=-1>Prev Page</font></A>"
Response.Write " "
Response.Write "<A href="http://links.10026.com/?link= & sQueryURL & "&PageNum=" &(nPageNum+1) & "><font face='Arial, Helvetica, sans-serif' size=-1>Next Page</font></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!
maniaYou 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.
