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.

ADVERTISEMENTS

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:
    Main data source of the InfoPath form
    Figure 1. Main data source of the InfoPath form.
  7. Modify the table and controls to resemble the following figure:
    The InfoPath form in design mode
    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.

 
 Subscribe for updates via RSS or Email

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 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
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 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

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