Creating a SQL Server User Interface with InfoPath - Page 3

Once the form designer is displayed, the fun begins. Figure 7 shows the InfoPath Form designer after the Wizard has created the form. At the top of the form there is information for the novice InfoPath user about the presence of two views. I like to see instructions on the form where it is impossible to miss. However, this is not for the end user of the form so I have started by deleting the section and adding instructions of my own.

In addition to changing the instructions, I took the following steps:

• Deleted the Data Entry view, making the Query view the default.
• Dragged the dataFields for Region into a new "Repeating Section with Controls" section on the form.
• Added a Submit Button to submit to the database.
• Removed the RegionDescription from the query.

After a little reformatting the form is complete and I can preview it as seen in Figure 8 after the Run Query button was pressed.

Here, all the records have been retrieved. However, you can also run the query to retrieve a particular record or use the New Record button to add one. Once you are done making changes, press the Submit button and all the modified records are updated. In this case, I have added an Asia region and modified the descriptions of the other Regions so that they are more international. You can see the results in Figure 9. This is after I submitted the changes and reran the query.

InfoPath will only write a very simple query using the equal operator. If you would like to have a more complex query, for example using the LIKE operator, you will have to handle the proper events and construct your own select string. This is only one of many opportunities that you have to intercede as InfoPath processes the form.

Sharing the Form

InfoPath forms can be distributed on file shares, web sites, or even via e-mail. However, to use them with a database the recipient must have access to the database and a correct Office Database Connection (ODC) file. The easiest way to achieve this is to publish the form on a file share on your network and let users get to it from there.

The published file is has a type of XSN. It is actually a CAB file with multiple compressed files inside. Most of them are in one type of XML format or another. If you would like to see them, open the form in the designer and use the menu command File/Extract Form Files.

One of the interesting aspects of InfoPath is that the user can save the form to a file. Come back to it later and submit it then. This feature allows a limited type of offline work.

Conclusion

This article has shown how to connect an InfoPath form to SQL Server and use it to create a simple GUI update form for a simple table. Seeing how this can be done in just a few minutes leaves me very hopeful about what can be done with InfoPath. I am looking forward to the full product release and updated help files.

I am not so sure that the direct database connection is going to be usable for anything but a pretty simple application. For applications that are more complex a WebService may be a better way to go. That is the next avenue that I will investigate for connecting InfoPath to SQL Server data.

» See All Articles by Columnist Andrew Novick