Recordset sort question

[yangchin78]

Hi,
I want to sort a recordset on a certain field, but I want to remove any leading spaces in that field. I cannot get to the SQL statement that the recordset is based on so I was thinking of using .sort. While I can partially get it to work, I can't figure out how to remove the leading spaces. I tried rsRecordset.sort ="ltrim(Category) ASC" but this doesn't work. Is there any way to do what I want to do, without being able to change the original SQL statement. Thanks in advance.

 

[Sheco]

If you can't change the way the RS is populated, perhaps you could update the values and then use the Sort property. Of course this wont work if you have a ForwardOnly cursor if it is ReadOnly. Anyway it would be very important to disconnect the recordset by setting the ActiveConnection property to Nothing before updating anything. Then just loop through and Trim it all, update, and sort.

If the cursor/lock won't allow that, you could make a copy of the recordset either by persisting it to a file and re-inflating it as another object, or by doing something like this:
1. Create a new empty recordset object
2. Add fields by looping through the existing recordset's Fields using For Each
3. Add rows by looping through the existing Recordset using Do While Not rs.EoF -- except trim the values before inserting.
4. Set the Sort property of the new recordset.


Another way to copy it would be:
1. Extract an array using GetRows
2. Iterate through and use Trim on every array element.
3. Google for a "QuickSort" ASP script
4. Use the sorted array instead of the recordset.