FIX: Recordset Displays Previous Page's Data When Filtering (178828)

SYMPTOMS

When using the Filter property in ActiveX Data Objects (AD0) version 1.5 in conjunction with the AbsolutePaging and PageSize properties, the recordset returned from the database displays the same record or records on multiple pages.

This scenario is common when you use Filtering in DataForm Wizard generated Active Server Pages (ASP) pages. For example, using a table that contains 22 records in an ASP page that is set to paginate after 10 records, shows records from the first page on the second and third pages when a Filter has been applied to that result set.

CAUSE

The following conditions cause this error to occur:

The table you are filtering does not have a primary key (on any field in the table) or does not have an "Order By" clause on the filtered field.
ADO version 1.5 has been installed (this occurs when installing IIS 4.0 or MDAC 1.5).

RESOLUTION

There are two possible workarounds:

When designing your table, use a primary key (or Index) so that the rows will be ordered. -or-
Set the cursor location property on the ASP page to "rs.CursorLocation =adUseClient" prior to opening the Recordset object. This sets the cursor to be created on the client.

The solution outlined below describes how to change the DataForm Wizard generated ASP pages.

In xxxList.ASP and xxxForm.ASP, go to the following section and add the line marked with the asterisk below:

   <%

   If fNeedRecordset Then

       Set conn= Server.CreateObject("ADODB.Connection")
       conn.ConnectionTimeout = Session("conn_ConnectionTimeout")
       conn.CommandTimeout = Session("conn_CommandTimeout")
       conn.Open Session("conn_ConnectionString"),
        Session("conn_RuntimeUserName"), Session("conn_RuntimePassword")
       Set cmdTemp = Server.CreateObject("ADODB.Command")
       Set rs= Server.CreateObject("ADODB.Recordset")
       cmdTemp.CommandText = "SELECT * FROM dbo.""tblSched"""
       cmdTemp.CommandType = 1
       Set cmdTemp.ActiveConnection = conn
       rs.CursorLocation = 3  ' ***** Add this line (3 is the same a
                adUseClient)
       rs.Open cmdTemp, , 1, 3

   End If
   %>

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

http://support.microsoft.com/search/default.aspx?qu=vinterdev

Microsoft Data Access Components (MDAC)can be downloaded from: