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.

Problem

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.

Solution

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.

Discussion

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:

1. 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)

2. Create a web service that contains the following web method in C# code: [WebMethod]
public int AddItem(string itemText)
{
using (SqlConnection conn = new SqlConnection(
"Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True"))
{
if (!String.IsNullOrEmpty(itemText))
{
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)";
cmd.Parameters.AddWithValue("@itemText", itemText);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}

return this.Context.Response.StatusCode;
}
3. Create a New Blank Form and add a Table with Title layout table to the InfoPath form.
4. Add a Combo Box control to the table.
5. Rename the Combo Box control to listItems.
6. Your Main data source should now resemble the following figure:
   
   Figure 1. Main data source of the InfoPath form.
7. Modify the table and controls to resemble the following figure:
   
   Figure 2. The InfoPath form in design mode.
8. 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.
9. Add a second data connection to Receive data from the Microsoft SQL Server database table you created.
10. Double-click on the combo-box to open its Properties dialog box.
11. Bind the combo-box to the data connection you created to Receive Data from the Microsoft SQL Server database table.
12. 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 Submit where 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: Properties where 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:

• InfoPath Basics: List control basics in InfoPath
• Programmatically add items to a drop-down list box in InfoPath 2007