BUG: Error Setting Sort to Char Field Greater than 255

ID: Q189837

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

SYMPTOMS

When you use an ADO recordset object, you cannot Sort on a field with more than 255 characters. Attempting to sort on a field with more that 255 characters results in the following error:

Run-time error '-2147217824 (80040e60)': The requested order could not be opened

You also see this error on attempting to sort on a SQL Server Text field, a Microsoft Access Memo field, or other Binary Large Object (BLOB) field.

With ADO 2.1 Service Pack 2 and later, this error does not occur on attempting to sort on a field with more than 255 characters. However, this error does occur on attempting to sort on a Binary Large Object (BLOB) field.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

Start Visual Basic and create a Standard EXE Project.

On the Project menu, choose References and add a reference to Microsoft ActiveX Data Objects 2.0 Library.

Place the following code in the Load Event of the form. Note that "Description" is a Memo field in the Categories table of the Nwind.MDB database that comes with Visual Basic:


   Private Sub Form_Load()
         Dim rs1 As New ADODB.Recordset
         Dim rs2 As New ADODB.Recordset
         Dim cn As New ADODB.Connection
         Dim rs3 As New ADODB.Recordset

         
rs1.Fields.Append "F1", adInteger
         rs1.Fields.Append "F2", adChar, 255
         rs1.Open
         rs1.Sort = "F2" '<==  This works
         rs2.Fields.Append "F1", adInteger
         rs2.Fields.Append "F2", adChar, 256
         rs2.Open
         rs2.Sort = "F2" '<==  Error occurs here, ADO 2.0 or earlier
            
         'Uncomment the following lines to test with ADO 2.1 sp2                     'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
         "C:\Program Files\Microsoft Visual Studio\VB98\Nwind.MDB"
         'rs3.CursorLocation = adUseClient
         'rs3.Open "select * from categories", cn, adOpenStatic, _
         '    adLockOptimistic
         'rs3.Sort = "Description" '<== Error occurs here, ADO 2.1 sp2+

   End Sub

If using ADO 2.1 or later, uncomment the lines above to test.

Press the F5 key to run the code.

RESULT: The following error message appears:

Run-time error '-2147217824 (80040e60)': The requested order could not be opened

Additional query words: kbvbp600 kbADO kbADO200 kbado200bug kbado210sp2bug kbdatabase kbmdac210sp2

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbbug