Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    43

    Unanswered: cannot search for multiple fields

    got the dropdown earch working with the help from rokslide but now I have a new problem. If I search the Searchdate and then also search for any of the other fields like firstname I get the following error:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'stipsdate BETWEEN #01/01/1970# AND #01/02/2004# WHERE Borrowers.FirstName LIKE 'pawloski''.
    /database/search_results.asp, line 65

    here is the code from search_results.asp:

    <%@ LANGUAGE=VBScript %>
    <%
    Response.Buffer = True
    Dim connect, recordset, whereclause, query, pageNumber, itemNumber, lineIndex
    lineIndex = 0
    Dim CustomerID
    Dim FirstName
    Dim MiddleInitial
    Dim LastName
    Dim HomePhone
    Dim WorkPhone
    Dim Email
    Dim NoResults
    Set connect = Server.CreateObject("ADODB.Connection") ' Prepare to connect to database
    connect.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("db1.mdb")
    pageNumber = CInt(Request("pageNumber"))
    if PageNumber = 0 then
    pageNumber = 1
    ' Get the form information in local variables

    FirstName = Trim(Request("FirstName"))
    MiddleInitial = Trim(Request("MiddleInitial"))
    LastName = Trim(Request("LastName"))
    PhoneNumber = Trim(Request("HomePhone"))
    WorkPhone = Trim(Request("WorkPhone"))
    Email = Trim(Request("Email"))
    StartDate = Trim(Request("startdate"))
    EndDate= Trim(Request("enddate"))

    whereclause = "WHERE "
    If CustomerID <> "" Then
    whereclause = whereclause & "Borrowers.CustomerID LIKE '" & CustomerID & "' AND "
    End If
    If FirstName <> "" Then
    whereclause = whereclause & "Borrowers.FirstName LIKE '" & FirstName & "' AND "
    End If
    If MiddleInitial <> "" Then
    whereclause = whereclause & "Borrowers.MiddleInitial LIKE '" & MiddleInitial & "' AND "
    End If
    If LastName <> "" Then
    whereclause = whereclause & "Borrowers.LastName LIKE '" & LastName & "' AND "
    End If
    If PhoneNumber <> "" Then
    whereclause = whereclause & "Borrowers.HomePhone = " & HomePhone & " AND "
    End If
    If WorkPhone <> "" Then
    whereclause = whereclause & "Borrowers.WorkPhone = " & WorkPhone & " AND "
    End If
    If Email <> "" Then
    whereclause = whereclause & "Borrowers.Email LIKE '" & Email & "' AND "
    End If
    If Right(whereclause, 4) = "AND " Then
    whereclause = Left(whereclause, Len(whereclause) - 4) ' strip off 'AND '
    Else
    whereclause = Left(whereclause, Len(whereclause) - 6) ' strip off 'WHERE '
    End If
    query = "SELECT * FROM Borrowers WHERE " & Request.Form("searchdate") & " BETWEEN #" & StartDate & "# AND #" & EndDate & "# " & whereclause
    Session("SSS_query") = query
    else
    query = Session("SSS_query")
    end if
    set recordset = Server.CreateObject("ADODB.Recordset")
    recordset.CursorType = 3 ' adOpenStatic
    recordset.PageSize = 20
    recordset.Open query, connect
    if not recordset.eof then
    recordset.AbsolutePage = CInt(pageNumber)
    end if
    %>
    <HTML>
    <title>Borrowers Search Results</title>
    <link rel="stylesheet" href="styles.css" type="text/css">
    <body topmargin=0 leftmargin=0 marginheight=0 marginwidth=0>
    <!--#include file="header.inc" -->
    <img src="logo.gif" width="144" height="75">
    <table width="100%" cellpadding="0" cellspacing="0" border="1" bordercolor="#000000">
    <tr>
    <td>
    <table width="100%" border="0" cellpadding="0" cellspacing="0">
    <tr>
    <td width="10" class="strip">&nbsp;</td>
    <td class="strip">CustomerID</td>
    <td class="strip">FirstName</td>
    <td class="strip">MiddleInitial</td>
    <td class="strip">LastName</td>
    <td class="strip">HomePhone</td>
    <td class="strip">WorkPhone</td>
    <td class="strip">Email</td>
    </tr>
    <%
    NoResults = True
    itemNumber = 0
    Do While Not recordset.EOF and itemNumber < recordset.PageSize
    NoResults = False
    %>
    <% If lineIndex MOD 2 = 0 Then %>
    <tr>
    <td width="10" class="data1">&nbsp;</td>
    <td class="data1"><a class="datalink" href="detailnew.asp?itemNumber=<%=(CInt(pageNumber ) - 1) * recordset.PageSize + itemNumber%>"><%=recordset("CustomerID")%></a></td>
    <td class="data1"><%=recordset("FirstName")%></td>
    <td class="data1"><%=recordset("MiddleInitial")%></td>
    <td class="data1"><%=recordset("LastName")%></td>
    <td class="data1"><%=recordset("HomePhone")%></td>
    <td class="data1"><%=recordset("WorkPhone")%></td>
    <td class="data1"><%=recordset("Email")%></td>
    </tr>
    <% Else %>
    <tr>
    <td width="10" class="data2">&nbsp;</td>
    <td class="data2"><a class="datalink" href="detailnew.asp?itemNumber=<%=(CInt(pageNumber ) - 1) * recordset.PageSize + itemNumber%>"><%=recordset("CustomerID")%></a></td>
    <td class="data2"><%=recordset("FirstName")%></td>
    <td class="data2"><%=recordset("MiddleInitial")%></td>
    <td class="data2"><%=recordset("LastName")%></td>
    <td class="data2"><%=recordset("HomePhone")%></td>
    <td class="data2"><%=recordset("WorkPhone")%></td>
    <td class="data2"><%=recordset("Email")%></td>
    </tr>
    <% End If %>
    <%
    itemNumber = itemNumber + 1
    lineIndex = lineIndex + 1
    recordset.MoveNext
    Loop
    %>
    </table>
    </td>
    </tr>
    <tr>
    <td class="strip">&nbsp;</td>
    </tr>
    </table>
    <br><br>
    <div align="center">
    <table border="0" cellspacing="0" cellpadding="0" class="detail">
    <tr>
    <td>
    <div align="center">
    <%
    If pageNumber > 1 Then
    %>
    <table width="150" border="1" bordercolor="#000000" bgcolor="#cccccc" cellpadding="0" cellspacing="0">
    <tr>
    <td class="button">
    <div align="center">
    <p><a class="button" href="search_results.asp?pageNumber=<%=pageNumber - 1%>">PREVIOUS</a></p>
    </div>
    </td>
    </tr>
    </table>
    </div>
    </td>
    <td>
    <div align="center"> </div>
    </td>
    <td>
    <div align="center">
    <%
    End If
    if not recordset.EOF then
    %>
    <table width="150" border="1" bordercolor="#000000" bgcolor="#cccccc" cellpadding="0" cellspacing="0">
    <tr>
    <td class="button">
    <div align="center">
    <p><a class="button" href="search_results.asp?pageNumber=<%=pageNumber + 1%>">
    NEXT</a></p>
    </div>
    </td>
    </tr>
    </table>
    <%
    end if
    %>
    </div>
    </td>
    </tr>
    </table>
    <br>
    <%
    if NoResults = True Then
    %>
    Sorry, no records in the database matched your search parameters. Click Back
    and try again.
    <%
    End If ' No match
    %>
    </div>
    <p align="center"><br>
    <div align="center">
    <table width="540" border="0" cellspacing="0" cellpadding="0">
    <tr>
    <td>
    <div align="center">Click on the hyperlinks in the leftmost column for
    more information. </div>
    </td>
    </tr>
    </table>
    </div>
    </BODY>
    </HTML>

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    see the answer in the previous post. try not to make multiple posts regarding the same set of code...

Posting Permissions

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