Solved

ADO: sorting by a string

Posted on 2001-07-09
24
1,529 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
Comment Utility
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
Comment Utility
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
Comment Utility
try this:

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



0
 
LVL 1

Author Comment

by:nguyenn
Comment Utility
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
Comment Utility
Post more codes.

hongjun
0
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

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

Expert Comment

by:rkot2000
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I got the same error at:

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

Expert Comment

by:rkot2000
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi rkot2000,

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

Have a nice day :)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now