Searching and sorting records in a recordset from the database

Next we create the Recordset object. Notice that we have set the CursorLocation of our Recordset to 'adUseClient'. By default it is set to 'adUseServer'. If we are to use Sort property of Recordset object we have to set it to 'adUseClient'. This is very important so don't forget it. Then we open the 'Names' table and use our connection string which we created earlier as the argument to ActiveConnection field. Next we set the CursorType to 'adOpenStatic'. By default it is set to 'adOpenForwardOnly' which is more faster but it only allows us to move forward in the records and not backwards. Since we will be moving the cursor back and forth and that we also need to use the RecordCount property to count records, we set it to 'adOpenStatic' which allows us to move both ways in the recordset and also allowing us to use the RecordCount property. Nothing much to say about the 'adLockReadOnly' lock type. In the options field we use 'adCmdTableDirect' to tell the ASP interpreter that we are not using SQL statements and will connect with the table directly.

We first check to see if the recordset created is not empty ( i.e. database contains no records ). If it is empty then we tell the user that no records were found. If recordset is not empty then we iterate through the records and using our Sub ShowRec() we show the records in a table. Notice that we have used RecordCount property to display the number of records found in the database.

Once we are done, we close the connection and set our Recordset object to Nothing to free memory.

What is Sorting ?
When records are shown they are most often then not shown according to the AutoNumber field. Sorting allows us to arrange the records and make them appear the way we want them to. We can sort the records according to one or more column names. This makes the records more readable.

Practical Application of Sorting
For example you have a database which contains title, description, keywords, date submitted and URL fields of your whole site in a table. Your site is divided into several sections. Now what you want to do is to show the relevant articles of your site according to the section of your site. You can show the records haphazardly there. But it won't make any sense. Wouldn't it be better if you could allow the user to see the records by title i.e. pages with title starting with A coming first and that with B coming second and so on. You could also show the records according to the date submitted with new articles coming first and older ones down at the bottom. You can achieve these tasks by simply sorting them out according to one of the fields and you are done. Below I will show you how to do it.

Updating filter.asp page
Now add the following code in the filter.asp page before the line 'rs.Close' :

Explanation
Note that you can always download the complete 'filter.asp' page at the end of this article so if you find it difficult to copy paste the code then only look and understand the code here and then download 'filter.asp' later.

After showing the records in our filter.asp page, the record cursor will be present at the end of the records. We move it back to the first record so that we can again iterate through the records and show them.

Next we sort the records using Recordset object Sort property. The way we show the records is the same as explained in the last page by using our ShowRec Sub. Important thing to note is how we use Sort property and then how records are displayed.

It is the only line which is different from the code which we used in the last page to show records in the database. Sort property takes the name of column/s and then sorts the records according to it. In this case we give it the name of 'name' column from our 'Names' table. So the records which will now be shown will be sorted out according to that field.

Next we remove the sorting by setting Sort property to "". Now the records are not sorted. Then we again sort the records according to the column 'country' from our 'Names' table. This time when the records are shown, they are sorted according to the country name alphabetically.

Then we remove the sorting. This time we sort according to the column name 'country' and arranging the records upside down by writing 'Desc' in front of the 'country' column field. Note that Sort property takes one or more column name as it's argument as well two more keywords; Asc and Desc. Desc arranges the records upside down to those of Asc. So this time the records are sorted according to the 'country' column but are arranged upside down as we have appended 'Desc' in front of it.

What is Filtering ?
Filtering means to select the records which we want from a bigger massive group of records according to the criteria which we set. In other words you can say that filtering is searching through all the records of the database and selecting only those which satisfy our criteria whatever it may be.

Practical Application of Filtering
You can use filtering in your search engine where you would want to select and display only the records which contain specific keyword/s entered by the user.

Or for example you have a big banner info database containg records about the banners shown and click thrus recorded. Now you want to show the advertiser the number of impressions shown and click thrus recorded of their banners. You can achieve this by filtering the records which contain the advertiser specific info and then showing it to the advertiser. Simple!

	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

Once we are done, we close the connection and set our Recordset object to Nothing to free memory.

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

These are ADO constants used in the code.

Updating filter.asp page
Now add the following code in the filter.asp page before the line 'rs.Close' :

' Moving the record pointer to the beginning
rs.MoveFirst

Now sorting according to the field "name"
rs.Sort = "name"
Response.Write "<br><br>Sorting by field ""name"" : <br><br>"
Response.Write vbcrlf
While Not rs.EOF
		ShowRec rs("id"), rs("name"), rs("country")
		rs.MoveNext
Wend
' Moving the record pointer to the beginning
rs.MoveFirst

'Now sorting according to the field "country"
rs.Sort = "country"
Response.Write "<br><br>Sorting by field ""country""" & _
	": <br><br>" & vbcrlf
While Not rs.EOF
		ShowRec rs("id"), rs("name"), rs("country")
		rs.MoveNext
Wend
' Moving the record pointer to the beginning
rs.MoveFirst

'Now sorting according to the field "country" and arranging in Desc order
rs.Sort = "country Desc"
Response.Write "<br><br>Sorting by field ""country"" and"
Response.Write " arranging in descending order : <br><br>" & vbcrlf
While Not rs.EOF
		ShowRec rs("id"), rs("name"), rs("country")
		rs.MoveNext
Wend
' Moving the record pointer to the beginning
rs.MoveFirst
' Removing the sort
rs.Sort = ""

' Moving the record pointer to the beginning
	rs.MoveFirst

	rs.Sort = "name"

	rs.Sort = ""
	rs.Sort = "country"

	rs.Sort = ""
	rs.Sort = "country Desc"

Practical Application of Filtering
You can use filtering in your search engine where you would want to select and display only the records which contain specific keyword/s entered by the user.