If you've ever designed and deployed a form that will be frequently used or require significant data analysis, you have probably looked into maintaining data in a SQL database. With InfoPath 2003 and the InfoPath 2007 rich client, you get what you expect. You create a main database data connection to the SQL server and pick tables and columns that meet the requirements for submit functionality. When you open your form and click the "Run Query" button, you see the data pulled from the database as you'd expect. You then happily insert, update, or delete records and, when the time is right, hit "Submit". Luckily for you, the InfoPath client took care of maintaining the list of changes that you made while editing your form. With this list of changes intact, your updated data streaks back to the database to await the next query.
Enter InfoPath Forms Server... Here we don't get the change tracking for free, so we'll need to do some work to simulate change tracking outside of the form's execution. Basically, what we're going to try to accomplish is to use an intermediate web service that will handle querying and submitting the data from and to the target database. The web service will timestamp the data at query time and send the data to the form for editing. Then the form filling user will edit the data and click "Submit". When the data arrives back at the web service, we need to figure out what changed in the meantime. This means that we'll have to check to see if anything has changed in the database since the time when we queried the data. If it has, then the submitted data should be rejected and the user should re-query before re-applying her edits. If it hasn't, we'll diff the submitted data with the database data and submit the difference back to the database! Let's get started!
Create the Web Service and Setup the Database
Since the InfoPath data connection wizard is easiest to use when your web service is already established and available, let's start with creating the web service and setting up the database.
1) Download the attached archive and extract it somewhere on your hard drive
2) Create a new web site in Internet Information Services (IIS)NOTE: IIS must be enabled as a Windows Component through "Add or remove Windows components" in the "Control Panel")
3) Create the web service
4) Add the code and service asmx files to the project
5) Customize the web service code for your database
6) Create the database table and DML triggerThe web service includes logic to update the database table and create a DML trigger to maintain a timestamp of Last Update for each record. However, you may want to create the timestamp column and trigger yourself.
7) Build the Visual Studio solution and publish the web site
Design the InfoPath Form Template
Now that we've setup our database and constructed our web service to do the querying and submitting for us, it'll be a breeze to design an InfoPath form template based on the web service.
1) Design a new, browser-enabled form template, based on the web service that will query/submit the DataSet.
2) Set the default values for the "ID" and "QueryTime" fields
3) Insert the controls into the View.
4) Publish the form template to your InfoPath Forms Server
At this point, you have a form template that will work correctly when you open it in the InfoPath rich client. But this post is all about getting things to work correctly in the InfoPath Forms Server. So you'll need to configure your data connections to work in the browser by converting the main query and submit data connections to use Universal Data Connection (UDC) files in a Data Connection Library (DCL). Now you should be all set. The web service will query and submit the data to the database, and we'll make our best attempt at a diff of the database data against the submitted data.
From here on out, it's up to you. If you want to, for example, modify the database structure or change the way the trigger works, then you're going to need to modify the web service code. You'll also need to use "Convert Main Data Source" to update your form template whenever you modify your web service. You might also want to add support for multiple tables. All this will take some exploration of ADO.Net DataSets, but it is a reasonable exercise as long as you're comfortable writing managed code.
Forrest DillawaySoftware Design Engineer in Test
Loving this post. Keep up the good work!! :-)
Great post. Everything works great in the InfoPath client, but not from Forms Services. The form is full trust and is an administrative form. Both of the data connections are UDC files and I enabled cross-domain data access. The query works great. The submit is giving me errors. Any ideas? Here is what I see in the SharePoint logs:
Unhandled exception when rendering form on postback System.IndexOutOfRangeException: Index was outside the bounds of the array. at Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.HandleSubmitDataSetInput(XPathNavigator inputSubDOM, Boolean useDataSets, XmlDocument soapRequest, Solution solution, String name) at Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.PrepareSoapRequest(XPathNavigator inputSubDOM, Boolean inputUseDataset, Solution solution, String name, Boolean useSelf) at Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.ExecWebRequestSync(XPathNavigator inputSubDOM, Boolean inputUseDataset, XPathNavigator resultsSubDOM, Boolean resultUseDataset, XPathNavigator errorsSubDOM, Uri serviceUrl, Uri soapAction, Int...
Thanks for your response! I'm glad to see someone's trying this out!
Anyway, as for the error you see on postback, I couldn't reproduce it with a simple test, so it looks like there's something about your DataSet that the Forms Server doesn't like. It's possible this has to do with the values you entered into the dataFields, but I'd have to see your source files to diagnose it and see if I could replicate it locally. On your side, I'd try tweaking the data types of the database column (and modifying the corresponding web service code) to find out if serialization is working correctly.
I'd love to investigate your failure. It would be great if you could send me:
1. A copy of your form template
2. Your web service code
3. The asmx file
4. The 2 sql scripts you used to create your DB table and the DML trigger
5. Version/SKU information about your Forms Services installation
6. A description of the data you entered into the form template prior to submitting
7. Any other environment-specific detail you think is relevant.
My (clumsily encoded) e-mail is “For[DELETEME – fighting spam]rest[dot]dilla[DELETEME – fighting spam]way [AT] MICROSOFT [DOT] COM”
I am having trouble getting VS 2005 to publish the site. I keep getting the error "js-forms-webservice:8080/" is not a valid virtual path. After initially setting the port number to 8080 that path should connect to the IPFSDiffGram directory under wwwroot. I assume VS connected sucessfully to it when creating the new project.
I did a little research and found a similar issue noting a VS bug prior to SP1 so I have now updated to SP1 but am still getting the error. Any ideas?
I'm not sure how to reproduce the error you're seeing, but could you elaborate on the steps you're following?
Following these steps, I don't see any failures:
1. Create the web site in IIS, with local path set to "C:\Inetpub\wwwroot\IPFSDiffGram" and port 4712.
2. Create a separate app pool for the web site.
3. Launch VS and create a new web site on local IIS under the site you just created.
4. Add the cs and asmx files.
5. Build the project.
At this point, I can access the web service via the URL: http://localhost:4712/DiffgramService.asmx.
Are you seeing different results in following those steps? "Publishing" the web site isn't strictly necessary when you're designing the web site on your local server.
Great post, great blog !
Finally some good / useful information about InfoPath!! :)
I'm running the sample (in exact the same configuration as the sample; same db names etc).
But I keep getting 'The exeption: The select command string must start with the 'SELECT' keyword!
I've added the selectCommandString to the exeption string so I can see what the commandString actualy is.. and that is: empty..
throw new Exception("The, " + selectCommandString + ", select
I've looked in the code where the selectCommandString is beeing filled.. but I can's seem to find it ...!
Thanks in advance!!
Great - I'm almost there, but getting the same error as rgardner i.e. Index out of bounds when submitting via a browser. Works fine in rich-client!
I have copied exactly what you had i.e. same db, table, username, password (although I had to change your code which had the columns called data1 etc - changed them to Column1 etc).
I'm guessing it's some configuration issue with SharePoint server. Any ideas?
Thank you for the great info.
If the submit process is an SQL insert, is there a way to return the ID column from the insert procedure and place it in a field on the form?
After long absence...
Shuwi, it looks like you're using the "DBDataDynamic" WebMethod. This was my best attempt on short notice to provide a scalable WebMethod that could accept an arbitrary connection string and command, and then query and submit valid recordsets to the database table. However, for most purposes, it's a lot easier to just use the "DBData" WebMethod, which takes a static number of query parameters corresponding to fixed columns in your DB table. Try that one before you dive too deeply...
Cterry and Rgardner, we've achieved an internal repro environment for the IndexOutOfRangeException, so we'll take a look and see what we can find out. For now, I don't have any further info.
Terry G Phillips, you can get the updated ID by simply executing the query data connection, constraining the recordset by passing queryFields that will return the record you're looking for. Then you copy the ID value, using Rules or custom code in your form template, to move it to a target field.
Oh, and Shuwi, the strings passed to the web service are populated with the values of the "dfs:queryFields" xml nodes in your form template. It's a good idea, when using the dynamic WebMethod, to set a default value for the queryFields to specify the connection string, command, etc.
Okay, Cterry and Rgardner, I think we've narrowed down the issue on our side. Shuwi, this affects you as well.
Map all submit parameters, not just the DataSet:
Revisit step 15 for designing your submit data connection: If your web service takes parameters to constrain the select command, map these parameters to the queryFields generated during the creation of the query connection.
In the screenshot of the submit data connection parameter mapping page in the data connection wizard, I only show the DataSet parameter for the web service. That screenshot is actually out of date relative to the source for the web service.
In the attached files, you'll see that the "UpdateDBData" WebMethod actually takes query parameters, in addition to the data being submitted. This is to allow the web service to restrict the result set, being queried and successively submitted, to a subset of the data in the table.
Now, practically, that means you'll actually see "Column1", ..., "Column5" in the data connection wizard. For browser-enabled form templates rendered in the browser, you must map the other (non-dataset) parameters to their corresponding queryFields. The rich client can handle it if you don't map the parameters, and empty strings are sent in place of the queryFields values. IPFS, on the other hand, requires that the mappings be defined.
Great post. Everything works well!
spent a lot of time:)
I receive an error message when I change the data type with int.
error :"The form can not be submitted because it contains validation errors. Errors are marked with either a red asterisk required fields or a red dashed border(invalid values) "
Fiedld or group :tns supplier_id
error: only integers allowed.
Excellent responses, everyone!
1. xjmmn, string fields in InfoPath do not, by default, require a non-empty value. When you change the data type to int, you'll need to guarantee that the fields contain an integer value. Otherwise, you'll see the data validation errors at submit time.
2. Bjoern Thomsen and Paresh also pointed out another corner case that needs to be addressed: After step 2.6, you need to set the default "Choice" for the DataSet to use the "Time" table. Follow these steps:
a. In the InfoPath Designer, click "Tools" >> "Default Values..."
b. Expand the nodes until you see the "DBTable" and "Time" group nodes under the repeating "(Choice)" node.
c. Select the "Time" radio button to include the timestamp in the form by default.
NOTE: After fixing your form template as above, you'll have to replace line 897 in "DiffGramService.cs" with the following code:
object maxPrimaryKeyObject = dbPrimaryKeyValuesReader.GetValue(0);
if (maxPrimaryKeyObject is System.DBNull)
// No primary key values exist in the table. This must mean that there are no records in
// the table. Therefore, we can safely assume this submit operation will create the first
// primary key in the table.
maxKey = 0;
maxKey = (int)maxPrimaryKeyObject;