Connect InfoPath 2010 to a SQL Server 2008 table

Learn how you can create an InfoPath 2010 Filler Form that has an InfoPath 2010 database connection with which you can connect to a SQL Server 2008 database table to view and from InfoPath 2010 submit to SQL.

Problem

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

Solution

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

Discussion

You can use the Database form template in InfoPath 2010 to create InfoPath 2010 database forms, but InfoPath automatically makes the InfoPath 2010 database form a web browser form when you do. And because you cannot in InfoPath 2010 submit to SQL, since submitting data to a SQL database is not supported in InfoPath 2010 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 to SQL Server.

You can set up an InfoPath 2010 database connection to create an InfoPath 2010 database form 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 to start creating an InfoPath 2010 database connection.

    Explanation for using the Database from template in InfoPath 2010
    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. With this you have created an InfoPath 2010 database connection.

    Submitting to a database is not supported in Web browser forms in InfoPath 2010
    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.

Query fields and data fields sections on an InfoPath 2010 Database form template
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.

QueryFields and dataFields nodes in data source of an InfoPath 2010 form template
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.

InfoPath 2010 form template to view data from or add data to a SQL Server database table
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 InfoPath 2010 database connection for the 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.

InfoPath 2010 form displaying data from a SQL Server 2008 database table
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.

 
 Subscribe for updates via RSS or Email

Related InfoPath Articles:

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services