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.
Back to the top
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.
Back to the top
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
| 1. | Start InfoPath 2007. |
| 2. | In the left 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.
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. |
| 8. | In the Select Table dialog box, move to
the bottom of the list, and then select the Suppliers table. Click OK. |
| 9. | In the Data source structure list box,
make sure that only the following column headers are selected:
| • | ID | | • | Company | | • | First_Name | | • | Last_Name |
Click Next. |
| 10. | Click Design data view first, and then
click Finish to exit the Data Source Setup Wizard and to build
the default form. |
| 11. | In the Data Source task pane, double-click
dataFields. |
| 12. | Move the d:Suppliers node to the Data view
of the form. |
| 13. | When you are prompted, add d:Suppliers as
a Repeating Table. |
| 14. | On the View menu, click Manage
Views. |
| 15. | In the Views task pane, click the
Query view.
You can see the view that permits you to
start your query. |
InfoPath 2003
| 1. | Start InfoPath 2003. |
| 2. | On the File menu, click Design a
Form. |
| 3. | In the Design a Form task pane, click New from Data
Source.
The Data Source Setup Wizard starts. |
| 4. | Click Database (Microsoft SQL Server or Microsoft
Office Access only), and then click Next. |
| 5. | 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. |
| 6. | In the Select Table dialog box, move to
the bottom of the list, and then select the Suppliers table. Click OK. |
| 7. | In the Data source structure list box,
make sure that only the following column headers are selected:
| • | SupplierID | | • | ContactName | | • | ContactTitle | | • | Phone |
Click Next. |
| 8. | Click Design data view first, and then
click Finish to exit the Data Source Setup Wizard and to build
the default form. |
| 9. | 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. |
| 10. | 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. |
Back to the top
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. |
Back to the top
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.
Back to the top
Help and Support
