ADO: sorting by a string

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
LVL 1
nguyennAsked:
Who is Participating?
 
rkot2000Commented:
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
 
hongjunCommented:
Post your code. It should be something like this.

strSQL = "select * from your_table order by Name"

hongjun
0
 
rkot2000Commented:
you may try to conver name to the string (covert function depends on rdms)

select cstr(name) as Myname from table
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
OtanaCommented:
try this:

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



0
 
nguyennAuthor Commented:
I have my ADO recordset already, from my ADO recordset (myADO), I want to sort like:

myAdo.Sort = "Name"
0
 
hongjunCommented:
Post more codes.

hongjun
0
 
rkot2000Commented:
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
 
OtanaCommented:
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
 
rkot2000Commented:
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
 
rkot2000Commented:
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
 
nguyennAuthor Commented:
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
 
rkot2000Commented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
nguyennAuthor Commented:
It doenst help, rkot200. I still get the same error
0
 
rkot2000Commented:
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
 
nguyennAuthor Commented:
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
 
nguyennAuthor Commented:
I thought "Name" is a reserved word too, but after I changed to a different name (CashierName), nothing different
0
 
nguyennAuthor Commented:
I thought "Name" is a reserved word too, but after I changed to a different name (CashierName), nothing different
0
 
nguyennAuthor Commented:
I got the same error at:

rsTemp.Fields("Name").Properties("Optimize") = True
0
 
rkot2000Commented:
I am using ado 2.5 on NT4.0 sp6a and I tested my code.

Did you change data type to adVarChar?
0
 
rkot2000Commented:
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
 
bob_onlineCommented:
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
 
nguyennAuthor Commented:
Hi rkot2000,

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

Have a nice day :)
0
 
nguyennAuthor Commented:
Hi rkot2000,

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

Have a nice day :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.