Designing a browser-enabled InfoPath form that can store data in a database - Part2

In the last post we have set up the DB that will receive data from our InfoPath forms. Now we will create the InfoPath form.

Creating the form

Now we’re left with the easiest part: creating our InfoPath form.

Since I am using a SQL Server DB I won’t need a web service to retrieve data as InfoPath knows already how to do this with a SQL Server DB, but if you will be using other Database types, you will have to create another webmethod in the endpoint definition in order to map a web service name to a stored procedure.

Start InfoPath 2007 and choose “Design a Form Template…” from the “Design a form” options.

Choose to design a new form template based on “Blank”. This will just create a form that is empty.

Choose Tools->Forms Options and in the “Compatibility” section select “Design a form template that can be opened in a browser or InfoPath, uncheck “Hide errors…” and enter the URL of your SharePoint server (https://servername:portnumber).

In the “Security and Trust” section, uncheck “Automatically detect security level” and choose “Domain”. If the DB to which you will be connecting isn’t a member server of the domain, then you will have to set the security level to “Full Trust” and you will have to sign the form.

Now on to designing the content of the form:

Create a section named ShopIDgroup that contains a table with title (Design Tasks -> Layout-> Table with Title – see below)

Then add a table with three columns and one row: make the table containing a text box control named shopID and a button as shown:

Double click on the button and select Rules->Add->“Add Action”: in the data connection section select “query using a data connection” then “add”. The data connection wizard will start: select “create a new connection to:” then “receive data” then click “next”; at this point select “database” if you have a SQL server DB (if you don’t have a SQL Server DB here you will have to select “web service” and then you will be prompted to connect to the server that exposes the web services and select your webservice) and click “next”. Now click on “select database…” and then in “my Data Sources” select “+newSQLServerConnection.odc”, this will start a data connection wizard where you will have to insert the name of the SQL server on which your db resides and select the database and table to which you are willing to connect (in our case the database name is InfoCityHall and the table is “Shops” (see below).

Click next and Ok you will return to the InfoPath wizard. Make sure you have selected all the columns of the Shop table and then click Next, Next and Finish. Then click OK till all the dialog boxes will close.

Now that we have set up the connection we need to store it in a Data Connection Library in SharePoint (this is mandatory for browser enabled form to access external data).

In the Data Source section click on Manage Data Connections, select the "Shops" data source and click on the Convert button, insert the URL of your data connection library (if you haven’t created one so far, create one in the same site collection as the one where you will store the forms) – the URL must include the udcx file name and make the data connection file relative to site collection and click OK.

Now delete the Shops connection and create another one as follows: in the data connections wizard, select Search for connections on SharePoint, Next. Select your SharePoint site and select the data connection (in our case Shops.udcx) click Next, Next (unselect automatically retrieve data when form is opened) and Finish and close the data connections window.

Now back to our form design: add another section named ShopData: this section will expose the info about the Shop that has been retrieved from the ShopID above.

Insert a table with two columns and three rows as follows:

shopName, shopAddr and shopOwner are all textboxes.

For each one of those controls: go to the control Properties and as the value of the control select the relevant column from the Shops table as follows: click the fx button, Insert a Field or Group, Data Source: Shops(secondary), select the relevant column and click on Filter Data then click Add and set the filter to ID is equal to shopID (from the main data source).

Repeat this for each one of these controls.

We will finish designing the form in the next blog post.