Creating a SQL Server User Interface with InfoPath

Tuesday Sep 2nd 2003 by Andrew Novick
Share:

When Office 2003 is released about a month from now, some of the versions will contain two new applications OneNote and InfoPath. Andy Novick shows how InfoPath can be used as a front end to a SQL Server database.

When Office 2003 is released about a month from now, some of the versions will contain two new applications OneNote and InfoPath. OneNote is a note taking application that may be interesting to those of you with tablet PCs. InfoPath is an XML based forms creation and entry application that looks very exciting to those of us who create applications that work with a lot of data on tablets and desktops.

InfoPath is one of several new applications on the cusp of delivery that enable the display and entry of data in a WYSIWYG forms based application without the need to write code. I am talking about:

  • XForms by the W3C
  • InfoPath by Microsoft
  • Acrobat 6 by Adobe

Applications that make it easy to create data entry forms aren't new. For example, Access forms and Access Data Access Pages are both pretty easy. Adobe has long had forms data entry capabilities in Acrobat. The acquisition of Canadian company Accelio, formerly Jetforms, has enabled Adobe to add an XML based infrastructure to Acrobat 6.0.

What is different today is that the new forms based applications are enabling the creation of XML data by the application instead of in a proprietary format. They are also facilitating distribution of the forms over the web and entry of data both online and offline. That is, once created, the XML can either be saved on disk for later use or be sent to one of many possible locations for consumption.

XForms is a W3C standard for Web Forms to supersede HTML based forms on multiple platforms. The multiple platform aspect is key to the design of XForms and creators have attempted to abstract the form as much as possible from the implementation. The standard has reached the "Proposed Recommendation" stage of development. You can read more about it at: http://www.w3.org/MarkUp/Forms/

What has made the surge in XML based forms possible is the evolution of various XML standards. Among them are:

  • XSLT: XML Style Sheets
  • XPath query specifications
  • XSD: XML Schema
  • XML Digital Signatures
  • Namespaces
  • Webservices: SOAP, WSDL, UDDI

These standards each make it possible to specify one aspect or another of an application in XML rather than in code. The ultimate effect is to create the data in an XML format instead of a proprietary format so that it can easily be manipulated and transferred from application to application. InfoPath uses the XML based standards and melds them with Office technology.

To get InfoPath you'll either have to purchase it separately or have a volume license agreement for the "Professional Enterprise" edition. However, I have heard a rumor that for a limited time it may be included in the "Professional Standard" edition. Keep checking Microsoft's site for more details as the October 21st ship date approaches.

One of InfoPath's strengths is flexibility in the source of data that it can read and update. The choices include XML files, Web Services and databases. That is, as long as the database is Access or SQL Server. I suspect the list of supported databases will grow in future releases.

If InfoPath works well with SQL Server, I am hoping that it will be an easy way to create a robust GUI interface for SQL Server tables. That might save hours of writing application code. It is also a possible way to display SQL Server reports without having to have a report writer. Moreover, the resulting forms share many of Office's features such as spell checking!

This article shows how InfoPath can be used as a front end to a SQL Server database. I have kept the example deliberately simple: we will design an InfoPath form and enable it to edit the Regions table in the Northwind Traders sample database in SQL Server 2000. The table is about as simple as they get with just two columns: RegionID and RegionDescription. The information in this article is based on the Office 2003 Beta 2 version of InfoPath so there may be some changes to the product when it is officially shipped.

Designing the Form

The first step in using InfoPath is to design the form using one of the form creation wizards. They are started from the Taskpad, which by default is docked on the right side of the screen. Figure 1 shows how InfoPath appears as I'm about to design a form with the "New from Data Source" wizard.

Click for larger image

Figure 1 Designing a form from a Data Source

The Data source can come from one of three choices as shown in Figure 2.


Figure 2 Selecting the type of Data Source

I selected Database and the Wizard proceeded to the Select a Database dialog (not shown). This let's you choose from among the Data Sources defined on your system or you can create a new data source from scratch. Data Sources are stored in Office Data Connection files with an ODC extension. These are HTML files with embedded XML that stores the connection information. For a SQL Server database the connection information boils down to an OLE DB (ADO) connection string. Listing 1 shows the text of the (local) Northwind.ODC file.

Once you have selected the database connection, InfoPath opens it and allows you to select from the views and tables in the database. Figure 3 shows the Select Table dialog as I pick the Region table.


Figure 3 Selecting a Primary Table for the Form

Each form has a primary table. The form can show additional tables linked by their parent/child relationships. Since this is my first attempt, I have decided to keep things simple and just display Region.

The next step is to select the columns that you want to work with from the tables selected. Figure 4 shows the dialog as I picked just the RegionID and RegionDescription columns.


Figure 4 Selecting Columns from the Database Tables

Once you are done selecting the columns, the next step in the Wizard completes the form definition and begins editing. Figure 5 shows the final dialog. It allows you to choose the view that is open for editing when the application opens. Because I do not need the data entry view, I allow editing to start in the query view.


Figure 5 Completing the Wizard

This dialog turns out to be incredibly important for the error message that it can show. Look at Figure 6. This is the same dialog as in Figure 5 but in an earlier trial run. The message in the small type within the red box tells you why InfoPath will not allow the submission to database action on a button. The red box does not appear on the screen, I added to so that you could find the message easily. In fact, I missed this error message the first few times that I tried to create the form. It was an incredibly frustrating experience. Two sources of information that I found useful while trying to resolve this problem were the microsoft.public.infopath newsgroup and the web site http://www.infopathfaq.com.


Figure 6 Selecting a View for Initial Editing

There are many reasons that InfoPath will not be able to update the database directly. When it cannot you are not totally out of luck. You can write your own handler for the submit button in Jscript or VBScript. It is not actually that difficult but since I am looking for a simple to create application, it is more than I want to do right now.



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.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved