BUG: Recordset Displays Previous Page's Data When Filtering

• ActiveX Data Objects (ADO), version 1.5
• Microsoft Visual InterDev, version 1.0

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.

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, 4 'Change locktype to OptimisticBatch.
   End If
   %>

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

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/support/vinterdev/