PRB: Using Wildcards in Filter of Data Form Wizard Created Form

ID: Q175830

The information in this article applies to:

Microsoft Visual InterDev, version 1.0
ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

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.

Convert the DataForm Wizard generated code from a "Filter" model to a "Requery" model.

Make sure your code works correctly before moving on.

Search for "Filter = Session" and comment out the following line in xxxform.asp:
```
      'rsxxx.Filter = Session("rsxxx_Filter") 
```

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

Search for "Filter = Session" and comment out the following line in xxxlist.asp: 'rsxxx.Filter = Session("rsxxx_Filter")

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

Enter %xxx% into one of the text boxes in the Filter page.

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/support/vinterdev/

Additional query words:

Keywords : kberrmsg kbwizard kbADO kbASP kbDatabase kbScript kbVisID kbVisID100 kbGrpASP kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.0,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb