Database - Recordset - Search Queries

<div id="search">
    <form method="post" action="serch.asp"> Enter Location: <input type="text" size="10" name="search" /><input type="submit" name="submit" value="Go" /></form>
  </div>


 <%
    ' see if the form SUBMIT button was pressed
    if request.form("submit") = "Go" then

     Search = request.form("search")
     If (search = "") Then
      SearchVal = true
      Error = true
     End if  %>


      <table>
        <tr>
          <th>Location Name</th>
          <th> Location Address</th>
          <th>Location Rating</th>
          <th>Location Catogory</th>
        </tr>

      <%

        set conn=server.createobject("ADODB.Connection")
        strMDBpath = Server.MapPath("DisabilityNet.mdb")
        conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath
        set objRS=server.createobject("ADODB.Recordset")

          ' build the SQL statement
           strSQL = "SELECT * FROM  Locations WHERE LocationName LIKE '%" & Search & "%';"



          'pass the SQL command to the db connection
          objRS.open strSQL,conn,1,2

          ' loop through all the db results
          Do while not objRS.EOF
            response.write "<p>"
            ' output the id field to the screen
          response.write "<tr>"
          response.write "<td>"
          response.write objRS("LocationName")
          response.write "</td>"
          response.write "<td>"
          response.write objRS("LocationAddress")
          response.write "</td>"
          response.write "<td>"
          response.write objRS("LocationRating")
          response.write "</td>"
          response.write "<td>"
          response.write objRS("LocationCategory")
          response.write "</td>"
          response.write "</tr>"

      ' move to the next object in the record set
      objRS.movenext
    ' loop again
    Loop
' close the db connection (important)
%></table>


<%


end if

%>

Search = Replace(request.form("search"), "'", "")

strSQL = "SELECT * FROM  Locations WHERE UCase(LocationName) LIKE '%" & UCase(Search) & "%';"