Introduction to data connections

Query data connections

A query data connection gets data from an external data source and stores that data in fields in the main or secondary data source of a form. You can then bind controls to those fields to display that data in the form. You can create a query data connection in the following ways:

• Design a form template that is based on a database, Web service, or settings stored in a connection library. The structure of the external data source then defines the main data source for the form.

• Modify an existing form template by adding a secondary data connection that queries a database, Web service, SharePoint list or library, or XML file.

When you design a new form template that is based on a database, Web service, or settings in a connection library, you create a query data connection that becomes the main data connection for forms that are based on that form template. You can define only one query data connection as the main data connection for a form template. If you need to query data from additional data sources, you can create secondary data connections that query data from those data sources. You will learn more about secondary data connections later in this article.

When you add a secondary data connection that queries data to your form template, you can specify whether InfoPath should use the data connection each time that the user opens the form or after specific events occur when the user fills out the form, such as clicking a button to display a list of options. To use the data connection after specific events occur, use one of the following methods:

• Insert a button control that refreshes data in the form.

• Add a rule to use the data connection when a condition on the form exists.

• Write custom code for specific events.

Submit data connections

When users submit a form, data from that form is sent through a submit data connection to an external data source. You can configure that form template to allow users to send data to the following types of external data sources:

• Microsoft Office Access database

• Microsoft SQL Server database

• Web service

You can also add secondary data connections that submit data in the following ways:

• Submit form data to a document library on a server running Microsoft Windows SharePoint Services.

• Submit form data in an e-mail message.

• Submit form data to an application on a Web server.

You create a submit data connection in the following ways:

• Design a new form template that is based on a database, Web service, or settings stored in a connection library on a server running Office SharePoint Server 2007. The structure of the external data source then defines the main data source for the form.

• Modify an existing form template by adding a secondary data connection, and then configure the form template to allow users to submit their forms through this secondary data connection.

  Tip: When you design a form template that is based on an Office Access or SQL Server database, InfoPath automatically configures a submit data connection if the database meets certain requirements. Find links to more information about those requirements in the See Also section. You can choose to disable the Submit feature if you want your users to submit their completed forms by using another type of submit data connection.

In most cases, you will configure one submit data connection as the main submit action for your form template. However, you can design your form template so that your users can submit their completed forms to multiple locations by using rules or custom code. For example, you can configure your form template to allow your users to submit their completed forms to a Web service as well as through an e-mail message. Typically, you configure the form template to submit all of the data in the form. However, if you use custom code to submit the form data or you are configuring the form template to allow the form to submit data to a Web service, you can submit a portion of the form's data.

Query and submit data connections

When you design a form template that is based on a database or a Web service, you can create a data connection that can get data from and submit data to the database or Web service. If you are designing a form template that is based on a database, the data connection can get or submit data. However, in order for forms based on this form template to submit data to the database, the following requirements must be met:

• You are not designing a browser-compatible form template.

• All of the tables in the main data connection must contain a primary key, unique constraint, or unique index.

• All of the tables in the query must have a simple, hierarchical relationship.

• None of the data fields in the main data source of the form can store a large binary data type.

You can learn more about these requirements in Considerations for connecting to external data sources later in this article.

A data connection that can get data from or submit data to a Web service is just like a query data connection and a submit data connection combined into a single data connection.

Top of Page

Microsoft Office Access or SQL Server database

InfoPath supports data connections to Access databases and SQL Server databases. To connect to another kind of database, you can use a Web service that works with that database.

A form can query data from a database through either a main or a secondary data connection, and you can select any number of database tables for a connection. The first table that you select is the primary table. Any table that you subsequently select must contain a field that is related to the primary table. InfoPath attempts to set this relationship by matching the field names in the two tables. You can choose to use this relationship or add your own table relationship when you create the data connection.

A form can submit data to a database through the form's main data connection if the form template that the form is based on and the database meet the following requirements:

• The form template is not a browser-compatible form template    InfoPath will not create a submit data connection in the main data connection if you are designing a browser-compatible form template. To allow users to submit data in a form that is based on a browser-compatible form template, use a Web service that works with the database.

• The left table in each pair of related tables in the main data source contains a primary key    At least one of the relationships for every pair of related tables must include a primary key from the left table.

• None of the data fields in the main data source of the form store a large binary data type    InfoPath will disable the submit data connection if the query includes fields that can store a large binary data type, such as pictures, images, OLE objects, file attachments, the Office Access memo data type, or the SQL Text data type.

If the form template and the database meet all of these requirements, InfoPath creates the submit data connection and configures the form template to allow users to submit their forms. You can disable the submit data connection in the main data connection if you want users to submit their form data through a different data connection.

When you design a form template that is based on a database, InfoPath creates a main data source that contains query fields and data fields that match the way that data is stored in the database. InfoPath also adds Run Query and New Record buttons to the default view of the form template. When a user clicks the Run Query button, InfoPath sends a query with the data in the query field to the database. The New Record button clears any data currently in the form and sets controls that are bound to the data fields to the default value of the data field. When a user submits data to the database, any changes to the data in the data fields change the corresponding records in the database. If a user deletes data from a data field, the corresponding record is deleted from the database. Finally, any data that has been added to the data fields updates the corresponding fields or adds the corresponding records to the database.

If you want to connect a form to a database, and the main data connection has already been created for the form, you can add a secondary data connection that queries the database. When you add a secondary data connection, InfoPath creates a secondary data source with query fields, data fields, and groups that match fields in the database tables. You can select multiple database tables and establish table relationships just as you would for the main data connection.

When you create a secondary data connection that queries data, you can configure the connection to send a query each time the user opens the form, or you can do one of the following to send the query:

• Add a button that users can click to refresh the data from the data connection.

• Create a rule that sends the query by using the data connection.

• Write custom code that sends the query when it is run.

Web service

A form can query or submit data to a Web service through either a main or a secondary data connection. InfoPath adheres to the following standards for connections to Web services:

• Simple Object Access Protocol (SOAP)    SOAP is the communication protocol that defines the XML messages that are used to communicate with the Web service.

• Web Services Description Language (WSDL)    WSDL is the XML Schema standard that is used to describe the location, communication protocols, and interfaces to the Web service. InfoPath can consume only document-literal style Web services.

• Universal Description Discovery and Integration (UDDI)    UDDI is the directory service that describes the Web services offered by a business.

When you design a new form template that is based on a Web service, InfoPath creates a main data connection to the Web service and then creates a main data source that contains query fields, data fields, and groups that match the XML Schema of the Web service.

If you configure the form template to use another operation in the same Web service or you want to use a different Web service altogether, you can add secondary data connections to the form template. When you add a secondary data connection that queries data, InfoPath creates a secondary data source with fields and groups that match the schema of the Web service. If you add a secondary data connection that submits data, you can configure the connection to send all or only some of the data in the form, depending on the parameters in the Web service.

When you create either a main or secondary data connection to a Web service, you can specify whether the connection queries data only, submits data only, or both queries and submits data. If the connection queries data, InfoPath adds the Run Query button to the form template. When a user clicks the Run Query button, InfoPath sends a query with the data in the query fields to the Web service. If the connection submits data, InfoPath enables the Submit feature for the form. When you configure a submit data connection, InfoPath determines what data is required by the Web service. Based on that information, you can specify what fields in the form template should submit their data to the Web service.

Document library or a list on a SharePoint site

A form can query data through a secondary data connection to a document library or a list on a server running Windows SharePoint Services. A form can also submit data to a document library on a server. You cannot connect to a library or list through a main data connection.

If you create a secondary data connection that queries data from a document library or list, InfoPath creates a secondary data source for the form that matches the columns in the document library or list.

If you create a secondary data connection that submits data to a document library and you want this submit data connection to be the main submit action for the form template, you must enable the Submit feature for the form template and associate it with the data connection by using the Submit Options dialog box (Tools menu, Submit Options command). By default, all of the data in the form is submitted through the submit data connection. If you want to submit only some of the data, you can do so by writing custom code for the form template.

XML file

A form can query data through a secondary data connection to an XML file. For example, you can save a Microsoft Office Excel worksheet as an XML file and then use a secondary data connection to query and display the data from that file in the form. You can also use a secondary data connection to query data from an XML file in order to retrieve data from an application on a Web server that returns XML, such as a Microsoft ASP.NET application, a Common Gateway Interface (CGI) script, or an application that uses the Internet Server Application Programming Interface (ISAPI).

Application on a Web server

You can create a secondary data connection that submits data to an application on a Web server, such as an ASP.NET application, a CGI script, or an application that uses ISAPI. To submit data to an application on a Web server, you must configure the form template to allow users to submit their form data and configure the submit options for the data connection by selecting Web server (HTTP) in the Submit Options dialog box (Tools menu, Submit Options command).

Connection settings in a data connection library

If several forms use the same or similar data connections, consider storing the settings for each data connection in a data connection file in a data connection library on a server running Office SharePoint Server 2007. A data connection file is an XML file that contains connection information for a single external data source and has an .xml or .udcx file name extension. When a user opens a form that connects to a data connection file, InfoPath uses the settings in that file to connect to the external data source. Advantages of using data connection files include the following:

• Multiple forms can use the same data connection file, so there is no need to create the same data connection from scratch for each form.

• If the location or connection settings for an external data source change, you need to update only the data connection file, not each form template.

• The data connection file can contain alternative authentication information that can be used by the server when a user fills out a form by using a browser.

• Forms that are filled out in a browser without a full trust security level can connect to a computer in another domain if all of the data connections in the form use data connection files.

To connect a form to a data connection file, the file must be stored in a data connection library on a server running Office SharePoint Server 2007 and it must follow the Universal Data Connection (UDC) version 2.0 file format.

Top of Page