Hooray! - Sorting and Searching Database Tables, Part 1

This article is the first part in a 4 part series. The series will be a complete tutorial of creating your own search engine and web portal called - Hooray! I will be discussing the following techniques:

1. Sorting and Searching Database Tables
2. Adding, Deleting, and Updating Records in a Database
3. Creating a Customized homepage (using a database to store preferences)
4. Putting It All Together (combines all previous articles as well as others to create Hooray!)

Lets get started with part 1. You can view a working copy of this article at: http://www.askasp.com/4guys/four_guys/hooray/sort_search.asp.

As a search engine, the main feature of Hooray! will be the ability to search through a database of web sites. This in an extremely easy task, and can be accomplished with very little code. While I am at it, we might as well learn how to sort the records as well. Lets start be creating a dummy Access database:

Create an Access database called "CompanyX" with a table called "tblEmployees" (this database is just for a very quick example and will not be used in the search engine we are creating).

Next we want to connect to our new database. (For more information on connecting to databases, be sure to read Connecting to a Database.)

'-- Declare your variables

Dim DataConnection, cmdDC, RecordSet

'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

'-- default SQL

SQL = "SELECT * FROM tblEmployees"

cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")

'-- Cursor Type, Lock Type

'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4

'-- For this example we will be using a Static cursor and a Read-Only lock type

RecordSet.Open cmdDC, , 0, 1

You will notice that I have used Session("DatabasePath") in the connection string. I do this to make the application a little more portable. I simply code the path in the global.asa file, so that if I want to move the site to a different server, or move the database to a remote directory to increase security, I can with ease.

The next step is to create the output table and our form. Lets start with the output table. First we will create our field headers.

<table>
<tr>


<td>ID</td>
<td>Name</td>
<td>Title</td>
<td>Department</td>


Then we want to grab the data from the database and place it in out table.

'-- Go to the first record

If Not RecordSet.BOF Then
RecordSet.MoveFirst

'-- Loop through records until we are at the end

Do Until RecordSet.EOF

'-- Display the fields

Response.Write("<tr>")
Response.Write(" <td>" & RecordSet.Fields("ID") & "</td>")
Response.Write(" <td>" & RecordSet.Fields("Name") & "</td>")
Response.Write(" <td>" & RecordSet.Fields("Title") & "</td>")
Response.Write(" <td>" & RecordSet.Fields("Department") & "</td>")
Response.Write("</tr>")

Do not forget to close out the table.

</table>

We are now going to create our sorting buttons. I like to use the A-Z & Z-A format.

<input type="submit" name="btnSortID" value="A-Z">
<input type="submit" name="btnSortID" value="Z-A">

Do this for each of the fields you would like to sort. We then want to use a different SQL string based on the button that is clicked. I do this using a If...Then statement placed AFTER the default SQL string we use to show all of the results. The easiest way to wite SQL is to have Access write it for you. Simply create a new querry and view the SQL. You can then cut-n-paste the sting into your ASP page.

If Request.Form("btnSortID") = "A-Z" Then


SQL = "SELECT tblEmployees.*, tblEmployees.ID FROM tblEmployees ORDER BY tblEmployees.ID ASC;"


That is all there is to it!

Lets move on to searching the database. First we will create the form:

<input type="text" name="txtSearchText" size="30"><input type="submit" name="btnSearch" value="Search">

Place the search criteria into a variable:

Dim SearchText
SearchText = Request.Form("txtSearchText")

Then we want to go into Access again to grab the SQL. To do this, we want to decide which fields are able to be searched, in this example we will be using all of them. We then want to use use the OR statement in the SQL and place in the SearchText variable (because of the length of the SQL string, I will be using several lines to keep it organized):

SQL = "SELECT tblEmployees.*, tblEmployees.Name, tblEmployees.Title, tblEmployees.Department "
SQL = SQL & "FROM tblEmployees "
SQL = SQL & "WHERE (((tblEmployees.Name) Like '%" & SearchText & "%')) OR (((tblEmployees.Title) Like '%" & SearchText & "%')) OR (((tblEmployees.Department) Like '%" & SearchText & "%'));"

In short, when the Search button is clicked, we sort through the database grabbing all of the records that match our criteria and display the results.

Note: in this example, if you click a sort button after you have performed a search, it will pull up all of the records rather than the searched records. To sort the searched records, you can use a flag to determine if the table has been searched through yet. If they have, simply place an ORDER BY statement on the end of your SQL.

Do not forget to close and release the database:

RecordSet.Close
Set RecordSet = Nothing

Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing

That is all there is to it!