How to Sort / Filter a Recordset

The recordset DTC is a 'wrapper' around the ADO recordset object - which supports sorting and filtering AFTER the recordset has been returned to you.

However, getting hold of the underlying ADO recordset in VBScript is a bit tricky. So the method that I adopted is to extend the RECORDSET.ASP library code... This FAQ assumes that you can find your way around a large JScript file!

1. Add a couple of new Properties to the Recordset object:
[tt]
function _Recordset(strName)
{
// private members
this._strSortCols = ';
this._strADOFilter = ';
[/tt]

2. And a couple of Methods to set the Sort / Filter
2.1 The Method prototype:
[tt]
function _RS__Prototype()
{
//public members
_Recordset.prototype.setSort = _RS_setSort;
_Recordset.prototype.setFilter = _RS_setFilter;
[/tt]

2.2 The Sort method implementation:
[tt]
function _RS_setSort(strSortColumns)
{
if (typeof(strSortColumns) == 'string')
{
this._strSortCols = strSortColumns;
if (this.isOpen()) {
this._rsADO.Sort = strSortColumns;
}
}
@if (@trace_warnings)
thisPage._traceWarning('Err 440: Invalid Sort Column(s) [' + strSortColumns + '].','recordset.asp',this.name + '.setSort(strSortColumns)');
@end
return ';
}
[/tt]
2.3 And the Filter Method implementation:
[tt]
function _RS_setFilter(strADOFilterCriteria)
{
if (typeof(strADOFilterCriteria) == 'string')
{
this._strADOFilter = strADOFilterCriteria;
if (this.isOpen()) {
this._rsADO.Filter = strADOFilterCriteria;
this._syncBOFandEOF();
}
}
@if (@trace_warnings)
thisPage._traceWarning('Err 440: Invalid Filter [' + strADOFilterCriteria + '].','recordset.asp',this.name + '.setFilter(strADOFilterCriteria)');
@end
return ';
}
[/tt]

3. Store the Sort/Filter between server round trips...
3.1 Preserve the values
[tt]
function _RS__preserveState()
{
if (this.maintainState)
{ // preserve state in hidden field
..other stuff here..
if (this._strSortCols != ')
{
bState = true;
state._strSortCols = this._strSortCols;
}
if (this._strADOFilter != ')
{
bState = true;
state._strADOFilter = this._strADOFilter;
}
if (bState)
return thisPage.persistState(this.name,state);
}
return false;
}
[/tt]
3.2 Restore the saved settings
[tt]
function _RS__restoreState()
{
..other suff here..
if (state._strSortCols != null)
this.setSort(state._strSortCols);
if (state._strADOFilter != null)
this.setFilter(state._strADOFilter);
}
r = true;
}
}
return r;
}
[/tt]

4. Now to use it...
[tt]
rsMyRecordset.setSort "COLUMN1, COLUMN2, COLUMN3 DESC"
[/tt]
where COLUMN1 etc. are actual column names.

or
[tt]
rsMyRecordset.setFilter "COLUMN1 > 100"
[/tt]
Note: There are some restrictions on the filter syntax (see the ADO syntax guide).

To remove the Sort / Filter - just set the Sort / Filter to an empty string.

Another Note! Visual Interdev will NOT include these new methods in its pop-up helper lists. So when you press the full-stop after the recordset name, and the list of Property and Method names pop-up, these new ones will not be included - for that you will need the source code of the DTC design-time DLL object.

Good luck I