PRB: Filter Property Fails When Using Multiple Single Quotation Marks (202955)


The information in this article applies to:

  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 (GA)
  • Microsoft Data Access Components 2.1 SP1
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q202955

SYMPTOMS

When you filter a recordset for a character field that contains more than one single quotation mark, for instance CompanyName = O'Riley's Pub, one of the following error messages is displayed:
Run-time Error '3001' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
-or-
Run-time Error '-2147024809 (80070057)': The parameter is incorrect.

RESOLUTION

For additional information about workarounds for this issue, click the article number below to view the article in the Microsoft Knowledge Base:

245408 HOWTO: Find a Record Using Complex Criteria with ADO

MORE INFORMATION

Steps to Reproduce

  1. In Microsoft Visual Basic 5.0 or 6.0, open a new Standard EXE project. Form1 is created by default.
  2. On the Project menu, select References, and add a reference to the Microsoft ActiveX Data Objects 2.x Library.
  3. Place the following code in the Form1 Code module:
    Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection

With cn
.ConnectionString = "Provider=SqlOledb;Data Source=<server_name>;Initial Catalog=Northwind;User id=<uid>;Password=<pwd>;"
.Open
End With

cn.Execute "update employees set Lastname = 'Davolio''' where EmployeeID = 1"

With rs
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
.ActiveConnection = cn
.Open "select * from employees"
End With

rs.Filter = "Lastname = 'Davolio'''"
Debug.Print rs.RecordCount
Debug.Print rs(1)

cn.Execute "update employees set Lastname = 'O''Davolio''' where Employeeid = 1"

rs.requery
rs.Filter = "author = 'O''Davolio'''" 'Error Occurs Here
Debug.Print rs.RecordCount
Debug.Print rs(1)
  4. Run the project. You receive the following error message:
    Run-time Error '3001' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
    or, under MDAC 2.6, you receive the following error message:
    Run-time Error '-2147024809 (80070057)': The parameter is incorrect.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbClient kbDatabase kbprb KB202955
©2002 Microsoft Corporation. All rights reserved.