Automatically type in and add a new item to a drop-down list box on an InfoPath form
Use a combo box control and a web service to automatically type in and add new items to a list, thereby updating the underlying data store of the secondary data source that the combo box is bound to.
You have a drop-down list box on your InfoPath form that is bound to secondary data source that gets its data from a SQL Server database table. If an item does not exist in the list, you would like to be able to type the item into the drop-down list box and have it automatically added to the list.
Use a combo-box control instead of a drop-down list box control to be able to enter new items into the list, add a rule to the combo-box to submit the text of the new item to a web service, which in turn saves the item to the underlying data store of the secondary data source that the combo-box control is bound to.
Since InfoPath does not offer out-of-the-box persistence (= saving) of data to secondary data sources (only to the Main data source), you will have to create a mechanism that will save the changes you make to the secondary data source back to the data store underlying the secondary data source. One way to do this is by using a web service.
You can accomplish this functionality as follows:
- Create a table called Properties in a Microsoft SQL Server database with the following fields:
Name Type ID int (identity, primary key, not null) PropertyName varchar(50) (not null)
Create a web service that contains the following web method in C# code:
public int AddItem(string itemText)
using (SqlConnection conn = new SqlConnection(
"Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True"))
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "IF NOT EXISTS(SELECT * FROM Properties WHERE PropertyName = @itemText" +
" OR CONVERT(varchar, ID) = @itemText)" +
" INSERT INTO Properties (PropertyName) VALUES (@itemText)";
- Create a New Blank Form and add a Table with Title layout table to the InfoPath form.
- Add a Combo Box control to the table.
- Rename the Combo Box control to listItems.
- Your Main data source should now resemble the following figure:
Figure 1. Main data source of the InfoPath form.
- Modify the table and controls to resemble the following figure:
Figure 2. The InfoPath form in design mode.
- Go to Tools > Data Connections... and add a data connection to Submit data to the web service you created. Select the listItems node as the Field or group to set the parameter to submit to the AddItem web method of the web service.
- Add a second data connection to Receive data from the Microsoft SQL Server database table you created.
- Double-click on the combo-box to open its Properties dialog box.
- Bind the combo-box to the data connection you created to Receive Data from the Microsoft SQL Server database table.
- Click on the Rules... button on the Properties dialog box to
add a Rule to the combo-box with an Action that says:
Submit using a data connection: Web Service Submitwhere Web Service Submit is the name of the data connection you created to submit data to the web service.
Add a second Action that says:
Query using a data connection: Propertieswhere Properties is the name of the data connection you created to receive data from the Microsoft SQL Server database table.
You should now have a fully functional InfoPath form so that when you type the text for a new item into the combo-box and move the focus away from the combo-box, the new item is permanently added to the secondary data source that has the Microsoft SQL Server database table as its underlying data store, and the list is updated to display the new item.
Related InfoPath Articles:
- Programmatically add items to a drop-down list box in InfoPath 2007
- Programmatically check whether a value exists in a drop-down list box bound to a SharePoint list
- Use a SharePoint list for a shrinking list in an InfoPath repeating table
- InfoPath Basics: List control basics in InfoPath
- InfoPath 2010 Tutorial: InfoPath combo box control
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. 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.