PRB: Using Wildcards in Filter of Data Form Wizard Created Form (175830)


The information in this article applies to:

  • Microsoft Visual InterDev 1.0
  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1 SP2
This article was previously published under Q175830

SYMPTOMS

The following error occurs when attempting to set filter criteria containing a wild card character at the beginning of the string:
Source: DataForm
Error Number: 20001
Description: Wildcard characters * and % can only be used at the end of the criteria

CAUSE

The DataForm Wizard produces code that uses the Recordset's Filter property. According to the ActiveX Data Objects (ADO) documentation on the Filter property (From Visual InterDev's InfoView select ADO Reference, ADO Objects, and then Recordset Object), "Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string."

RESOLUTION

In order to implement a solution that allows wild card characters at the beginning of filter criteria, you must make major modifications to the code produced by the DataForm Wizard.
  1. Convert the DataForm Wizard generated code from a "Filter" model to a "Requery" model.
    Make sure your code works correctly before moving on.
  2. Search for "Filter = Session" and comment out the following line in xxxform.asp:
          'rsxxx.Filter = Session("rsxxx_Filter")
  3. Comment out the following lines in the "PrepFilterItem" function of xxxaction.asp:
          ' Check for leading wildcards
      'If Left(strValue, 1) = "*" Or Left(strValue, 1) = "%" Then
      '   RaiseError errInvalidPrefix, strFieldName
      'End If
  4. Search for "Filter = Session" and comment out the following line in xxxlist.asp: 'rsxxx.Filter = Session("rsxxx_Filter")
  5. If your filter uses multiple criteria -- like "field=0 and field2 like %xx%" -- you will have to make the following change in the last line of the "PrepFilterItem" function in xxxAction.asp:

    From 
          PrepFilterItem = "[" & strFieldName & "]" & " " & strOperator & " " &
      strDelimiter &  strValue & strDelimiter
    To:
          PrepFilterItem = "(" & strFieldName & " " & strOperator & " " &
      strDelimiter & strValue &  strDelimiter &")"

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Enter %xxx% into one of the text boxes in the Filter page.
  2. Click Apply.
The error described in the "Symptoms" section occurs.

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

http://support.microsoft.com/search/default.aspx?qu=vinterdev

Modification Type:MajorLast Reviewed:5/2/2006
Keywords:kbDatabase kberrmsg kbprb kbScript kbwizard KB175830
©2004 Microsoft Corporation. All rights reserved.