KB181487 - FIX: ADO Filter Fails If Filter String Contains "= '*strval'"

Article ID: 181487

Article Last Modified on 3/14/2005

Microsoft ActiveX Data Objects 1.5

This article was previously published under Q181487

SYMPTOMS

Receiving error 3001 using an ADODB.Recordset when the filter string contains '*stringvalue' pattern for the string column and the operator is '='.

CAUSE

The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft FrontPage listed at the beginning of this article. This bug was corrected in ActiveX Data Objects (ADO) version 2.0 and later.

MORE INFORMATION

Put_Filter fails if the filter string contains '*stringvalue' pattern for the string column when the operator is an '='. It should not fail. It should return records that have fields that literally match '*stringvalue'; that is, that have an asterisk as the first character. Instead, it acts like the LIKE syntax, treating the asterisk as a wildcard character.

Note that using a wildcard character as the first character in the string criteria of a LIKE clause is not supported. You may only use a wildcard character as the ending character, or as both the beginning and ending character, of the criteria of a LIKE cause for an ADO Filter.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

226119 INFO: ADO's Find and Filter Support Usage of Wildcards

Steps to Reproduce Behavior

   Sub FilterEqualFails()
       Dim conn As New ADODB.Connection
       Dim rs As New ADODB.Recordset

       conn.Open "<DSN_Name>", "sa", ""
       On Error Resume Next
       conn.Execute "drop table Test"
        On Error GoTo errh
       conn.Execute "create table Test(id int primary key,f1 " & _
                    "varchar(200))"
       rs.Open "select * from Test", conn, adOpenKeyset, adLockOptimistic
       rs.AddNew
       rs("id") = 1
       rs("f1") = "*x Hello world"
       rs.Update
       rs.Requery
       rs.Filter = "f1 = '*x Hello world'"
       Exit Sub

   errh:

       Debug.Print "Error:", Err, Hex(Err)
       Debug.Print "Source:", Err.Source
       Debug.Print "Desc:", Err.Description

   End Sub

When you execute this code, you expect it to find the row with the value. However, this is the output received when you execute the code:

   Error:        3001         BB9
   Source:       ADODB.Recordset

Keywords: kbbug kbfix kbdatabase kbado200fix kbmdacnosweep KB181487