Filter records

This is another neat trick to add to your collection and is a very useful one to know , particularly in a situation where you want a user to search a database . In this case we use the Recordset object's Filter property .

Code :

<%
our constants
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdTable = 2
'declare our variables
Dim objRS , strConnect , strFilter
'connect to our database , change the Data Source for your database path
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\asp\quotes.mdb;"
'create an ADO Recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
'open the tblquote table
objRS.Open "tblquote" , strConnect , adOpenStatic , adLockReadOnly , adCmdTable
'our filter , match all records who's author is unknown
strFilter = "author LIKE 'unknown'"
'set the filter
objRS.Filter = strFilter
'if there are matching records the display a table with the records
If not objRS.EOF Then
Response.Write "<table><tr><td>id</td><td>quote</td><td>author</td></tr>"
While Not objRS.EOF
Response.Write "<tr><td>" & objRS("id") & "</td>" & _
"<td>" & objRS("quote") & "</td>" & _
"<td>" & objRS("author") & "</td></tr>"
objRS.MoveNext
Wend
Response.Write "</table>"
End If
'close and destroy Recordset object
objRS.Close
Set objRS = Nothing
%>

Download :

The database in this example can be downloaded from here