Searching and sorting records in a recordset from the databaseby Faisal Khan.
 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, adCmdTableDirectIntroduction
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 | 
.NET
ASP (16) 
Contains 1 or more replies by the Author of this Article.
Contains 1 or more replies by 
