ADO Find method only supports one criteria

Article translations Article translations
Article ID: 195222 - View products that this article applies to.
This article was previously published under Q195222
Expand all | Collapse all

On This Page

SYMPTOMS

When you attempt to use the ActiveX Data Objects (ADO) Find method to search by multiple criteria, or in other words you use AND, the following error occurs:
Error: 3001:
This application is using arguments that are of the wrong type, out of acceptable range or in conflict with one another.

CAUSE

The Find method is a single column operation only because the OLE DB specification defines IRowsetFind this way.

The ADO 2.5 and later documentation for the Find method (ADO) explains in the Remarks section of the Help Topic:
Only a single-column name may be specified in criteria. This method does not support multi-column searches.

RESOLUTION

This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact Microsoft Advisory Services.

Advisory Services is an hourly fee-based, consultative support option that provides proactive support beyond your break-fix product maintenance needs. This is a remote, phone-based support option that includes working with the same technician for assistance with issues like product migration, code review, or new program development. This service is typically used for shorter engagements, and is designed for developers and IT professionals who do not require the traditional onsite consulting or sustained account management services that are available from other Microsoft support options. For information on Microsoft Advisory Services, refer to this Microsoft Web site:http://support.microsoft.com/gp/advisoryservice Here are four possible workarounds to this limitation.
  • Use the ADO Filter property. It allows multiple criteria.

    -or-
  • Use a more restricted Select statement to either re-create the recordset or to create an additional recordset.

    -or-
  • Use the ADO Filter property and the Clone method. This allows you to find the correct bookmark in the clone without affecting the rows that are visible in the recordset.

    A code sample that illustrates this method follows:
          Dim cn As ADODB.Connection
          Dim rs As ADODB.Recordset
    
          'Create a variable for the Cloned Recordset.
          Dim clone_rs As ADODB.Recordset
    
          Set cn = CreateObject("ADODB.Connection")
          Set rs = CreateObject("ADODB.Recordset")
    
          With cn
           .ConnectionString = "PROVIDER=SQLOLEDB;" & _
                               "DATA SOURCE=<server>;" & _
                               "USER ID=<uid>;" & _
                               "PASSWORD=<pwd>;" & _
                               "INITIAL CATALOG=<init_cat>"
           .Open
          End With
    
          With rs
           .CursorLocation = adUseClient
           .CursorType = adOpenStatic
           .LockType = adLockBatchOptimistic
           .ActiveConnection = cn
           .Open "select * from authors"
          End With
    
          'A clone recordset has some benefits :
          ' - Very little overhead, it is only an object variable containning a
          '   reference to the original recordset.
          ' - It does not require another round trip to the server.
          ' - It maintains separate but shareable bookmarks with the original.
          ' - Closing and filtering clones does not affect the original or
          '   other clones.
    
          'Create a clone of the recordset.
          Set clone_rs = Rs.Clone
          'Apply a filter to the clone using the criteria passed in.
          clone_rs.Filter = "state = 'CA' AND city = 'Oakland'"
    
          If clone_rs.EOF Or clone_rs.BOF Then
          'If criteria not found move to EOF; just as ADO's Find
           rs.MoveLast
           rs.MoveNext
          Else
          'If found, move the Recordset's bookmark to the same location as the
          'clone's bookmark.
          rs.Bookmark = clone_rs.Bookmark
          End If
    
          clone_rs.Close
          Set clone_rs = Nothing
    
          rs.Close
          cn.Close
    
          Set rs = Nothing
          Set cn = Nothing
    
          End Sub
    					
    -or-

  • Create a custom Find routine. The custom Find method can be very simple with minimal features or more complicated and having all the features of the ADO Find method.

    A simple Multi_Find routine would be a modification of the ideas in the third workaround. For instance:
          Public Sub Multi_Find( _
           ByRef oRs As ADODB.Recordset, _
           sCriteria As String)
          '
          'This Sub Routine simulates a Find Method that accepts Multi-Find
          'Criteria. It searches columns in a recordset for specific values.
          '
          'ADO Recordset's Find Method has a limitation of single criteria
          'finds.
          'For instance:
          ' ADO Recordset's Find only accepts criteria like the following:
          '   rs.Find = "state = 'CA'"
          '
          'It generates an error if multiple criteria are passed to it:
          '   rs.Find = "state = 'CA' AND city = 'Oakland'"
          '
          'This Sub Routine has the following syntax:
          ' Multi_Find oRs, sCriteria
          'Where:
          ' oRs is the ADO Recordset object where the Find is to be done.
          ' sCriteria is a String in the same format as the Find method
          '  with the addition of multiple conditions can be provided so
          '  long as each is joined by an "AND".
          '
          'Example:
          ' Multi_Find rs, "state = 'CA' AND city = 'Oakland'"
    
          Dim clone_rs As ADODB.Recordset
          Set clone_rs = oRs.Clone
    
          clone_rs.Filter = sCriteria
    
          If clone_rs.EOF Or clone_rs.BOF Then
           oRs.MoveLast
           oRs.MoveNext
          Else
           oRs.Bookmark = clone_rs.Bookmark
          End If
    
          clone_rs.Close
          Set clone_rs = Nothing
    
          End Sub
    					
The limitations of this Multi-Find method versus the ADO Find method are that it does not support the "SkipRows", "SearchDirection", or "Start" parameters. If you want these features, you need to create a more complicated custom multi-find method. The Multi-Find method would have to parse the criteria string out properly, navigate the recordset accordingly while checking each criteria for matches.

STATUS

This behavior is by design.

MORE INFORMATION

The ADO Help documentation states the following for the Find method's first argument, criteria:
A String containing a statement that specifies the column name, comparison operator, and value to use in the search.

Steps to Reproduce Behavior

  1. Start Microsoft Visual Basic, Form1 is created by default.
  2. Set a Project Reference to the Microsoft ActiveX Data Objects Library.
  3. Insert a command button on the form.
  4. Insert the following code into the Command1_Click event:
          Dim objConnection As New ADODB.Connection
          Dim rs As New ADODB.Recordset
    
          objConnection.ConnectionString = _
          "Provider=SQLOLEDB;Data Source=<server>;" & _
          "User ID=<uid>;Password=<pwd>;" & _
          "Initial Catalog=Northwind"
          objConnection.Open
    
          rs.Open "Select * from customers", objConnection, _
          adOpenStatic, adLockOptimistic
          rs.Find "Customerid = 1 and companyname = 'Hello'"
    					
  5. Run the form and click the command button. RESULTS: The error occurs.

REFERENCES

For additional information, please search the MSDN Library for the topic Find method (ADO). For additional information, please see the following article in the Microsoft Knowledge Base:
193871 INFO: Passing ADO Recordsets in Visual Basic Procedures

Properties

Article ID: 195222 - Last Review: July 6, 2006 - Revision: 2.4
APPLIES TO
  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft Data Access Components 2.8
Keywords: 
kbdatabase kbprb KB195222

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com