FIX: 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.

   <%

   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 this to be a problem in the Microsoft products listed at the beginning of this article.

This bug was corrected in ADO version 2.0 and later.

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/

http://www.microsoft.com/data

Additional query words: Cursor ADO 1.5 Filtering data form dataform wizard kbdsi kbcode

Keywords : kbcode kbADO150bug kbADO200fix kbVisID100bug kbGrpMDAC
Version : WINDOWS:1.0,1.5
Platform : WINDOWS
Issue type : kbbug