DOCUMENT:Q191919  27-AUG-1998  [vbwin]
TITLE   :BUG: DataGrid Doesn't Handle Special Filter Constants Correctly
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
 - ActiveX Data Objects (ADO), versions 1.5, 2.0
---------------------------------------------------------------------

SYMPTOMS
========

When the filter of an ADO recordset is set to one of the special constants,
such as adFilterPendingRecords, the bound DataGrid still displays all of
the rows in the recordset. If the filter is set to specify a particular
field, such as "State = 'CA'," the DataGrid will only show the rows that
are specific to that filter.

RESOLUTION
==========

Executing a Recordset.Requery after applying the filter constant will
repopulate the grid with the correct data. This option may have a negative
impact on performance as the query that created the data will need to be
executed again.

STATUS
======

Microsoft has confirmed this to be a bug in the Microsoft products
listed at the beginning of this article.

MORE INFORMATION
================

The code below that filters based on a field shows the appropriate data in
the grid.

Modifying records in the data grid and setting the Filter property of the
recordset to display only records with pending changes (Filter =
adFilterPendingRecords) causes the recordset to correctly filter on those
changed records. The data grid, however, does not correctly display the
filtered recordset object.

Steps to Reproduce Behavior
---------------------------

1. Create a Standard EXE Project in Visual Basic. Form1 is created by
   default.

2. From the Project/References menu, select Microsoft ActiveX Data Objects
   2.0 Library.

3. From the Project/Components menu, select Microsoft DataGrid Control.

4. Add a DataGrid Control, and two CommandButtons to Form1.

5. Set the name and caption properties of the CommandButtons as follows:

      Command1
      Name = cmdFilterField
      Caption = FilterField

      Command2
      Name = cmdFilterPending
      Caption = FilterPending

6. Add the following code to the General Declarations of Form1:

      Dim rsAuthors As New ADODB.Recordset

      Private Sub cmdFilterField_Click()
        rsAuthors.Filter = adFilterNone
        rsAuthors.Filter = "State = 'UT'"
      End Sub

      Private Sub cmdFilterPending_Click()
        rsAuthors.Filter = adFilterNone
        rsAuthors.Filter = adFilterPendingRecords
      End Sub

      Private Sub Form_Load()
        Dim cnPubs As New ADODB.Connection
        Dim strConn As String

        ' You may have to change the following line
        ' to a valid SQL Server
        strConn = "Provider=SQLOLEDB;Data Source=(local);" & _
                 "Initial Catalog=pubs;"

        cnPubs.CursorLocation = adUseClient
        cnPubs.Open strConn, "sa", ""

        rsAuthors.Open "SELECT * FROM Authors", cnPubs, adOpenStatic, _
                  adLockBatchOptimistic

        Set rsAuthors.ActiveConnection = Nothing

        cnPubs.Close

        Set DataGrid1.DataSource = rsAuthors

      End Sub

 7. Press the F5 key to run the app and note behavior of the different
    filter methods.

Additional query words: kbCtrl kbVBp600bug kbdse kbDSupport kbVBp kbADO150
kbVBp500bug kbADO200
======================================================================
Platform          : WINDOWS
Issue type        : kbbug

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.