PRB: Setting the Sort Property of an ADO Recordset Object Causes Run-Time Error Message 3251

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

On This Page

SYMPTOMS

Setting the Sort property of an ADO Recordset object whose CursorLocation property has not been explicitly set or has been set to adUseServer, causes the following error message to appear:
Runtime error '3251': The operation requested by the application is not supported by the Provider
Or, under MDAC 2.6 or later, the following message will appear:
Runtime error '3251': Current provider does not support the necessary interfaces for sorting or filtering.

CAUSE

The provider in use does not expose the OLEDB interfaces required by the ADO Sort method. These are optional interfaces. Most OLEDB providers do not support the Sort method when using server-side cursors.

RESOLUTION

The ADO Client Cursor Engine does support the Sort method. Set the CursorLocation property of the ADO recordset object to adUseClient before opening it.

-or-

If the OLEDB provider supports a query language, such as SQL, you can request the records in a sorted order. For example, the Microsoft Jet and Microsoft SQL Server OLEDB providers both support the SQL ORDER BY clause. If you want to re-sort the data, you have to either re-select it or use a client-side cursor.

STATUS

This behavior is by design.

MORE INFORMATION

The default setting for the CursorLocation property of an ADO Recordset object when not specified explicitly is adUseServer. This setting uses a data provider or driver-supplied cursor to create the recordset.

Setting the CursorLocation property of the ADO recordset to adUseClient uses a client-side cursor supplied by the local cursor library to create and populate the recordset. The ADO Client Cursor Engine supports the Sort method and can be used if the OLDEB provider does not.

Steps to Reproduce Behavior

The following example uses the SQL Server 7.0 pubs database. The problem can also be reproduced using the Microsoft Jet OLEDB Provider and an Access database.
  1. Open a new Standard EXE project in Visual Basic 5.0 or 6.0. Form1 is created by default.
  2. On the Project menu, click References. Add a reference to the Microsoft ActiveX Data Objects Library.
  3. Add a Command button (Command1) to the default form and change the Caption property to Sort Records.
  4. Add the following code to the form:
    Option Explicit
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Private Sub Form_Load()
      Set cn = New ADODB.Connection
      cn.Open "Provider=SQLOLEDB.1;Data Source=MyServer;Initial   Catalog=pubs;User ID=<user name>"
    End Sub
    
    Private Sub Command1_Click()
      Set rs = New ADODB.Recordset
    '  rs.CursorLocation = adUseClient
      rs.open "Select * from Authors", cn, adOpenKeyset, adLockReadOnly, adCmdText
      rs.Sort = "au_lname desc"
      Do While Not rs.EOF
        Debug.Print rs!au_lname
        rs.MoveNext
      Loop
      rs.Close
    End Sub
    					
    Note You must adjust the connect string to point to a valid data source. Make sure that the User ID has the appropriate permissions to perform this operation on the database.
  5. Run the project and click the Command button. Note that you see the error shown in the "Symptoms" section of this article.
  6. Stop the project and uncomment the following line:
    '  rs.CursorLocation = adUseClient
    					
  7. Run the project again. Note that it completes successfully, and that the records print in descending order on au_lname.

Properties

Article ID: 253248 - Last Review: November 6, 2003 - Revision: 2.0
APPLIES TO
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.01
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
Keywords: 
kbprb KB253248
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

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