Microsoft KB Archive/178828

= FIX: Recordset Displays Previous Page's Data When Filtering =

Article ID: 178828

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft Visual InterDev 1.0 Standard Edition
 * Microsoft Data Access Components 2.5

-



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.



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



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.

