sorting a recordset

[aldinator]

how do i use

dtaData.Recordset.sort

i want to sort by the field called "Last Name" within the recordset

[Mega_Man]

Are you using ADO?

[aldinator]

no im just using the data control in the toolbox - i know it may not be the best way to go but .... meh.

can u help me plz?

[zuperman]

Sort Property


Specifies one or more field names on which the Recordset is sorted, and whether each field is sorted in ascending or descending order.

Settings and Return Values

Sets or returns a String of comma-separated field names to sort on, where each name is a Field in the Recordset, and is optionally followed by a blank and the keyword ASC or DESC, which specifies the field sort order.

Remarks

The data is not physically rearranged, but is simply accessed in the sorted order.

A temporary index will be created for each field specified in the Sort property if the CursorLocation property is set to adUseClient and an index does not already exist.

Setting the Sort property to an empty string will reset the rows to their original order and delete temporary indexes. Existing indexes will not be deleted.

The field cannot be named "ASC" or "DESC" because those names conflict with the keywords ASC and DESC. Give a field with a conflicting name an alias by using the AS keyword in the query that returns the Recordset.

[aldinator]

thanks zuperman that helps me understand exactly what it does - but it still doensn't solve my problem because i don't know the code to use. ive tried dtaData.recordset.sort = "Last Name ASC" but it always gives me and error

[Mega_Man]

Are you using a datagrid as well?

[aldinator]

no datagrid

its for a sports points keeper - textboxes are set to the data control

[Mega_Man]

What is the field you want to sort on?

[aldinator]

"Last Name"

[Mega_Man]

Originally posted by aldinator
"Last Name"

Ok, just put the name of ADODC control like

ADODC1.Recordset.Sort = "Last Name"

Mega.

[aldinator]

k thanks but is there a way to do it without adodc control? im using the regular data control

[Mega_Man]

Originally posted by aldinator
k thanks but is there a way to do it without adodc control? im using the regular data control

What version of VB are you running. I was only aware of the ADO databound control. I am nor familiar with the one you say you are using. Please tell me the exact name of the component you are using.

Mega.

[aldinator]

im using vb6 - its always in the toolbox to the left of the OLE control

[Mega_Man]

You have me stumped. I have the object browser to the left of OLE. Unless you're talking about the Data View Window in which case that is a totally different concept.

Mega.

[skald2k]

Sorting a recordset incurs fairly high cpu usage. Your best bet would be to sort on the database backend.

[aldinator]

here:

[Edneeis]

Its because the field name is two words, try enclosing it in bracets:

dtaData.Recordset.sort="[Last Name]"

When designing your database I find that two word names for fields aren't worth the hassle and should be avoided.

[aldinator]

edneeis: i have tried that with no success - even with a one-word field it doesn't work

[Edneeis]

What error are you getting exactly?

Also here is other info on sorting: http://www.vbforums.com/showthread.p...sort+recordset

[aldinator]

the error is:

[Edneeis]

Maybe you can only sort clientside cursors. What kind of database are you using?

This is from the help:

You can use the Sort property with dynaset- and snapshot-type Recordset objects....
...The Sort property doesn't apply to table- or forward-only–type Recordset objects. To sort a table-type Recordset object, use the Index property.

Just for the record I think DAO is of the devil by the way.

[Mega_Man]

Is there any reason why you cant change the data control to a ADO data control. The functionality is far greater and I dont think you would have to change any other code.

Mega.

[aldinator]

theres no reason why i couldn't change it - its just that ive never used it before

edneeis - thanks alot i think thats the info i needed - but ho would i use the index property to sort my table?

[Mega_Man]

Originally posted by aldinator
theres no reason why i couldn't change it - its just that ive never used it before

It looks and acts exactly the same except I have just created 2 projects binding a text box to the surname of a table.
The standard data control will not sort but the ADO one works like a charm.

Mega.

[Edneeis]

Originally posted by aldinator
theres no reason why i couldn't change it - its just that ive never used it before

edneeis - thanks alot i think thats the info i needed - but ho would i use the index property to sort my table?

I don't know its been too many moons since I've had to use DAO. I'd take Mega up on his suggestion. You'll probably have to change the connection string, but not that much else.

[aldinator]

k thanks a lot and im gonna try it right now.

ill get back to you in about 5 minutes

[aldinator]

im getting some errors

i don't know if ADO is going to work too great for this app... cause its done - im just adding some finishing touches...

so edneeis could you figure out what the index thing msn was talking about

[Mega_Man]

This is likely to be my last posting on this.. for tonight anyway. So one pointer.

1. Set the sort on the recordset when the form opens, not when you doing a .Movenext

Mega.

[aldinator]

yaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaay!!!!!!!!!!

i did it!!!

incase anyones wondering how i did it....
i changed the index value of Last Name to Yes(allow duplicates) and then used dtaData.recordset.index = "Last Name" and voila!!

[skald2k]

Good stuff

[JPnyc]

Hey all, I'm having a problem sorting a recordset, with a ADODB opened through code. I keep getting "object or provider does not support this type of op." or something like that. adOpenDynamic, and adLockOptimistic, are being used. The field I want to sort by is indexed. Any ideas?

[Kunchesm]

CursorLocation should be set to adUseClient

[JPnyc]

Thanks, I'll try it.

[JPnyc]

Should it be adOpenDynamic, adUseClient ? Because I'm still getting the same error mess.

[Kunchesm]

try with adOpenStatic ...

whts the error msg ur getting??

[JPnyc]

"Object or provider is not capable of performing requested op."

[JPnyc]

I tried static, dynamic, etc. Perhaps server-side cursor? Security set to false, even though this is not an updating function. Merely a sort. Would it be more practical to sort the grid? Can you even sort a flexgrid? I use that for the search, rather than the rs.