Microsoft KB Archive/192040

= BUG: Incorrect Results Returned DataForm Wizard Filter Command =

Article ID: 192040

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Visual InterDev 1.0 Standard Edition
 * Microsoft Data Access Components 1.5
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1 Service Pack 2

-



This article was previously published under Q192040



SYMPTOMS
When attempting to Filter Data using the Filter option in an application generated by the Visual InterDev 1.0 Data Form Wizard or when returning an ADO Recordset when using "AbsolutePaging," "PageSize," and the "Filter" properties, incorrect results are displayed.

For example, using a table that contains 22 records in a DataForm-Wizard- generated page that is set (by default) to paginate after 10 records with a filter set, the ASP page will correctly display the first two pages.

On the final page, rather than displaying the last two records of the resultset, it repeats the data from page two. The behavior may vary slightly depending on your data and the number of results returned.



CAUSE
The cause of this problem for MDAC 1.5 installations is a bug with filtering. This bug has been fixed in MDAC 2.0; however, the behavior may still occur with MDAC 2.x.

The cause of this problem for MDAC 2.x installations is that the underlying database does not support a cursor engine, therefore filtering is not possible without a client supplied cursor. An example of a database that does not support a cursor engine is Microsoft Access. An example of a database that supports a cursor engine is Microsoft's SQL Server.



RESOLUTION
For both MDAC 1.5 and MDAC 2.x installations, you will need to set the CursorLocation property of the recordset object to adUseClient (3) prior to opening the recordset. This means that the underlying data provider (OLE DB) will maintain the cursor (query results).

In xxxList.ASP and xxxForm.ASP, go to the following section and add the line marked with the asterisks 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 '* Use adUseClient for Cursor the location * rs.Open cmdTemp,, 1, 3 End If  %> For MDAC 2.x installations, setting a client-side cursor will force a Static CursorType. Having a static CursorType may cause problems with concurrency of data. For example, if two clients attempt to update the same record, the first will succeed, but the second will fail since the changes the first client made have caused the second clients cursor information to be "out of synch". The error encountered will read:

Microsoft Cursor Engine error '80040e38'

The specified row could not be located for updating: Some values may have been changed since it was last read.

/appname/xxxAction.asp, line ...

To resolve this behavior, refresh the cursor information before updating the recordset. To do this, you will use the Recordset Object's Requery method. Edit xxxAction.asp and locate the following section of code. Add the lines marked with the asterisks below: Case "Update" On Error Resume Next ' Make sure we exit and re-process the form if session has timed out If IsEmpty(Session("RS_Recordset")) Then Response.Redirect "xxxForm.asp?FormMode[ASCII 237]it" End If  Set RS = Session("RS_Recordset") CurrentBookmark = rs.absolutePosition '* preserve bookmark RS.Requery        '* Refresh the data before attempting the update rs.absolutePosition = CurrentBookmark '* restore bookmark If RS.EOF and RS.BOF Then Response.Redirect "xxxForm.asp"



STATUS
Microsoft has confirmed this to be a bug in Microsoft's Data Access Components version 1.0. The bug has been fixed in Microsoft's Data Access Components 2.x.

