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

    Unanswered: Criteria search ASP button program

    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=""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>&nbsp;<P>")
    IF (nPageCount > 1) THEN
    IF (nPageNum = 1) THEN
    Response.Write "<A HREF=" & sQueryURL & "&PageNum=2><font face='Arial, Helvetica, sans-serif' size=-1>Next Page</font></A>"
    ELSEIF (nPageCount = nPageNum) THEN
    Response.Write "<A HREF=" & sQueryURL & "&PageNum=" &(nPageNum-1) & "><font face='Arial, Helvetica, sans-serif' size=-1>Prev Page</font></A>"
    ELSE
    Response.Write "<A HREF=" & sQueryURL & "&PageNum=" &(nPageNum-1) & "><font face='Arial, Helvetica, sans-serif' size=-1>Prev Page</font></A>"
    Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"
    Response.Write "<A HREF=" & 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!
    mania

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie, you have this section of code here
    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 " & _
    " 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)
    "
    which from the looks of it will select everything....

    you then try to add things to it using this....
    Code:
    If Request.Form("tradename") <> "" Then
      bCriteriaMatched = True
      searchtradenameAZSQL = searchtradenameAZSQL & " tradename.name like '"& Request.Form("tradename") &"' "
    End If
    that's not really going to work.... (as you may have noticed).

    what you need to do is have this bit of code...
    [code]
    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 " & _
    " products.name = suppliers.name and color.name = weight.name and "
    instead of your first bit and then add either...
    Code:
    & _
    " 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)
    "
    or
    Code:
    " tradename.name like '"& Request.Form("tradename") &"' "
    depending on what has been selected/submitted. does that make sense??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •