Solved

ADO: sorting by a string

Posted on 2001-07-09
24
1,585 Views
Last Modified: 2008-03-03
I'm using ADO to create a recordset, but whenever I sort my ADO by a string (eg. "Name"), I got an runtime error: -2147217824 - Sort order cannot be applied). "Name" field is declared as adVariant

Could anyone tell me how could I sort by "Name" field?

Thanks in advance
nguyenn
0
Comment
Question by:nguyenn
  • 9
  • 9
  • 2
  • +3
24 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 6265526
Post your code. It should be something like this.

strSQL = "select * from your_table order by Name"

hongjun
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265550
you may try to conver name to the string (covert function depends on rdms)

select cstr(name) as Myname from table
0
 
LVL 11

Expert Comment

by:Otana
ID: 6265566
try this:

strSQL = "select * from your_table order by cast(Name as varchar(50))"



0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 1

Author Comment

by:nguyenn
ID: 6265567
I have my ADO recordset already, from my ADO recordset (myADO), I want to sort like:

myAdo.Sort = "Name"
0
 
LVL 33

Expert Comment

by:hongjun
ID: 6265575
Post more codes.

hongjun
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265586
back to your question on ssgrid if you are trying to sort ado.recordset and show results in the grid you need to use order by in the sql statement.
this is from www.shersoft.com

Does Data Widgets 3.11 support the Sort/Filter property of a Recordset?
Article ID: DAT346
Last reviewed: 6/30/1999

===========================================
Product: Data Widgets
Date Reported: 6/30/1999
Version Reported For: 3
Environment Reported In: N/A
Status: N/A
Access Level: General
===========================================

SUMMARY
When you issue a sort or filter on a standard OLE DB Recordset, the resulting recordset is returned to us in a hierarchical format.


OLE DB Chapter Limitations


New to OLE DB and ADO 2.0 are the notion of chapters. For more information on chapters, please refer to the OLE DB and ADO 2.0 SDK. Recordsets get organized into chapters when you sort or group your recordset. Data Widgets 3.11 does not support recordsets that are organized into chapters. As a consequence, Data Widgets 3.11 will not correctly bind to recordsets that have had their Sort property set or to recordsets that were retrieved by using the SHAPE command, such as the Master-Detail type created using the Data Environment Designer. We are looking into providing this support in the future.


Reference Number: DAT1459

0
 
LVL 11

Expert Comment

by:Otana
ID: 6265590
Problem is probably that you can't sort by a variant field. you'll have to cinvert the type somehow, but I think it must be done in the query. (see my suggestion above)
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265621
Otana, I believe you need to convert your fields in the select statement, not order by.
strSQL = "select * from your_table order by cast(Name as varchar(50))"
So it should be
Select cast(Name as varchar(50) from your_table order by ?..  ? this is for sql server
Cstr ? for access
and to_char for oracle
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265622
Otana, I believe you need to convert your fields in the select statement, not order by.
strSQL = "select * from your_table order by cast(Name as varchar(50))"
So it should be
Select cast(Name as varchar(50) from your_table order by ?..  ? this is for sql server
Cstr ? for access
and to_char for oracle
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6265626
Okie, here it is:

Private Sub Command1_Click()
  Dim myADO as ADODB.Recordset
  Dim adoTemp as Recordset
  Set myADO = MakeRS(adoTemp)
  call FillRS(1, "My Name")
  call FillRS(2,"Your Name")
  If not (myADO.EOF and myADO.BOF) Then
     myADO.Filter = adFilterNone
     myADO.Sort = "Name"
     ......
     ......
  End IF
End Sub

Function MakeRS(ByVal rsSource As ADODB.Recordset) As ADODB.Recordset
    Dim rsTemp As ADODB.Recordset

    Set rsTemp = New ADODB.Recordset
    With rsTemp.Fields
        .Append "CashierID", adInteger
        .Append "Name", adVariant
    End With
    Set MakeRS = rsTemp
End Function


Public Sub FillRS(ByVal CashierID As Integer, _
                  ByVal CashierName As String)
                 
    rsDest.AddNew
    rsDest.Fields(0).Value = CashierID
    rsDest.Fields(1).Value = CashierName
    rsDest.Update
End Sub
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265643
chahge to
Function MakeRS(ByVal rsSource As ADODB.Recordset) As ADODB.Recordset
   Dim rsTemp As ADODB.Recordset

   Set rsTemp = New ADODB.Recordset
   With rsTemp.Fields
       .Append "CashierID", adInteger
       .Append "Name", adVarChar, 100, adFldIsNullable
   End With
   Set MakeRS = rsTemp
End Function
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6265665
You aren't going to be able to do it I am afraid. Setting the sort property requires an index on the appropriate field for the recordset. A disconnected recordset cannot have an index associated with it so you won't be able to set the sort property. The only way you will get a recordset in the order you want is to put it in to the recordset in the correct order in the first place.
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6265670
It doenst help, rkot200. I still get the same error
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265698
maybe name is reserved. Try use different field name such as name1

TimCottee ,

It?s possible to build a dynamic index, I will post example in 10 minutes
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6265699
Thanks for your suggestion, TimCottee. But "CashierID" values get from a table (this table doesnt have "Name" field), then based on "CashierID" I reference to another table to get "Name"

Do we have anyway to sort by a string in ADO? Is it impossible to sort by a string in ADO?
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6265706
I thought "Name" is a reserved word too, but after I changed to a different name (CashierName), nothing different
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6265708
I thought "Name" is a reserved word too, but after I changed to a different name (CashierName), nothing different
0
 
LVL 5

Accepted Solution

by:
rkot2000 earned 100 total points
ID: 6265729
you forgot to open your recodset.
I am using ador but you can use adodb)
ador does not have extar overhead

Tim check this :

The Optimize property is "dynamic"; it is not a part of the Field object interface. It exists only in a Field object's Properties collection, provided that you set the CursorLocation property to adUseClient.

To create an index on a field, set the Optimize property to True. To delete the index, set this property to False.


rsTemp.Fields("Name").Properties("Optimize") = True
   
this should work I just tested

Option Explicit
Dim myADO As ADOR.Recordset

Private Sub Command1_Click()
 

 Set myADO = MakeRS()
 Call FillRS(1, "My Name")
 Call FillRS(2, "Your Name")
 myADO.Sort = "Name"
 
End Sub

Function MakeRS() As ADOR.Recordset
   Dim rsTemp As ADOR.Recordset
   Set rsTemp = New ADOR.Recordset
   With rsTemp.Fields
       .Append "CashierID", adInteger
       .Append "Name", adVarChar, 100, adFldIsNullable
   End With
   rsTemp.Open
   rsTemp.Fields("Name").Properties("Optimize") = True
   Set MakeRS = rsTemp
End Function


Public Sub FillRS(ByVal CashierID As Integer, _
                 ByVal CashierName As String)
                 
   myADO.AddNew
   myADO.Fields(0).Value = CashierID
   myADO.Fields(1).Value = CashierName
   myADO.Update
End Sub
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6265763
I got the same error at:

rsTemp.Fields("Name").Properties("Optimize") = True
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265776
I am using ado 2.5 on NT4.0 sp6a and I tested my code.

Did you change data type to adVarChar?
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6265880
if you are using ado 2.0 you need to update it.

found on microsoft:


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.

http://support.microsoft.com/support/kb/articles/Q189/8/37.ASP?LN=EN-US&SD=gn&FR=0&qry=-2147217824&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ADO
0
 
LVL 5

Expert Comment

by:bob_online
ID: 6265894
It's possible this is simpler than everyone is making it.

If you are doing this:

rsStuff.Sort = "Name", you need to lose the "=" sign.

rsStuff.Sort "Name"

If that doesn't solve it, the problem has to be the adVariant field type --- strings will sort.
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6266070
Hi rkot2000,

After replaced adVariant by adVarChar, everything is working fine. Thanks for helping me out

Have a nice day :)
0
 
LVL 1

Author Comment

by:nguyenn
ID: 6266089
Hi rkot2000,

After replaced adVariant by adVarChar, everything is working fine. Thanks for helping me out

Have a nice day :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access - Capture pressed key onclick 4 42
MsgBox 4 69
Excel Automation VBA 19 91
VBA - If Bookmark = "XXBOOKMARKXX" then 15 41
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question