DataConnections in InfoPath 2007- Part I

This article is for those who know InfoPath 2007 as a beginer. Those who don’t know InfoPath can also understand it with little digging.

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.

By Sakshi a   Popularity  (4749 Views)