Data adapter failed during onload using a dataconnection file from InfoPath Form

The problem: When accessing a SQL table or view from a browser enabled InfoPath Form gives you an error Data adapter failed during OnLoad

EventID: 5566

This happens when using a dataconnection to a SQL server using a .udx file.

To solve this you need to have Enable embedded SQL authentication checked. Also if your SQL runs on another server you need cross domain acces. So make sure that Allow Cross-domain data access for User Form Templates is checked.


Change these settings in Central Administration/Configure InfoPath Forms Services

  • http://<servername:port>/_admin/ipfsConfig.aspx

Click for a lager view

Checking date format

Sometimes you have to deal with an Infopath textfield to collect a date. Then it would be usefull to check if the date that is entered is a valid one.

This solution is not the perfect one, but it filters out most of the wrong answers and typo’s.

Click on “Validation” and add a new validation.

Enter this pattern in the pattern field. Make sure you select “does not compare to”

(([0-9])|([0-2][0-9])|(3[0-1]))\-(([1-9])|(0[1-9])|(1[0-2]))\-(([0-9][0-9])|([1-2][0,9][0-9][0-9]))
This checks for a european date (dd-mm-yyyy and d-m-yy or any combination)
An english version (MM/DD/YYYY) would be something like this: (([1-9])|(0[1-9])|(1[0-2]))\/(([0-9])|([0-2][0-9])|(3[0-1]))\/(([0-9][0-9])|([1-2][0,9][0-9][0-9]))

itemID in infopath filename

Sometimes you just need to make sure that the filename of the infopathform is unique when you save it to a library. One method to achieve this is to include the itemid in the filename.

Here is how to do just that:

First you need to create a dataconnection to the library where your infopath forms are saved. This is to aquire the itemID field.

Lateron we are going to determine the last ID and add “1” to it.

Use this formula in the filenamefield:

concat("Formname - ";max(@Id) + 1)



Select the ID field in the above created dataconnection.

Carry on!

Display the formtemplate version on the InfoPath 2007 form

To display the form version on the InfoPath 2007 form add an expression box control to the form. Copy and past the following code in the Xpath box.

substring-before(substring-after(/processing-instruction()[local-name(.) = "mso-infoPathSolution"], 'solutionVersion="'), '"')

Navigate back to URL of your choice after closing in a form in a webbrowser

When you have filled in an InfoPath form in a webbrowser using a link you would like to return to the place you left off.
By default you will be sent back to the list where the form originates.

To sent you back to a URL of your choice you can use the &Source option in the URL that is calling the form.

http://sharepoint-serverl/site/_layouts/FormServer.aspx?XsnLocation=http://sharepoint-server/site/FormServerTemplates/Formname.XSN&SaveLocation=http://sharepoint-server/site/Formlibary&Source=http://sharepoint-server/site/page.aspx&DefaultItemOpen=1

Cascading drop down selection fields in browser enabled InfoPath form

The other day I had to create an InfoPath form that was using cascading drop down selection fields. But then I discovered that when you set the form to be browser enabled it is not possible to use a filter in order to limit the selection.

Luckily i stumbled upon a very good article by Ishai Sagi. He explains in this article how to use the owssvr.dll service to load a filtered xml version of a SharePoint list. Thanks Ishai!

There are a few additions i would like to add to his article.

  1. When selecting items from a warehouse that are based on an item-category the list that pops up is not sorted. This might make finding the right item a tough job. This is where the View parameter comes in handy.
    • Create a view in SharePoint that lists the items in the order you wish and maybe even apply a filter.
    • Use the &View={view GUID} parameter to select this view.  …/owssvr.dll?Cmd=Display&List={put the ID here}&View={List GUID}&XMLDATA=TRUE
  2. To prevent making selections that do not match is is wise to clear the child fields when you have selected an item in the parent field. Otherwise it is possible to change the parent field after you have selected an item in the child field and come up with an impossible set of items. To do this create a rule for the parent field that sets the child field to “”. This way you are sure that the child field is blank and that a new choice has to be made.

11 Proef in Infopath

Een validatie van een BSN in infopath. Bij het invullen van een BSN is het wenselijk dat direct aangegeven kan worden of het ingevoerde nummer een geldig BSN is.

De formule die hiervoor gebruik wordt is:

(9 * substring(., 1, 1) + 8 * substring(., 2, 1) + 7 * substring(., 3, 1) + 6 * substring(., 4, 1) + 5 * substring(., 5, 1) + 4 * substring(., 6, 1) + 3 * substring(., 7, 1) + 2 * substring(., 8, 1) – 1 * substring(., 9, 1)) mod 11 != 0

Hieronder leg ik aan de hand van voorbeelden uit hoe deze is te gebruiken in een Infopath formulier.

Open de eigenschappen van een tekstveld:
 

Klik op gegevensvalidatie:

E
n kies Toevoegen

Kies vervolgens bij Als deze voorwaarde waar is: voor De expressie

En plak hier de formule uit het voorbeeld

Vul bij Scherminfo in: Geen geldig BSN

Klik 3 x op OK

Klaar!

Dataconnections in InfoPath and SharePoint

Dataconnections in InfoPath and SharePoint

Introduction

This walkthrough is based on screenshots of a dutch environment. This however should not be a problem. These screens in any other language would have the same options on the exact same spot. You even might learn a little Dutch while you’re at it.

What we are going to do here is create a dataconnection and eventually save it to a connectionslibrary in SharePoint as a .udcx file.

Create dataconnection

Create a dataconnection in infopath.

Select Database (Microsoft SQL Server only)

And click Next

Click op Database Selecteren

Select New SQL serverconnection.odc

And Click open

Enter the servername and select the type of credentials you would like to use. Click Next.

When you choose to use username and password it recommended to save these credentials in the file.

Select a table and Click Next.

Select the fields that need to be read.
and Click Next, Next and  Finish.

Convert the dataconnection

Select the connection we’ve just created and Click convert.

Select the dataconnectionslibrary in SharePoint and name the file. Make sure to add the .udcx extension.

And Click OK.

Delete the original dataconnection and Click Add.

Select search the Microsoft Office SharePoint Server for connections.

And Click Next.

By clicking the Manage Sites button add the site that holds the dataconnectionslibrary.

Open the dataconnections folder and select the newly created dataconnection.

Click Next.

Name the new connection and click Finish.



The new dataconnection is ready for use.

Security settings in SharePoint

Make sure the user has all the required permissions on SQL Server.

If you have hardcoded the credentials (in connection string, UDCX file, etc), “Allow embedded SQL Authentication” must be enabled under Central Admin -> Application Management -> Configure InfoPath Forms Services.

Make sure to have cross-domain access enabled for Infopath Forms Services under Central Admin -> Application Management -> Configure InfoPath Forms Services and also have this setting enabled in Internet Explorer for the zone InfoPath form loads.

Have both SQL and Windows authentication enabled on SQL server

If your browser enabled form is not working with Windows credentials, try using SQL credentials.

How to change the status of the Infopath logo

How to change the status of the logo

By default, the logo is turned on after you install the hotfix. To change the status that the logo has when you view a form in a browser window, do one of the following on the server that is running Forms Server 2007:

  • To turn off the logo, run the following SQL script.
    stsadm -o setformsserviceproperty -pn AllowBranding -pv false

  • To turn on the logo, run the following SQL script.
    stsadm -o setformsserviceproperty -pn AllowBranding -pv true

  • To determine whether the logo is turned on or whether the logo is turned off, run the following SQL script.
  • stsadm -o getformsserviceproperty -pn AllowBranding