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 2

By Andrew Novick

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.

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