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.

Comments

• Anonymous
  January 01, 2003
  I wrote the post based on a project I actually did, so yes I have tried my post. The convert button is disabled if you have already converted the data source to a udcx file. Just try to recreate the data source connection with the database and then you should see the Convert button is enabled.

• Anonymous
  January 01, 2003
  Mindblown: you could modify your webservice so that the stored procedure updates more than one table.

• Anonymous
  January 01, 2003
  Perry The connection you use is exactly the same connection but this time we are using the one we have just saved on sharepoint (instead of the one local to the infopath form) so that now the connection is managed centrally: if something changes to the DB and the data connection is modified accordingly, as long as the same interface is used and the name of the data connection is preserved, our form will still work.

• Anonymous
  January 01, 2003
  Great post, explained really well and I could really understand. Thank you.

• Anonymous
  May 21, 2008
  Hey! did you ever tried your post? In my case the "Convert" button is disabled after creating the data connection to SQL server. If I use a SharePoint list for receiving data, the convert button is enabled. Unfortunally my informations are stored in SQL server - so why is the button "Convert" disabled?

• Anonymous
  January 08, 2009
  Great Post, got the example to work perfectly. Looking for a solution to be able to submit to several tables in one go. Any suggestions please?

• Anonymous
  January 14, 2009
  When you delete the data connection for the "Retrieve Shop Data"  Button , what connection do you use to retrieve the data. I am assuming when you input the shop id and click Retrieve the name/address/pwner will come up.

• Anonymous
  April 14, 2009
  I followed all your steps and can't seem to get it to work right. I get the following message: One or more data connections in your form are specified with a server-relative link, but no publish location has been defined for the form. Data connection settings will be retrieved from the server specified at design time. To enable the server-relative link, publish your form to a Microsoft Office Server. Certain types of server-defined Web Service connections will not work with the design time settings. I also get this message: The query cannot be run for the following DataObject: Classes InfoPath cannot run the specified query. The query cannot be run for the following DataObject: Classes InfoPath cannot run the specified query. You are working offline. InfoPath will use offline data instead of connecting to external data sources. If no offline data is available, some form elements, such as drop-down lists, may be blank. I get this message even though I have published the template to SharePoint. Any clues?