Connect InfoPath 2010 to a SQL Server 2008 table

Learn how you can create an InfoPath 2010 Filler Form to connect to a SQL Server 2008 database table to view and submit data.

Problem

You have a SQL Server 2008 database table and you would like to use InfoPath 2010 to create an interface to be able to view and add data to the database table.

Solution

Create an InfoPath 2010 form template that is based on a SQL Server 2008 database table.

Discussion

When you use the Database form template in InfoPath 2010, InfoPath automatically makes it a web browser form template. And because submitting data to a database is not supported in InfoPath web forms, you'll have to manually change settings on the form template after you've created it, so that you can use the InfoPath Form Filler application to be able to not only retrieve data from, but also submit data to the SQL Server 2008 database table.

You can create an InfoPath 2010 Database form template as follows:

1. In InfoPath Designer 2010, click the File tab, and then click New.

2. Under the Advanced Form Templates section, click Database. At the right, you'll see a brief explanation of what you can do with a Database form template with a Design Form button below it. Click Design Form.

   
   Figure 1. Explanation for using the Database form template in InfoPath 2010.

3. On the Data Connection Wizard, click Select Database.
4. On the Select Data Source dialog box, click New Source.
5. On the Data Connection Wizard, select Microsoft SQL Server and click Next.
6. On the Data Connection Wizard, enter the server name, enter appropriate log on credentials, and click Next.
7. On the Data Connection Wizard, select the database that contains the database table you want to connect to, select the table from the database tables list, and click Finish.
8. On the Data Connection Wizard, ensure the database table fields you want to use are selected, and click Next.

9. On the final screen of the Data Connection Wizard, you'll see a message saying "Submitting to a database is not supported in Web browser forms". Ignore this message for now, because we'll correct this later on. Click Finish.

   
   Figure 2. Submitting to a database is not supported in Web browser forms in InfoPath 2010.

Once the form template has been created for you, there will be a query fields and data fields section on it.

Figure 3. Query fields and data fields sections on an InfoPath 2010 Database form template.

You'll also see queryFields and dataFields nodes in the data source of the InfoPath form on the Fields pane.

Figure 4. QueryFields and dataFields nodes in data source of InfoPath 2010 form template.

For this InfoPath solution, we'll only use the data fields, so drag the repeating node under the dataFields node in the Fields pane to the data fields section on the form, drop it, and select Repeating Section with Controls from the context menu that appears. After you've done this, you can add layout tables and apply a theme to the page if you want to apply any InfoPath 2010 styles to the form.

Figure 5. InfoPath 2010 form template to view data from or add data to a SQL Server database table

Now if you click Preview on the Home tab of the Ribbon and then click the Run Query button on the InfoPath form, you should see data from your SQL Server 2008 database appear in repeating sections. Click Close Preview when you're done.

To make the form submit newly inserted or updated data to the database table:

1. Click the File tab and then Form Options.
2. On the Form Options dialog box, select Compatibility, change the Form type from Web Browser Form to InfoPath Filler Form, and click OK.
3. Click the Data tab and then click Data Connections under the Get External Data section on the Ribbon.
4. On the Data Connections dialog box, select the data connection for the InfoPath form template, and click Modify.
5. On the Data Connection Wizard, click Next.
6. On the last screen of the Data Connection Wizard, select the Enable submit for this connection check box, and click Finish. Note: Your database table must have a primary key set on it to be able to submit data to it using an InfoPath form. If the database table does not have a primary key, then the Enable submit for this connection check box will be disabled.
7. On the Data Connections dialog box, click Close.

To create a button to save database table records:

1. Place the mouse cursor behind the New Record button.
2. Click the Home tab on the Ribbon.
3. In the Controls section on the Ribbon, click Button.
4. Click the Properties tab.
5. Under the Button section on the Ribbon, change the Action of the button to Submit.
6. Under the Button section on the Ribbon, change the Label of the button by typing in the text Save.
7. Under the Button section on the Ribbon, click Submit Actions.
8. On the Submit Options dialog box, click Advanced, and then select Leave the form open in the After submit drop-down list box.
9. Click OK to close the Submit Options dialog box.

Click the Home tab and then click Preview.

Figure 6. InfoPath 2010 form displaying data from a SQL Server 2008 database table.

You should now be able to click New Record, enter data for a new record in the data fields section, click Save, and have InfoPath 2010 store the new record in the SQL Server 2008 database table. When you click Run Query, the new record you just added should appear as a new repeating section on the InfoPath form.

Related InfoPath Articles:

• InfoPath does not support the specified database. The database must be a Microsoft SQL Server
• Use an Access table as a secondary data source in a browser-compatible InfoPath form
• Using a Receive data connection in InfoPath to execute an SQL Server stored procedure