Featured Database Articles

Creating a SQL Server User Interface with InfoPath - Page 2

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.

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.