INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...Praise should be given to the Forum managers or the Tipmasters - they are what make it work - give them extra recognition!!! They are timely (prompt - unlike ACTUAL support sites) and on the ball!!!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Development Tools > Microsoft: Visual InterDev > FAQs

Microsoft: Visual InterDev FAQ

Recordset DTC's

How to Sort / Filter a Recordset

faq117-814

Posted: 11 Jun 01

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:

function _Recordset(strName)
{
  // private members
  this._strSortCols = '';
  this._strADOFilter = '';

2. And a couple of Methods to set the Sort / Filter
2.1 The Method prototype:

function _RS__Prototype()
{
  //public members
  _Recordset.prototype.setSort = _RS_setSort;
  _Recordset.prototype.setFilter = _RS_setFilter;

2.2 The Sort method implementation:

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 '';
}

2.3 And the Filter Method implementation:

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 '';
}

3. Store the Sort/Filter between server round trips...
3.1 Preserve the values

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;
}

3.2 Restore the saved settings

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;
}

4. Now to use it...

  rsMyRecordset.setSort "COLUMN1, COLUMN2, COLUMN3 DESC"

where COLUMN1 etc. are actual column names.

or

  rsMyRecordset.setFilter "COLUMN1 > 100"

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

Back to Microsoft: Visual InterDev FAQ Index
Back to Microsoft: Visual InterDev Forum

My Archive

My FAQ Archive