MOSS 2007 & MS Office InfoPath 2007

Navigation

MOSS 2007 & MS Office InfoPath 2007 | Part 02

Article Target
Using SQL Server DB to retrieve data from it using a web service and export this data to an InfoPath compatible form through an external data source then publish this form to a Sharepoint site using network publishing location.

Steps
A) Creating the Database:

Open SQL Server Enterprise Manager through : (Start > All Programs > Microsoft SQL Server > Enterprise Manager), then in Console Root folder expand the Microsoft SQL Servers then expand the SQL Server Group then expand your instance then at the Databases folder right click on it and create a new database called “TestDB” then expand the TestDB database and right click on Tables to create a new table called “Employee” this table holds three columns: (EmployeeID – EmployeeName - EmployeeAddress) as shown in this figure: Then , you’ll find three tabs: (Home , Operations , Application Management) , So , Click on the Application Management tab as shown in the figure:

B) Creating the Web service:

Open MS- visual studio .net 2005 then click File menu > New > Web Site > ASP.Net Web Service :
Then in the App_Code folder open Service.cs file then add these namespaces:
Using System. Data;
Using System.Data.SqlClient;
Then, add this web method to get all of the employees names from Employee table in the TestDB database:

[WebMethod]

public DataSet GetEmployeeNames()

{

SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TestDB");

SqlDataAdapter da = new SqlDataAdapter("SELECT Employee.EmployeeName FROM Employee", con);

DataSet ds = new DataSet();

con.Open();

da.Fill(ds, "Employee");

con.Close();

con = null;

da = null;

return ds;

}

Which shown in this figure:

Then , run this service as shown in this figure :

Then click on the GetEmployeeNames link , then invoke the webmethod as shown in the figure:

Then after invoking the webmethod , it should return All of the employees names as shown in the figure:

C) Creating the Compatible InfoPath form & Publishing:

Open Microsoft Office InfoPath 2007 then design a new blank form template which explained before in the previous article ,then add a table with title layout and add a List Box control to it in order to hold the Employees names from the TestDB database via the created web service , as shown in the figure:

Then from Tools menu > Data Connections, as shown in the figure:

Then select receive data in order to receive data via the web service as shown in the figure:

Then select the resource of your data as a web service as shown in the figure:

Then write the URL of the location of the web service you want to use as your data connection as shown in the figure:

Then you’ll find the web method which wrote in the web service which called “GetEmployeeNames” as shown in the figure:

Then store a copy of the data in the form template as shown in the figure:

Then check on the automatically retrieve data when form is opened as shown in the figure:

Then you’ll get the data connection source for the form template as shown in the figure:

Then right click on the list box and click on list box properties as shown in the figure:

Then at the list box entries select look-up values from an external data source as shown in the figure:

Then click on the entries button as shown in the figure:

Then expand the data fields till reach to the EmployeeName data filed as shown in the figure:

Then you’ll find the X-path to the EmployeeName data field in the entries textbox as shown in the figure:

Then from the preview tab click on form item to run the form as shown in the figure:

Here’s the result after running the form:

Then close the preview as shown in the figure:

Then from Tools menu > Form options >:
At the security and trust category > security level > set full trust (the form has access to files and settings on the computer, then at the form template signature > check on the sign this form template, as shown in the figure :

Then, click on File menu > Publish, as shown in the figure:

Then select to a network location, as shown in the figure:

Then write the path and the file name for the published form template, as shown in the figure:

Then remove the path from this textbox, as shown in the figure:

Then click on Publish button, as shown in the figure:

Then click on close button, as shown in the figure:

Then open the central administration which explained before in the previous article and click on the Application Management tab > InfoPath forms services > Manage form templates, as shown in the figure:

Then click on upload form templates, as shown in the figure:

Then click on the browse button to upload the published form template, as shown in the figure:

Then click on upload button as shown in the figure:

Then click on Ok button, as shown in the figure:

Then right click on the new published form template > Activate to a site collection, as shown in the figure:

Then at the activation location section, right click on the site collection drop-down list and select change site collection, as shown in the figure:

Then right click on the web application drop-down list and select change web application, as shown in the figure:

Then click on the sharepoint-80 link, as shown in the figure:

Then from the URL section select your site link, as shown in the figure:

Then click ok button, as shown in the figure:

D) Creating the Sharepoint Site Document-Library:

Use the same steps that were explained before in the previous article in order to create a sharepoint site and a document library so , here I created another document library called “WSDocLib” in order to publish the published form template to it as a content type , as shown in the figure :