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

Microsoft Office InfoPath makes 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). Then, InfoPath enables the results of the query to be displayed and�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 enable the advanced user or the developer to build forms that can perform a custom query that uses more complex SQL syntax. One example of a common custom query uses a wildcard character to find records that partly match the specified criteria.

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

InfoPath lets you cast a query data connection as an�AdoQueryConnection object. This gives you methods to retrieve and to set information about the following items:

• The connection string�
• The SQL command text
• The time-out value

By using the AdoQueryConnection�object, you can change an SQL statement to perform more complex operations, such as a query that uses wildcard characters.�

The information in this article applies to SQL databases and to Access databases. The following examples use the Northwind database that is included with Microsoft Office Access 2007 and with Microsoft Office Access 2010.�

Create the database

If you have not yet used the Northwind sample database from your version of InfoPath, create the database locally. To do this, follow these steps:�

1. Start Microsoft Office Access.
2. In the�Available Templates�section, select�Sample templates.
3. Select the Northwind database, and then click�Create.

Design a form that queries the database

This section describes how to design a query form in InfoPath. This example uses the Suppliers table from the Northwind database. To build a form that queries this table, follow the steps that are provided for your version of InfoPath.

InfoPath 2010

Create the form template, and then set the form template code language to C#. To do this, follow these steps:

1. Start InfoPath Designer 2010.
2. In the�Advanced Form Templates�section, select�Database, and then click Design Form.
3. In the Data Connection Wizard, click Select Database.
4. Locate�the folder where you saved the Northwind database, select�the file Northwind.accdb, and then click Open.
5. In the Select Table window, scroll to the bottom of the list, select the Suppliers table, and then click OK.
6. Make sure that only the following fields are selected in the Suppliers table:
7. • ID
   • Company
   • Last_Name
   • First_Name
   • Job_Title
   • Business_Phone
8. Click Next.
9. Click Finish�to complete the Data Connection Wizard.
10. In the Fields�pane, expand the�dataFields�node, and then drag the add�d:Suppliers�node to the�box under the�Run Query�button.�
11. When you are prompted, click�to select Repeating Table.
12. In the�Fields�pane, expand the�queryFields�node, and then drag the add�q:Suppliers node to the box under the�New Record�button. (This automatically adds a Section that has controls.)�
13. Under the�Developer�tab, click�Language.
14. In the�Programming language�section, under�Form template code language, select C#, and then click�OK.

InfoPath 2007

Create the form template. To do this, follow these steps:

1. Start InfoPath 2007.
2. In the navigation pane of the�Fill Out a Form dialog box, click Design a Form Template.
3. In the Design a Form Template window, click Blank, and then click OK.
4. On the Tools menu, click Data Connections, and then click Add. The Data Connection window opens.
5. 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.
6. Click to select the Database (Microsoft SQL Server or Microsoft Office Access only) check box, and then click Next.
7. Click Select Database.
8. Locate the folder where you saved the Northwind database, select the folder, and then click Open.
9. In the Select Table�dialog box, scroll to the bottom of the list, select the�Suppliers�table, and then click�OK.
10. In the Data source structure�list, make sure that only the following column headers are selected:�
    • ID
    • Company
    • First_Name
    • Last_Name
    • Job_Title
    • Business_Phone
11. Click�Next.
12. Click Design data view first, and then click Finish to exit the Data Source Setup Wizard and to build the default form.
13. In the Data Source task pane, double-click dataFields.
14. Move the d:Suppliers node to the Data view of the form.
15. When you are prompted, add d:Suppliers as a Repeating Table.
16. On the View menu, click Manage Views.
17. In the Views task pane, click the Query view.
18. On the Tools menu, click Form Options.
19. In the Category list, click Programming.
20. In the Form template code language box, click C#, and then click OK.
    
    

Change the form to run a custom query

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

To change the form to run a custom query of the�Job Title�field in InfoPath 2010 or in InfoPath 2007, follow these steps: �

1. In Design mode, right-click the Run Query button, and then click Button Properties.
2. Make the following changes:
   • Change the Action of the button to�Rules and Custom Code
   • Change the Label�to�Run Query
   • Change the ID�to�btnQuery
3. Click the Edit Form Code button to apply the changes, and then start the Visual Studio Tools for Applications editor. It will open the btnQuery "Clicked" event handler.
4. Replace the comment "// Write your code here" with the following code:

   //Create an XPathNavigator object for the main data source
   XPathNavigator xnMain = this.MainDataSource.CreateNavigator();
    
   //Create an AdoQueryConnection from the main data source by "casting" the default
   //data connection to an "AdoQueryConnection"
   AdoQueryConnection cn = (AdoQueryConnection)this.DataConnections["Main connection"];
    
   //Obtain the default SQL command for the form.
   string strOrigSQLCommand = cn.Command.ToString();
    
    
    
   // Obtain the query node that you want to change.
   XPathNavigator xnSuppliersQuery = xnMain.SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Suppliers", this.NamespaceManager);
    
   //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.
   string strJobTitle = xnSuppliersQuery.SelectSingleNode("@Job_Title", this.NamespaceManager).Value;
   xnSuppliersQuery.SelectSingleNode("@Job_Title", this.NamespaceManager).SetValue(string.Empty);
    
   //Have InfoPath construct an SQL command that is based on all other field values.
   string strMySQLCommand = cn.BuildSqlFromXmlNodes(xnSuppliersQuery);
    
   //Save the other query items, and then clear the other query items before the next query.
   string strSupplierID = xnSuppliersQuery.SelectSingleNode("@ID", this.NamespaceManager).Value;
   xnSuppliersQuery.SelectSingleNode("@ID", this.NamespaceManager).SetValue(string.Empty);
   string strCompany = xnSuppliersQuery.SelectSingleNode("@Company", this.NamespaceManager).Value;
   xnSuppliersQuery.SelectSingleNode("@Company", this.NamespaceManager).SetValue(string.Empty);
   string strLastName = xnSuppliersQuery.SelectSingleNode("@Last_Name", this.NamespaceManager).Value;
   xnSuppliersQuery.SelectSingleNode("@Last_Name", this.NamespaceManager).SetValue(string.Empty);
   string strFirstName = xnSuppliersQuery.SelectSingleNode("@First_Name", this.NamespaceManager).Value;
   xnSuppliersQuery.SelectSingleNode("@First_Name", this.NamespaceManager).SetValue(string.Empty);
   string strBusPhone = xnSuppliersQuery.SelectSingleNode("@Business_Phone", this.NamespaceManager).Value;
   xnSuppliersQuery.SelectSingleNode("@Business_Phone", this.NamespaceManager).SetValue(string.Empty);
    
   //Add Job_Title to the query so that Job_Title can support wildcard characters.
   if (strJobTitle != string.Empty)
   {
          if (strMySQLCommand != string.Empty)
                 strMySQLCommand += " AND ";
    
          //Check whether the user entered the wildcard character (%) as part of the title.
   if(strJobTitle.Contains("%"))
                strMySQLCommand += "[Job Title] LIKE '" + strJobTitle + "'";
          else
                strMySQLCommand += "[Job Title] LIKE '" + strJobTitle + "%'";               
   }
    
   //Construct the full query string.
   string strSQLQuery = strOrigSQLCommand;
   if (strMySQLCommand != string.Empty)
   strSQLQuery += " WHERE " + strMySQLCommand;
    
   //Set the command and run the query.
   cn.Command = strSQLQuery;
   cn.Execute();
    
   //Restore all the user entries to the Query fields so that the user entries will 
   //be available if you want to change and to rerun the query.
   xnSuppliersQuery.SelectSingleNode("@ID", this.NamespaceManager).SetValue(strSupplierID);
   xnSuppliersQuery.SelectSingleNode("@Company", this.NamespaceManager).SetValue(strCompany);
   xnSuppliersQuery.SelectSingleNode("@Last_Name", this.NamespaceManager).SetValue(strLastName);
   xnSuppliersQuery.SelectSingleNode("@First_Name", this.NamespaceManager).SetValue(strFirstName);
   xnSuppliersQuery.SelectSingleNode("@Job_Title", this.NamespaceManager).SetValue(strJobTitle);
   xnSuppliersQuery.SelectSingleNode("@Business_Phone", this.NamespaceManager).SetValue(strBusPhone);
    
   //Restore the default table command (for the next time).
   cn.Command = strOrigSQLCommand;
    
   //Clean up
   xnMain = null;
   cn = null;
   xnSuppliersQuery = null;

5. Build the project by clicking the Build�menu item and then clicking Build�project name.
6. Save the changes, and then return to InfoPath.

Test the code

The sample code that is provided in the previous procedure lets you�do a wildcard character search of the�Job Title 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 do not have "Sales" in Job Title field are filtered out.


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

1. For InfoPath 2010:�On the�Home�tab on the Ribbon, click�Preview.
   For InfoPath 2007:�On the toolbar, click Preview, and then click�Form.
2. In the Job Title query field, type Sales%.
3. Click Run Query.

The records that are returned by your custom query are all contacts in a Sales position.