Microsoft KB Archive/264002

= FIX: Compound Filter Condition in ADO2.5 Incorrectly Returns 0 Records =

Article ID: 264002

Article Last Modified on 10/15/2002

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q264002



SYMPTOMS
Under all prior versions of ActiveX Data Objects (ADO), using a compound filter condition with expressions joined by an AND clause returned the correct subset of records. In ADO 2.5, there are a number of circumstances where a filter condition incorrectly returns 0 records.



RESOLUTION
This problem has been fixed in MDAC 2.6. For previous versions of MDAC the following workarounds are available:   Build a local index on the second and subsequent fields in the filter condition. For example, to filter on the expression FIELD1 = 'Value1' and FIELD2 = 'Value2' add the following line to your code before the filter condition is applied: rs.Fields(&quot;FIELD2&quot;).Properties(&quot;Optimize&quot;) = True Concatenate the filter condition to itself with the OR clause. For example, to filter on the expression FIELD1 = 'Value1' and FIELD2 = 'Value2' add the lines: FilterString = &quot;(FIELD1 = 'Value1' and FIELD2 = 'Value2')&quot; FilterString = FilterString & &quot; OR &quot; & FilterString rs.Filter = FilterString If all the filter expressions test equality to a character expression, use LIKE in the filter expression. In place of FilterString = &quot;FIELD1 = 'Value1' and FIELD2 = 'Value2' substitute: FilterString = &quot;FIELD1 LIKE 'Value1%' and FIELD2 LIKE 'Bar%&quot;   Concatenate the filter condition to itself with the OR clause. For example, to filter on the expression FIELD1 = 'Value1' and FIELD2 = 'Value2' add the lines: FilterString = &quot;(FIELD1 = 'Value1' and FIELD2 = 'Value2')&quot; FilterString = FilterString & &quot; OR &quot; & FilterString rs.Filter = FilterString If all the filter expressions test equality to a character expression, use LIKE in the filter expression. In place of FilterString = &quot;FIELD1 = 'Value1' and FIELD2 = 'Value2' substitute: FilterString = &quot;FIELD1 LIKE 'Value1%' and FIELD2 LIKE 'Bar%&quot;   If all the filter expressions test equality to a character expression, use LIKE in the filter expression. In place of FilterString = &quot;FIELD1 = 'Value1' and FIELD2 = 'Value2' substitute: FilterString = &quot;FIELD1 LIKE 'Value1%' and FIELD2 LIKE 'Bar%&quot; 



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
Due to the general nature of the problem, two reproduction scenarios are included here.   In the simplest case, an in-memory recordset, the .Sort method must first be applied to the recordset in order to produce the anomaly. Add a reference to ADO 2.5 in the project that contains this code.

The following sample program writes five rows to the in-memory recordset, sorts it, and applies a filter that should return one row: Dim rs As New ADODB.Recordset Dim filterstring As String

With rs   .Fields.Append &quot;Field1&quot;, adInteger .Fields.Append &quot;Field2&quot;, adInteger .Open

' write 5 records For i = 1 To 5 .AddNew Array(&quot;Field1&quot;, &quot;Field2&quot;), Array(i, i)   Next .Sort = &quot;Field1 ASC&quot;

' Workaround 1 - uncomment this line to create a local index on the second and subsequent condition '.Fields(&quot;Field2&quot;).Properties(&quot;Optimize&quot;) = True

filterstring = &quot;(Field1 = 1 and Field2 = 1)&quot;

' Workaround 2 - uncomment this line to OR the filterstring with itself '   filterstring = filterstring & &quot; OR &quot; & filterstring

rs.Filter = filterstring Debug.Print &quot;I should have 1 record, I actually have: &quot; & rs.RecordCount End With

rs.Close In the second scenario, a hierarchical recordset is created from the sample NWIND Employees and Orders tables. Modify the pointer to NWIND.MDB in the connection string as appropriate. Also, add a reference to ADO 2.5 in the project that contains this code.

For convenience, a filter is applied to the Employees table to display only one parent record.

A compound filter is applied to the child recordset that should return nine records; instead, it returns zero (0) records. This is indicated by the value for &quot;Child record count&quot; that appears in the debug window. Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim rsCh As ADODB.Recordset Dim i, j As Integer Dim FilterString As String

' Change the pointer to NWIND in the connection string With cn   .ConnectionString = &quot;Provider=MSDATASHAPE;&quot; & _ &quot;Data Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=NWIND.MDB&quot; .Open End With

With rs   .Open &quot;SHAPE {SELECT EmployeeID FROM Employees} AS Employees &quot; & _ &quot;APPEND ({SELECT EmployeeID, ShipCountry FROM Orders} AS Orders &quot; & _       &quot;RELATE EmployeeID TO EmployeeID)&quot;, _ cn, adOpenStatic, adLockOptimistic End With

' print the parent and child field names For i = 0 To rs.Fields.Count - 1 Debug.Print rs.Fields(i).Name, If rs.Fields(i).Type = adChapter Then Set rsCh = rs.Fields(&quot;Orders&quot;).Value For j = 0 To rsCh.Fields.Count - 1 Debug.Print rsCh.Fields(j).Name, Next End If Next Debug.Print

' print the data rs.MoveFirst ' filter the parent rs so that only EmpID 1 is displayed rs.Filter = &quot;EmployeeID = 1&quot; Do While Not rs.EOF Debug.Print rs.Fields(&quot;EmployeeID&quot;) Set rsCh = rs.Fields(&quot;Orders&quot;).Value

' workaround 1 - build a local index on the second and subsequent ' columns returned 'rsCh.Fields(&quot;ShipCountry&quot;).Properties(&quot;Optimize&quot;) = True

FilterString = &quot;(EmployeeID = 1 and ShipCountry = 'France')&quot; ' workaround 2 - concatenate the ' same expression twice using OR - uncomment to apply this workaround '   FilterString = FilterString &&quot; OR &quot;& FilterString rsCh.Filter = FilterString

' this should return 9 records Debug.Print &quot;Child record count: &quot; & rsCh.RecordCount Do While Not rsCh.EOF Debug.Print &quot;       &quot; & rsCh.Fields(&quot;EmployeeID&quot;).Value, _ rsCh.Fields(&quot;ShipCountry&quot;).Value rsCh.MoveNext Loop Debug.Print Loop   In the second scenario, a hierarchical recordset is created from the sample NWIND Employees and Orders tables. Modify the pointer to NWIND.MDB in the connection string as appropriate. Also, add a reference to ADO 2.5 in the project that contains this code.

For convenience, a filter is applied to the Employees table to display only one parent record.

A compound filter is applied to the child recordset that should return nine records; instead, it returns zero (0) records. This is indicated by the value for &quot;Child record count&quot; that appears in the debug window. Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim rsCh As ADODB.Recordset Dim i, j As Integer Dim FilterString As String

' Change the pointer to NWIND in the connection string With cn   .ConnectionString = &quot;Provider=MSDATASHAPE;&quot; & _ &quot;Data Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=NWIND.MDB&quot; .Open End With

With rs   .Open &quot;SHAPE {SELECT EmployeeID FROM Employees} AS Employees &quot; & _ &quot;APPEND ({SELECT EmployeeID, ShipCountry FROM Orders} AS Orders &quot; & _       &quot;RELATE EmployeeID TO EmployeeID)&quot;, _ cn, adOpenStatic, adLockOptimistic End With

' print the parent and child field names For i = 0 To rs.Fields.Count - 1 Debug.Print rs.Fields(i).Name, If rs.Fields(i).Type = adChapter Then Set rsCh = rs.Fields(&quot;Orders&quot;).Value For j = 0 To rsCh.Fields.Count - 1 Debug.Print rsCh.Fields(j).Name, Next End If Next Debug.Print

' print the data rs.MoveFirst ' filter the parent rs so that only EmpID 1 is displayed rs.Filter = &quot;EmployeeID = 1&quot; Do While Not rs.EOF Debug.Print rs.Fields(&quot;EmployeeID&quot;) Set rsCh = rs.Fields(&quot;Orders&quot;).Value

' workaround 1 - build a local index on the second and subsequent ' columns returned 'rsCh.Fields(&quot;ShipCountry&quot;).Properties(&quot;Optimize&quot;) = True

FilterString = &quot;(EmployeeID = 1 and ShipCountry = 'France')&quot; ' workaround 2 - concatenate the ' same expression twice using OR - uncomment to apply this workaround '   FilterString = FilterString &&quot; OR &quot;& FilterString rsCh.Filter = FilterString

' this should return 9 records Debug.Print &quot;Child record count: &quot; & rsCh.RecordCount Do While Not rsCh.EOF Debug.Print &quot;       &quot; & rsCh.Fields(&quot;EmployeeID&quot;).Value, _ rsCh.Fields(&quot;ShipCountry&quot;).Value rsCh.MoveNext Loop Debug.Print Loop </li></ol>

Keywords: kbbug kbfix kbmdac260fix KB264002

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.