Searching and sorting records in a recordset from the database

Introduction
In this article we will see on how to search the database for a group of records. Searching a database is very easy and fun provided you know how to do it. Many of you who know SQL will already be doing it. But I will not use any lengthy SQL statements, instead will use some very useful Recordset object properties; Filter and Sort.

Filter allows us to filter ( search ) specific records from a group of records and Sort allows easy sorting of the records based on the criteria that we give.

We will begin by creating a test database for our simple project. And then show it's records. After that we will learn how to use Filter and Sort properties of Recordset object to search records.

The example database and ASP page that we will create can be downloaded at the end of the article. Just bear me for a while and I hope after reading this tutorial you won't need to go any where else, at least for searching and sorting of database records.

Basic Steps
Create a new Microsoft Access database and name it 'filter.mdb'. Then create a new table in design view. Add 3 fields in it. Name and types of those fields are given below :

id : AutoNumber
name : Text
country : Text

Make 'id' field as the Primary Key. Then save this table as 'names'. Just make sure that the design view of your table matches the following table given in the figure :

names - Table

Populating the Database
After saving the table, double click it's name to open the 'Names' table. Now add names and coutries as given in the following figure in their respective fields :

names - Table

Basics
We will now follow some simple steps to show the records of out database. We will not yet Filter or Sort them, that we will do in subsequent pages.

I have explained the steps of connecting to a database by first creating a DSN ( Data Source Name ) in my tutorial Accessing Database from ASP. In this article we will not create DSN and instead use a more faster and efficient way; DSN less connections. These connections instead of taking a DSN take a connection string as their argument. This connection string is nothing more than a one line string consisting of 'Provider' and 'Data Source' fields and their values. After establishing connection to the database, rest of the steps of accessing the data from it are the same as discussed in Accessing Database from ASP article.

Creating filter.asp page
Now open your text pad and create a new empty file. Now copy and paste the following code into it and then save it as 'filter.asp' :

<% Option Explicit %>
<%
Sub ShowRec(qc1, qc2, qc3)
	Response.Write "<table width=500 border=0 " & _
"cellpadding=0 cellspacing=0" & vbcrlf
	Response.Write "<tr>"
	Response.Write "<td width=100>"
	
	Response.Write qc1
	Response.Write "</td><td width=200>"
	Response.Write qc2
	Response.Write "</td><td width=200>"
	Response.Write qc3
	Response.Write "</td></tr></table>"
End Sub

' Putting the values of ADO constants
	Const adCmdTableDirect = &H0200
	Const adLockReadOnly = 1
	Const adOpenStatic = 1
	Const adUseClient = 3
%>

<html>
<head>
	<style>
		body { font-family : Verdana; font-size : 8pt; }
	</style>
</head>
<body>
<%
	Dim connStr
		connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
			Server.MapPath("/database/filter.mdb")

	Dim rs
		Set rs = Server.CreateObject("ADODB.Recordset")

	rs.CursorLocation = adUseClient
	rs.Open "Names", connStr, adOpenStatic, adLockReadOnly, adCmdTableDirect

	If Not rs.EOF Then
		Response.Write "Records Found!" & "<br><br>" & vbcrlf

		Response.Write "Showing all records : " & _
rs.RecordCount & "<br><br>" & vbcrlf
		While Not rs.EOF
			ShowRec rs("id"), rs("name"), rs("country")
				rs.MoveNext
		Wend

	Else
		Response.Write "No records Found!"
	End If

	rs.Close
	Set rs = Nothing
%>

Explanation
If you can understand the code then go ahead move to the next page. I will very briefly explain the steps involved in above code.

		<% Option Explicit %>

This line tells the ASP interpreter the we won't be using any variables without first defining them. For example following definition of a variable named 'name' will fire a runtime error :

		name = "Faisal Khan"

But the following declaration and definition of the code is perfectly legal :

	Dim name
		name = "Faisal Khan"

The point is that you have to Dim every variable before you use it. Why we do it ? because it increases performance of our ASP page.

Sub ShowRec(qc1, qc2, qc3)
	Response.Write "<table width=500 border=0 " & _
"cellpadding=0 cellspacing=0" & vbcrlf
	Response.Write "<tr>"
	Response.Write "<td width=100>"
	Response.Write qc1
	Response.Write "</td><td width=200>"
	Response.Write qc2
	Response.Write "</td><td width=200>"
	Response.Write qc3
	Response.Write "</td></tr></table>"
End Sub

We then create a Sub routine to display the records. Since we will be showing records again and again with different filters and sorts later, it will be wise enough for us to create a Sub and stuff code in it to show the records by creating simple HTML tables thus making it more readable to the user.

	connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
		Server.MapPath("/database/filter.mdb";

This line is the connection string which I talked earlier. It is a single line containing the Provider name ( data driver ) and the complete physical path to the database. Most often then not Provider name will remain the same but the physical path of the database will change. So instead of writing the complete physical path as the argument to the Data Source field, we use Server.MapPath method to map the relative path of our database into complete physical path. Note this way of connecting to the database is more easier and efficient than the DSN way.

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
	rs.CursorLocation = adUseClient
rs.Open "Names", connStr, adOpenDynamic, adLockReadOnly, adCmdTableDirect