DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 826992 - Last Review: February 7, 2011 - Revision: 3.0

On This Page

SUMMARY

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.

MORE INFORMATION

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:
    • ID
    • Company
    • Last_Name
    • First_Name
    • Job_Title
    • Business_Phone
  7. Click Next.
  8. Click Finish to complete the Data Connection Wizard.
  9. In the Fields pane, expand the dataFields node, and then drag the add d:Suppliers node to the box under the Run Query button. 
  10. When you are prompted, click to select Repeating Table.
  11. 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.) 
  12. Under the Developer tab, click Language.
  13. 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.


APPLIES TO
  • Microsoft Office InfoPath 2007
  • Microsoft Office InfoPath 2003
Keywords: 
kbhowto KB826992
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support