ADO: sorting by a string

Post your code. It should be something like this.

strSQL = "select * from your_table order by Name"

hongjun

you may try to conver name to the string (covert function depends on rdms)

select cstr(name) as Myname from table

try this:

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

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

myAdo.Sort = "Name"

Post more codes.

hongjun

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

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)

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

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

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

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

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.

It doenst help, rkot200. I still get the same error

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

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?

I thought "Name" is a reserved word too, but after I changed to a different name (CashierName), nothing different

I thought "Name" is a reserved word too, but after I changed to a different name (CashierName), nothing different

I got the same error at:

rsTemp.Fields("Name").Properties("Optimize") = True

I am using ado 2.5 on NT4.0 sp6a and I tested my code.

Did you change data type to adVarChar?

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

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.

Hi rkot2000,

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

Have a nice day :)

Hi rkot2000,

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

Have a nice day :)