How To Use Wildcard Characters in an InfoPath Form Query When Binding to an ADO Data Source (826992)

MORE INFORMATION

InfoPath exposes the QueryAdapter property on the XDocument object. The QueryAdapter property returns a reference to the data adapter object that is used for binding to the primary data source of the form. When you use an SQL database or an Access database as your data source, InfoPath returns an ADOAdapter object. The ADOAdapter object gives you the methods that you must have to get and to set information about following:

• the connection string of the adapter
• the SQL command text
• the timeout value

By using the ADOAdapter object, you can modify an SQL statement to perform more complex actions such as using wildcard characters in a query.

The information in this article applies to an SQL database and to an Access database. The following example uses the Northwind database that is included with Microsoft Office Access 2003.

Design a Query Form by Using an Access Data Source

This section describes how to design a query form in InfoPath. The form uses the Suppliers table from the Northwind database. To build a query form, follow these steps:

1. Start InfoPath. On the File menu, click Design a Form.
2. In the Design a Form task pane, click New from Data Source.
   
   The Data Source Setup Wizard starts.
3. Click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
4. Click Select Database.
   
   In the Samples folder under the Office directory, locate the Northwind.mdb database.
   
   If you have not previously used the Northwind.mdb database, you must install the database from Access 2003. To install the database, click Help, click Sample Databases, click Northwind Sample Database, and then click Open.
5. In the Select Table dialog box, move to the bottom of the list, and then select the Suppliers table. Click OK.
6. In the Data source structure list box, make sure that only the following column headers are selected:
   • SupplierID
   • ContactName
   • ContactTitle
   • Phone
   Click Next.
7. Click Design data view first, and then click Finish to exit the Data Source Setup Wizard and to build the default form.
8. In the Data Source task pane, double-click dataFields. Move the d:Suppliers node to the Data view of the form. When you are prompted, add d:Suppliers as a Repeating Table.
9. On the View menu, click Manage Views. In the Views task pane, click the Query view.
   
   You can see the view that permits you to start your query.

Change the Form to Run a Custom Query

This section describes how to search for records that have similar values, but the values do not match exactly. To search for records with similar values, but where the values do not match exactly, you can use a wildcard character string and the LIKE statement in an SQL query. The wildcard character string and the LIKE statement, when used together, can find the correct records based on the wildcard character search criteria. These records are then returned to InfoPath.

To modify the form to perform a custom query, follow these steps:

1. In Design mode, right-click Run Query, and then click Button Properties.
2. Make the following changes:
   • Change the Action of the button to Script.
   • Make the Label type Run Query.
   • Make the Script ID value MyQuery.
   Click Microsoft Script Editor to apply the changes and to display Script Editor.
3. Add the following code to the button handler.

   function MyQuery::OnClick(eventObj)
   {
     // Get the default SQL command for the form.
     var strOrigSQLCommand = XDocument.QueryAdapter.Command;
   
     // Get the query node that you want to modify.
     var querySuppliers = XDocument.DOM.selectSingleNode(
       "/dfs:myFields/dfs:queryFields/q:Suppliers");
       
     // Obtain the text that was entered for the wildcard character search, and then clear
     // the current query parameter so that InfoPath will leave the current query parameter blank.
     var strTitle = querySuppliers.selectSingleNode("@ContactTitle").text;
     querySuppliers.selectSingleNode("@ContactTitle").text = "";
     
     // Ask InfoPath to construct an SQL command that is based on all other field values.
     var strMySQLCommand = XDocument.QueryAdapter.BuildSQLFromXMLNodes(querySuppliers);
   
     // Save each of the other query items, and then clear the other query items before the next query.
     var strSupplierID = querySuppliers.selectSingleNode("@SupplierID").text;
     querySuppliers.selectSingleNode("@SupplierID").text = "";
     var strContactName = querySuppliers.selectSingleNode("@ContactName").text;
     querySuppliers.selectSingleNode("@ContactName").text = "";
     var strPhone = querySuppliers.selectSingleNode("@Phone").text;
     querySuppliers.selectSingleNode("@Phone").text = "";
    
     //  Add ContactTitle to the query so
     // that ContactTitle can support wildcard characters.
     if (strTitle != "")
     {
       if (strMySQLCommand != "")
           strMySQLCommand = strMySQLCommand + " AND ";
                 
       strMySQLCommand = strMySQLCommand + 
          "([Suppliers].[ContactTitle] LIKE \"" + strTitle + "\")";
     }
     
     // Construct the full query string.
     var strSQLQuery = strOrigSQLCommand;
     if (strMySQLCommand != "")
         strSQLQuery = strSQLQuery + " WHERE " + strMySQLCommand;
         
     // This is the query.
     //XDocument.UI.Alert(strSQLQuery);
   
     // Run the query.
     XDocument.QueryAdapter.Command = strSQLQuery;
     XDocument.Query();
     
     // Restore all the user entries to the Query fields so that the user entries will 
     // be available if you want to modify and to rerun the query.
     querySuppliers.selectSingleNode("@SupplierID").text = strSupplierID;
     querySuppliers.selectSingleNode("@ContactName").text = strContactName;
     querySuppliers.selectSingleNode("@ContactTitle").text = strTitle;
     querySuppliers.selectSingleNode("@Phone").text = strPhone;
     
     // Restore the default table command (for the next time).
     XDocument.QueryAdapter.Command = strOrigSQLCommand;
     
     // Switch to data entry view to see results.
     XDocument.View.SwitchView("Data Entry");
     
   }
   

4. Save the changes, and then return to InfoPath.

Test the Code

The code permits you to do a wildcard character search of the ContactTitle field in the Suppliers table. By providing a search query such as Sales%, all records that are returned will have contacts in a Sales position. These contacts may be a representative, a manager, or an agent, as long as their record meets the search criteria of Sales%. Contacts that are not listed in Sales are filtered out.

To verify that all records that are returned have contacts in a Sales position, follow these steps:

1. On the toolbar, click Preview Form.
2. In the ContactTitle field, type Sales%.
3. Click Run Query.

The records that are returned as a result of your custom query are all contacts in a Sales position.