PRB: Setting the Sort Property of an ADO Recordset Object Causes Run-Time Error Message 3251
Retired KB Content Disclaimer
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.
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 BehaviorThe 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.
- Open a new Standard EXE project in Visual Basic 5.0 or 6.0. Form1 is created by default.
- On the Project menu, click References. Add a reference to the Microsoft ActiveX Data Objects Library.
- Add a Command button (Command1) to the default form and change the Caption property to Sort Records.
- Add the following code to the form: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.
Option ExplicitDim cn As ADODB.ConnectionDim rs As ADODB.RecordsetPrivate Sub Form_Load() Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB.1;Data Source=MyServer;Initial Catalog=pubs;User ID=<user name>"End SubPrivate 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.CloseEnd Sub
- Run the project and click the Command button. Note that you see the error shown in the "Symptoms" section of this article.
- Stop the project and uncomment the following line:
' rs.CursorLocation = adUseClient
- Run the project again. Note that it completes successfully, and that the records print in descending order on au_lname.
Article ID: 253248 - Last Review: 11/06/2003 23:06:25 - Revision: 2.0
- kbprb KB253248