DataConnections in InfoPath 2007- Part I

What is InfoPath?

Infopath is a very powerful Form Designer. One of the major advantages of InfoPath is, it is wholely XML-based and the form design is based on XSD. Infopath 2007 comes with MS Office 2007.

What is Form Template and Form Data?

Form Template is nothing but the Form Design. Form Data is what we store through the Form.

Data Connections

It is the connection to Data Source. They allow to do read, write operations over the Data Source. There are many types of connections in InfoPath. They are :

1. SQL Server

2. Web Service

3. XSD

4. XML

5. Data Connection Library (DCL)

6. E- mail

7. SharePoint Document Library

The objective of this topic is to explain “How to work with DataConnections in InfoPath 2007”. So, We are not going to discuss the basics of InfoPath in detail.

We are going to see here a sample in a step by step manner , which shows you the basic of Data Connection in InfoPath. Here InfoPath is the front-end and MS Sql Server 2005 is the back-end.

A sample displaying an Employee Information from the back-end

Let us see the sample in a step by step manner with back-end and fron-end flow.

Back-end (SQL Server):

We are creating a PayRoll Database for this sample. You can either create this database or use your existing database. But you need to create the following Employee table.

The code to create employee table:

CREATE TABLE [dbo].[Employee](

[EmpId] [bigint] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DOJ] [datetime] NOT NULL,

[Designation] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Department] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Salary] [money] NOT NULL,

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

[EmpId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Code to insert data into the table:

INSERT INTO [Employee]([EmpName],[DOJ],[Designation],[Department],[Salary])

VALUES ("Bill Gates","01/01/1983","CEO","Computers","12222222.0000")

Simillarly insert few records.

Front-End (InfoPath):

Step 1 : Start InfoPath

Click Start > All Programs > MS Office > MS InfoPath 2007 as show in Fig. 1

Step 2: Decide Way of Designing

You will be shown a ‘Getting Started’ Window as shown in Fig. 2.

Click on Design a Form Template.

Step 3: Select Template

Select Database , Check whether “Enable browser-compatible features only” is checked. If not then check it. Form Template Option button will also bet selected. Then Click Ok.

Step 4: Select Database

Data Connection Wizard will be shown .

Click on Select Database.

Double click on +NewSQLServerConnection

Enter the server name.

Select “Use Windows Authentication”

Click Next.

Choose PayRoll database from the combo box.

Select Employee table , then Click Next.

Click on Finish.

Select desired fields there in this screen.

Click Next

Click Finish in the next Screen.

Step 5: Design Form

Expand the queryFields in the DataSource Window.

Then Drag EmpId into the form and drop into “Drag query fields here” area.

Simillarly expand dataFields and drag remaining fields into “Drag data fields here” area.

Then the resultant form will look like in the Fig.10

Now the Form creation and data connection is over. That’s all the database form generation.

The final step is to test the Form we created above.

Step 6: Running and Testing the Form

Click Preview in the toolbar.

Enter 1 in the EmpId field.

Click Run Query.

A security screen will be shown, click Yes in it. After that the form will fetch the corresponding values for the empId “1”, as shown in the following diagram. You can test with your desired values and it will display the result.

Click Close Preview to exit.

Have fun creating forms.