Create and Design InfoPath Form and host on SharePoint – Part 2
In part 1 of the article we have discussed how to create and design the InfoPath form. And we see the detailed steps how to implement client side validation and rule into the InfoPath form.
In this article we will focus on how to integrate data base connection with InfoPath form and make them alive.
How to implement DataBase connection with InfoPath:h2>
To connect with data base, you first need to build up the database connectivity. To do so, click on Tools > Data Connections. Connection info screen will be open up. In this screen all existing database connection will be listing.
InfoPath Dataconnection Form
To add a new connection: Click on Add. Here you have to choose the way you want to connect data. There are two ways, either receive or submit to connect with database. InfoPath form has been designed to connect with database through HTTP stateless mode, and no rich set of ADO.NET mechanism we can use to keep and maintain data connection state in the application.
InfoPath DataConnection wizard
So, in plain there is two ways you can connect data, receive and submit. You will create retrieve connection mode when you want to display form data in the InfoPath screen and those data will basically pull in from the associated table from the database.
Submit data connection you will use, when push the submit/post to the associated column in a table.
Let’s click on Receive data and here you can see the option where from you can retrieve data through XML file, SQL database, web service or from SharePoint list library. In most of the cases it would be SQL Database, or web service.
Data Retrive Way
Select Database option and click next. The next screen will be to select the data source.
Dataconnection wizard in InfoPath
Next, click on Select Database.. and click on New Source button. Very simple UI will be appeared. Here the data connectivity is very simple, and most of us had done this before while programming in VB or .NET.
DataConnection in InfoPath
Next couples of screens are very self explanatory, and I’m not going to explain what you need to feed up there.
Connect Database In InfoPath
Once you put the required data information, the following screen will be appeared. Select any table from the list and click next. The only important note is data connection here based on datable. You cannot choose more than one table here.
DataBase Table Connection in InfoPath
Once you select the table and click next, the Data Connection ODC file is going to be created, and using this connection you will have option to retrieve the table information from the mentioned database and you can map the information data into InfoPath control field (i.e. text box, combo list etc). Click Finish to save this file and it will be save on your My Documents\My Data Sources folder.
Save DataConnection File in InfoPath
To check the ODC file, you can use NOTEPAD to open the ODC file to review J
The file will be simple like XML file and data base connection string is inside the XML TAG odc:ConnectionString.
<html>
<head>
<meta http-equiv=Content-Type content=”text/x-ms-odc; charset=utf-8″>
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content=TEST>
<meta name=Schema content=dbo>
<meta name=Table content=”Analyst_Master”>
<xml id=docprops></xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc=”urn:schemas-microsoft-com:office:odc” xmlns=”http://www.w3.org/TR/REC-html40″>
<odc:Connection odc:Type=”OLEDB”>
<odc:ConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=dwssdb1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=HIMADRISH;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=TEST</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType> <odc:CommandText>"GDG"."dbo"."Analyst_Master"</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
Now, if you click on the Data Source link in the right side, you will see the table column fields over there.
InfoPath form with DataConnection
We will discuss more briefly on how to map table filed with InfoPath control, and build up the InfoPath form completely with Database interaction in Part 3.
InfoPath Dataconnection Form
InfoPath DataConnection wizard
Data Retrive Way
Dataconnection wizard in InfoPath
DataConnection in InfoPath
Connect Database In InfoPath
DataBase Table Connection in InfoPath
Save DataConnection File in InfoPath
<head>
<meta http-equiv=Content-Type content=”text/x-ms-odc; charset=utf-8″>
<meta name=ProgId content=ODC.Table>
</odc:OfficeDataConnection>
</xml>
InfoPath form with DataConnection
Absolutely with you it agree. It is good idea. It is ready to support you.