Filter and RecordCount Properties Example (JScript)

This example opens a Recordset on the Companies table of the Northwind database and then uses the Filter property to limit the records visible to those where the CompanyName field starts with the letter D. Cut and paste the following code to Notepad or another text editor, and save it as FilterJS.asp.

<!-- BeginFilterJS -->
<%@  Language=JavaScript %>
<%// use this meta tag instead of adojavas.inc%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->

<html>

<head>
<title>ADO Recordset.Filter Example</title>
<style>
<!--
BODY {
   font-family: 'Verdana','Arial','Helvetica',sans-serif;
   BACKGROUND-COLOR:white;
   COLOR:black;
    }
.thead {
   background-color: #008080; 
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
   color: white;
   }
.thead2 {
   background-color: #800000; 
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
   color: white;
   }
.tbody { 
   text-align: center;
   background-color: #f7efde;
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
    }
-->
</style>
</head>

<body bgcolor="White">

<h1>ADO Recordset.Filter Example</h1>
<!-- Page text goes here -->
<%
    // connection and recordset variables
    var Cnxn = Server.CreateObject("ADODB.Connection")
    var strCnxn = "Provider='sqloledb';Data Source=" + Request.ServerVariables("SERVER_NAME") + ";" +
            "Initial Catalog='Northwind';Integrated Security='SSPI';";
    var rsCustomers = Server.CreateObject("ADODB.Recordset");
    var SQLCustomers = "select * from Customers;";
    // record variables
    var fld, filter
    var showBlank = " ";
    var showNull = "-NULL-";
    
    try
    {
        //open connection 
        Cnxn.Open(strCnxn);
    
        // create recordset client-side using object refs
        rsCustomers.ActiveConnection = Cnxn;
        rsCustomers.CursorLocation = adUseClient;
        rsCustomers.CursorType = adOpenKeyset;
        rsCustomers.LockType = adLockOptimistic;
        rsCustomers.Source = SQLCustomers;
        rsCustomers.Open();
    
        rsCustomers.MoveFirst();
    
        //set filter
        filter = "CompanyName LIKE 'b*'";
        rsCustomers.Filter = filter
    
        if (rsCustomers.RecordCount == 0) {
            Response.Write("No records matched ");
            Response.Write (SQLCustomers + "So cannot make table...");
            Cnxn.Close();
            Response.End
        }
        else {
        // show the data
            Response.Write('<table width="100%" border="2">');    
            while(!rsCustomers.EOF) {
                Response.Write('<tr class="tbody">');
                for (var thisField = 0; thisField < rsCustomers.Fields.Count; thisField++) {
                    fld = rsCustomers(thisField);
                    fldValue = fld.Value;
                    if (fldValue == null)
                        fldValue = showNull;
                    if (fldValue == "")
                        thisField=showBlank;
                    Response.Write("<td>" + fldValue + "</td>")
                }
                rsCustomers.MoveNext();
                Response.Write("</tr>");
            }
            // close the table
            Response.Write("</table>");
        }
    }    
    catch (e)
    {
        Response.Write(e.message);
    }
    finally
    {
        // clean up
        if (rsCustomers.State == adStateOpen)
            rsCustomers.Close;
        if (Cnxn.State == adStateOpen)
            Cnxn.Close;
        rsCustomers = null;
        Cnxn = null;
    }
%>

</body>

</html>
<!-- EndFilterJS -->

Filter and RecordCount Properties Example (JScript)

See Also