Tutorial: Search a Database

[Drew]

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
%>

[millers_35]

Is this using an SQL database? And I didnt see the files you said were listed to see the flow.
Thanks! Thats good stuff there.

Miller

[Drew]

This example was for an access database. For MSSQL you just have to change the connection string and it should work ;)

[william_stam]

how do you get a multiword search going. like google.

the above example if you search for "and the" it would only return the records where those two words are together. how do you get it so that it returns records that contain both words but not necessarly together?

thanks, i like the strip sql part, i have been using client side till now.