Microsoft KB Archive/826992

= How to use wildcard characters in an InfoPath form query when binding to an ADO data source =

Article ID: 826992

Article Last Modified on 3/23/2007

-

APPLIES TO


 * Microsoft Office InfoPath 2007
 * Microsoft Office InfoPath 2003, Service Pack 1 (SP1)
 * Microsoft Office InfoPath 2003

-



SUMMARY
Office InfoPath is designed to make binding to Microsoft SQL Server or to Microsoft Access simple. InfoPath can automatically generate simple, direct table queries to an SQL database or to an Access database by using Microsoft ActiveX Data Objects (ADO). InfoPath then permits the results of the query to be displayed and to be edited in the form.

For more advanced queries, InfoPath makes several programming methods available to the advanced user or to the developer. These programming methods permit the advanced user or the developer to build forms that can perform a custom query with more complex SQL syntax. One common example of a custom query uses a wildcard character to find similar records that match a specific criteria.

The following steps show how to use scripting in InfoPath to perform a wildcard character search in an SQL query or in an Access query by using the ADOAdapter object.



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.

InfoPath 2007
 Start InfoPath 2007. In the left pane of the Fill Out a Form dialog box, click Design a Form Template. In the Design a Form Template window, click Blank and then click OK. On the Tools menu, click Data Connections, and then click Add. The Data Connection window opens. In the Data Connection window, click to select the Create a new connection to check box, click to select the Receive Data check box, and then click Next. Click to select the Database (Microsoft SQL Server or Microsoft Office Access only) check box, and then click Next. 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.</li> In the Select Table dialog box, move to the bottom of the list, and then select the Suppliers table. Click OK.</li> In the Data source structure list box, make sure that only the following column headers are selected: <ul> ID</li> Company</li> First_Name</li> Last_Name</li></ul>

Click Next.</li> Click Design data view first, and then click Finish to exit the Data Source Setup Wizard and to build the default form.</li> In the Data Source task pane, double-click dataFields.</li> Move the d:Suppliers node to the Data view of the form.</li> When you are prompted, add d:Suppliers as a Repeating Table.</li> On the View menu, click Manage Views.</li> In the Views task pane, click the Query view.

You can see the view that permits you to start your query.</li></ol>

InfoPath 2003
<ol> Start InfoPath 2003.</li> On the File menu, click Design a Form.</li> In the Design a Form task pane, click New from Data Source.

The Data Source Setup Wizard starts.</li> Click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.</li> <li>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.</li> <li>In the Select Table dialog box, move to the bottom of the list, and then select the Suppliers table. Click OK.</li> <li>In the Data source structure list box, make sure that only the following column headers are selected: <ul> <li>SupplierID</li> <li>ContactName</li> <li>ContactTitle</li> <li>Phone</li></ul>

Click Next.</li> <li>Click Design data view first, and then click Finish to exit the Data Source Setup Wizard and to build the default form.</li> <li>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.</li> <li>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.</li></ol>

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: <ol> <li>In Design mode, right-click Run Query, and then click Button Properties.</li> <li>Make the following changes: <ul> <li>Change the Action of the button to Script .</li> <li>Make the Label type Run Query .</li> <li>Make the Script ID value MyQuery .</li></ul>

Click Microsoft Script Editor to apply the changes and to display Script Editor.</li> <li> 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(   &quot;/dfs:myFields/dfs:queryFields/q:Suppliers&quot;); // 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(&quot;@ContactTitle&quot;).text; querySuppliers.selectSingleNode(&quot;@ContactTitle&quot;).text = &quot;&quot;; // 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(&quot;@SupplierID&quot;).text; querySuppliers.selectSingleNode(&quot;@SupplierID&quot;).text = &quot;&quot;; var strContactName = querySuppliers.selectSingleNode(&quot;@ContactName&quot;).text; querySuppliers.selectSingleNode(&quot;@ContactName&quot;).text = &quot;&quot;; var strPhone = querySuppliers.selectSingleNode(&quot;@Phone&quot;).text; querySuppliers.selectSingleNode(&quot;@Phone&quot;).text = &quot;&quot;; // Add ContactTitle to the query so  // that ContactTitle can support wildcard characters. if (strTitle != &quot;&quot;) {   if (strMySQLCommand != &quot;&quot;) strMySQLCommand = strMySQLCommand + &quot; AND &quot;; strMySQLCommand = strMySQLCommand + &quot;([Suppliers].[ContactTitle] LIKE \&quot;&quot; + strTitle + &quot;\&quot;)&quot;; } // Construct the full query string. var strSQLQuery = strOrigSQLCommand; if (strMySQLCommand != &quot;&quot;) strSQLQuery = strSQLQuery + &quot; WHERE &quot; + 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(&quot;@SupplierID&quot;).text = strSupplierID; querySuppliers.selectSingleNode(&quot;@ContactName&quot;).text = strContactName; querySuppliers.selectSingleNode(&quot;@ContactTitle&quot;).text = strTitle; querySuppliers.selectSingleNode(&quot;@Phone&quot;).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(&quot;Data Entry&quot;); } </li> <li>Save the changes, and then return to InfoPath.</li></ol>

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 in InfoPath 2003, click Preview Form. On the toolbar in InfoPath 2007, click Preview, and then click 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.

Additional query words: IP2007

Keywords: kbhowto KB826992

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.