For those of you that have tried developing an InfoPath 2007 form that is browser-enabled will agree with me that there can be a lot of headaches associated with it. A lot of the controls and functionality of InfoPath are limited when compatibility is browser-enabled. However, there are ways around things, just can be more time consuming. That being said, there is the option of writing code to programmatically achieve some desired results.

One simple concept is populating a list box control. The item can be populated in 3 different ways:

  1. Entered manually
  2. Retrieve from the form’s data source
  3. Retrieve from an external data source

In this example, I want to explore the concept of populating a list box from an external data source. The external data source will be SQL Server database that is called by a stored procedure. The value passed into the stored procedure will be a result from a drop down box selection.

InfoPath form setup:

First, create a new blank form template. Make sure the checkbox is selected for browser-compatible. Then, drag the following controls onto the page:

  1. Drop Down Box
  2. List Box
  3. Button
1

Set each control properties as follows:

Drop Down Box
Field name: dropDown
Click OK 		Button
Action: Rules and Custom Code
Label: Go
ID: btnGo
Click OK

 List Box
Field name: listBoxSelected
click OK

Next, there needs to be some nodes added to the data source. Create a group called listBox. Create a repeating node under this called listBoxItem. Under the repeating node, create two field elements, listBoxItemName and listBoxItemValue. Your data source should look similar to the following:

2

Create Data Connection:

In SQL Server, I created a database named Creatures with two tables named Animal and AnimalDetail. We need to establish a data connection to the Animal table to populate the drop down box. Complete the following steps to establish a data connection to the Animal table.

Step 1) Go to the InfoPath menu and select Tools / Data Connections …  and select Add Table on the dialog box. Select Create a new connection to: Receive data and click Next.
3 		Step 2) Select Database from the list and click Next.

4

Step 3) Type the server name into the database and the correct permissions associated with the server and click Next.

5


Step 4) Select the Creatures database and then select the Animal  table and click Next.

6


Step 5) Call the file name Creatures Animal.odc and the friendly name Creatures Animal and click Next.

7


Step 6) Leave the default values selected for the Animal.dbo table and click Next.

8


Step 7) Leave the “Store a copy of the data in the form template” checkbox unchecked and click Next.

9


Step 8) Leave the name of the data connection to Animal and make sure the checkbox is checked for “Automatically retrieve data when form is opened” and then click Finish.
10 		 

Binding the drop down box to the data connection:

Double click the drop down box control (or right click and select Drop Down List properties). Change the List Box entries section to “Look up values from an external data source”.

Make sure the data source is pointing to the Animal data connection you just created and Select the xPath button and choose the Animal repeating node.

  • For the Value field, make sure the @animalID is selected
  • For the Display Name field, make sure the @animalType is selected
11a   11

Call stored procedure programmatically to populate list box:

13a

First off, we need to make sure the form options programmability is set to C#. Go to Tools/ Form Options and select the Programming section on the left and make sure the Form Template code language is set to C# in the drop down.

Back on the InfoPath form, double click the button control and click the Edit Form Code button. This will open Microsoft Visual Studio Tools for Applications IDE.

12

On the click event of the button (btnGo), we will start adding code to call a stored procedure to return a list of items based upon the selection in the drop down box. The first step in the process is to obtain the id of the selected value in the drop down control. To do this, we must create a XPath navigator to select the node that holds the drop down value. After we create a navigator object, we select the node value by its XPath value. To obtain the XPath value, go to the Infopath Data Source and right click the dropDown node and click Copy XPath. Then we assign the value to a string variable.

The next part in the process is to call check to see if the list box is already populated and if so, clear the values. However, we’ll come back to this method in a moment.

A connection needs to be made to the database. Insert a using statement with a connection string set for the Creatures database. Replace SERVER_NAME with the name of your server.

Create a new SQLCommand object of type StoredProcedure and pass in the GetAnimalTypes stored procedure name. Set the value of the parameter for @animalID equal to the string that was obtained from the drop down node (strAnimalId)

Execute a SQLDataReader and create a while loop to read the records

In the while loop, a new XmlDocument will need to be created. By obtaining the element names, we will populate the XmlNode values. Group will equal the listBoxItem repeating group. Field values will be associated with listBoxItemName and listBoxItemValue. The text for these nodes will be obtained from the stored procedure fields: animalName and animalDetailID.

The last step in the click event will be appending the XML document to the listbox group node in the InfoPath data source.

As mentioned earlier. When populating the list box, we need to check to make sure it’s empty and if it’s not, to clear it out. Otherwise, the values will keep being appended to the list box and the list box will grow and grow!

I created a method called ClearListBox that passes in the XPath string for the list box and the XPathNavigator object.

So, the code all put together looks like the following for the click event of btnGo:

Binding list box to form data source:

Now, after writing the code for the button control., one last thing needs to be done for the list box control. Double click the list box and select the Look up values in the form’s data source under the List Box entries section. Click the XPath selection button of the entries field and select the listBoxItem repeating node. Set the value field to the listBoxItemValue and the Display name to the listBoxItemName node. Then click OK.

    14 14a

Signing the InfoPath Form:

Last step before testing the InfoPath form is to sign the form and set the trust level. To do this, go to Tools / Form Options and click the Security and Trust category on the left. Uncheck the box that says, “Automatically determine security level (recommended)" and select the Full Trust option. Under the Form Template Signature section, check the box that says, “Sign this form certificate” and click “Create this certificate”

13

Test InfoPath Form:

You are now ready to test your form. From the Visual Studio IDE, go to the menu and select Debug /  Start Debugging or just click F5. This will open the InfoPath form and you can then select the drop down box value you want and click the Go button. Voila! the list box is populated with the values based upon the drop down box. Hope this helps some of you out. Happy Coding!

15

Full source code can be downloaded here: List Box Populate Full Source Code

Category: Applications