Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 2, 2003

Creating a SQL Server User Interface with InfoPath - Page 3

By Andrew Novick

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.

Figure 7 InfoPath Forms Designer After the Wizard is Done.

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.

Figure 8 A Preview Window

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.


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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM