BUG: Incorrect Results Returned DataForm Wizard Filter Command (192040)

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"

BUG: Incorrect Results Returned DataForm Wizard Filter Command (192040)

SYMPTOMS

CAUSE

RESOLUTION

STATUS

REFERENCES