FIX: Recordset Displays Previous Page's Data When Filtering

View products that this article applies to.

This article was previously published under Q178828

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.

↑ Back to the top

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).

↑ Back to the top

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
   %>

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This bug was corrected in ADO version 2.0 and later.

↑ Back to the top

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:

http://msdn.microsoft.com/en-us/data/aa937729.aspx

↑ Back to the top