Error When Trying to Search in Access DB Using ASP

mvollmer · October 10th, 2003, 09:12 AM

Hey all,

I am getting the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Site_Code LIKE '%2%' AND WHERE Access_by_PubTrans LIKE '%y%' AND Code <> '1''.

/Portal/Portal/Misc/practicum/results.asp, line 37

When I use the following code:

<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Search Results</title>
</head>

<body>
<center>
<%

Dim query

Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.ConnectionTimeout = 15
dbGlobalWeb.CommandTimeout = 30
dbGlobalWeb.Open "DBQ=" & Server.MapPath("sites.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20 ;", "username", "password"

query = "SELECT * FROM sites"

If Request.Form("DaInBox1") <> "" Then
    query = query & " WHERE " & Request.Form("TypeSearch1") & " LIKE '%" & Request.Form("DaInBox1") & "%' AND "
End If

If Request.Form("DaInBox2") <> "" Then
    query = query & " WHERE " & Request.Form("TypeSearch2") & " LIKE '%" & Request.Form("DaInBox2") & "%' AND "
End If

If Request.Form("DaInBox3") <> "" Then
    query = query & " WHERE " & Request.Form("TypeSearch3") & " LIKE '%" & Request.Form("DaInBox3") & "%' AND "
End If

query = query & "Code <> '1' ORDER BY Site_Name ASC"

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
rsGlobalWeb.Open query, dbGlobalWeb, 3
%>
<%
If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>

<h2 align="center">No match found in current practicum sites.</h2>
<a href="search.asp">Back to Search Page</a>
<%Else%>

<%If Not rsGlobalWeb.BOF Then%>

<h2>Sites Matching Search:</h2>

<table BORDER="1">
  <tr>
      <td><b>Site Number</b></td>
    <td><b>Site Name</b></td>
    <td><b>Access by Pub. Trans</b></td>
    <td><b>Options</b></td>
  </tr>
<%
    Do While Not rsGlobalWeb.EOF
    %>
  <tr>
      <td><%= rsGlobalWeb.Fields("Site_Code").Value %></td>
    <td><%= rsGlobalWeb.Fields("Site_Name").Value %></td>
    <td><%= rsGlobalWeb.Fields("Access_by_PubTrans").Value %></td>
    <td><a href="<% If not isnull(rsGlobalWeb("Site_Code")) Then response.write "view.asp?key=" & Server.URLEncode(rsGlobalWeb("Site_Code")) Else response.write "javascript:alert('Invalid Record! Key is null.');" %>">View Site Information</a></td>
  </tr>
<% rsGlobalWeb.MoveNext
    Loop
    %>
</table>
<%End If%>
<%End If%>
<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>
</center>
</body>
</html>

From what I can tell it seems like it is not picking up the beginning of the query value "SELECT * FROM sites", or it is dropping it for some reason. This is being used for a search page. Below is the form for the user to search with and it posts to the page above:

<div align="center"><h2>Search All Practicum Sites</h2>
<table>
<tr>
<td ><form action="results.asp" method="post" name="DaForm" align="center">

      <div align="center"><center><table border="1">
        <tr>
          <td align="left"><select name="TypeSearch1" size="1">
            <option selected value="Site_Code">Site Number</option>
            <option value="Site_Name">Site Name </option>
            <option value="Access_by_PubTrans">Access by Pub. Trans(Y/N) </option>
           </select></td>
          <td align="right"><input type="text" size="30" name="DaInBox1"></td>
        </tr>
        <tr>
          <td align="left"><select name="TypeSearch2" size="1">
            <option selected value="Site_Code">Site Number</option>
            <option value="Site_Name">Site Name </option>
            <option value="Access_by_PubTrans">Access by Pub. Trans(Y/N) </option>
           </select></td>
          <td align="right"><input type="text" size="30" name="DaInBox2"></td>
        </tr>
        <tr>
          <td align="left"><select name="TypeSearch3" size="1">
            <option selected value="Site_Code">Site Number</option>
            <option value="Site_Name">Site Name </option>
            <option value="Access_by_PubTrans">Access by Pub. Trans(Y/N) </option>
           </select></td>
          <td align="right"><input type="text" size="30" name="DaInBox3"></td>
        </tr>
        <tr>
          <td colspan="2" align="center"><div align="left"> <div align="right">
                <input type="submit"
          name="B1" value="Search">
                <input type="reset" name="B2" value="Clear">
          </div></td>
        </tr>
      </table>
      </center></div>
    </form>
    </td>
</tr>
</table>
</div>

I appreciate any help. I want to let my users search with multiple criteria with several options. What I am doing in theory should work, but for some reason it is dropping the beginning value of query. Thanks again.

Michael W. Vollmer

Imar · October 10th, 2003, 10:26 AM

Hi there,

You can use the keyword WHERE only once in a query:

SELECT Field1, Field2 FROM Table WHERE Field1 = 'Something' AND Field2 = 'SomethingElse'

If that doesn't help, make the following change in your code:

Code:

  Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
  Response.Write(query)
  Response.End
  rsGlobalWeb.Open query, dbGlobalWeb, 3
%>

and post the outcome of the Response.Write statement to this list.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

mvollmer · October 10th, 2003, 10:45 AM

THANK YOU SOOOOOOOOOOOOOOOOOOOOOOOOOOOO MUCH!!!!!!!!!!

I feel like a total moron now that I look at it and it was something so simple. Thank you again, you just saved me from so many problems.

This forum is awesome. You are the best! Thanks again!

Michael W. Vollmer

joefawcett · October 10th, 2003, 10:52 AM

Two other things:
1) Try to change to oledb provider, not odbc one you are using. Plenty of example connection strings on the web.
2) To build up your query start with:

Code:

sWhere = "SELECT * FROM sites WHERE 1 = 1 "

and then add on stuff as needed.
BE VERY CAREFUL about feeding in user input directly to sql statements. They can easily, on purpose or by design, crash or steal information they shouldn't have access to. E.g. by entering single quotes. See "SQL injection" on the web, especially at msdn.microsoft.com.

--

Joe