I have solve the problem, its due to the SQL statement
To anyone that is interested, this is the code:
<%
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")
%>
<p>Search our sample db by first or last name. (% returns all)</p>
<form action="<%= strURL %>" method="get">
HI
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<p>[Try 'am' or 'er' for an example]</p>
<%
If strSearch <> "" Then
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))
strSQL = "SELECT ProjName, sales " _
& "FROM [Proj] " _
& "WHERE ProjName LIKE '%" & Replace(strSearch, "'", "''") & "%';"_
'& "OR first_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
'& "ORDER BY ProjName;"
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="1">
<tr>
<th>Name</th>
<th>Sales</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("ProjName").Value %> </td>
<td><%= rstSearch.Fields("sales").Value %></td>
</tr>
<%
rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
' That's all folks! See it's really not all that hard.
%>
