How to search a database
Intro
Searching a database is actually a easy thing. It just involves a form, and a page that displays the results. Im going to show you how to make a simple form, and a page that displays the results.
First steps
So the first step is to create a form from where we search. This is just some simple html. Save this as page "search.asp".
<table>
<tr>
<form method="POST" action="search_results.asp">
<td>Search: <input type="text" name="txtSearch" size="30"></td>
</tr>
<tr>
<td><input type="submit" value="Search"></td>
</tr>
</table>
Next we are going to make the search page. This will be the page where the database is going to be searched. We need to open the database, create a SQL string to search the database, and display the results if there are any.
Define variables
First lets define all the variables we are going to use.
<%
Dim strInputSearch 'Variable for the search word
Dim strCon 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim rsSearch 'Holds the search recordset
Search field
Next lets get the search field and make it so no one can inject any bad SQL code.
'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")
'This makes it so people cant inject SQL code and/or cause some unwanted errors
strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)
Database connection
Next we are going to create a database connection before we can search the database. If your database is a different name, you need to change the name in the red.
'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")
'This is the connection string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("database.mdb")
'Open the connection
adoCon.Open strCon
Open and search
Now we are going to open the database and search it.
'Set the database connection
Set rsSearch = Server.CreateObject("ADODB.Recordset")
'This is the SQL statement for searching. You will need to change tblTable and Field to match your database (they are in red)
strSQL = "SELECT tblTable.* FROM tblTable WHERE tblTable.Field LIKE '%" & strInputSearch & "%';"
'Opens the database so we can get the results of the search
rsSearch.Open strSQL, adoCon
Display results
Now we are going to display if there are any results, or if there are none.
'If there are no matches then continue
If rsSearch.EOF Then
'Write out the message that there are no matches
Response.Write("There are no matches with your search")
'If there are matches, continue
Else
'Loop through the database to display all the matches
DO UNTIL rsSearch.EOF
'Write out the match found. You need to change Field to your database field you are search (its in red)
Response.Write(rsSearch("Field") & "<br>")
'Move to next line in database
rsSearch.MoveNext
'Continue looping through database to display all results found
Loop
End If
Now that you opened the database and returned the results, it is very important to close all the connections and we are all done!
'Reset server objects
Set rsSearch = Nothing
adoCon.Close
Set adoCon = Nothing
%>
Unsolved 
