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

Even­tID: 5566

This hap­pens when using a dat­a­con­nec­tion to a SQL serv­er using a .udx file.

To solve this you need to have Enable embed­ded SQL authen­ti­ca­tion checked. Also if your SQL runs on anoth­er serv­er you need cross domain acces. So make sure that Allow Cross-domain data access for User Form Tem­plates is checked.


Change these set­tings in Cen­tral Administration/Configure InfoPath Forms Ser­vices

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

Click for a lager view

Checking date format

Some­times you have to deal with an Infopath textfield to col­lect a date. Then it would be use­full to check if the date that is entered is a valid one.

This solu­tion is not the per­fect one, but it fil­ters out most of the wrong answers and typo’s.

Click on “Val­i­da­tion” and add a new val­i­da­tion.

Enter this pat­tern in the pat­tern field. Make sure you select “does not com­pare 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 euro­pean date (dd-mm-yyyy and d‑m-yy or any com­bi­na­tion)
An eng­lish ver­sion (MM/DD/YYYY) would be some­thing 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

Some­times you just need to make sure that the file­name of the infopath­form is unique when you save it to a library. One method to achieve this is to include the itemid in the file­name.

Here is how to do just that:

First you need to cre­ate a dat­a­con­nec­tion to the library where your infopath forms are saved. This is to aquire the itemID field.

Lat­eron we are going to deter­mine the last ID and add “1” to it.

Use this for­mu­la in the file­name­field:

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



Select the ID field in the above cre­at­ed dat­a­con­nec­tion.

Car­ry on!

Display the formtemplate version on the InfoPath 2007 form

To dis­play the form ver­sion on the InfoPath 2007 form add an expres­sion box con­trol to the form. Copy and past the fol­low­ing 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 web­brows­er 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 orig­i­nates.

To sent you back to a URL of your choice you can use the &Source option in the URL that is call­ing 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 oth­er day I had to cre­ate an InfoPath form that was using cas­cad­ing drop down selec­tion fields. But then I dis­cov­ered that when you set the form to be brows­er enabled it is not pos­si­ble to use a fil­ter in order to lim­it the selec­tion.

Luck­i­ly i stum­bled upon a very good arti­cle by Ishai Sagi. He explains in this arti­cle how to use the owssvr.dll ser­vice to load a fil­tered xml ver­sion of a Share­Point list. Thanks Ishai!

There are a few addi­tions i would like to add to his arti­cle.

  1. When select­ing items from a ware­house that are based on an item-cat­e­go­ry the list that pops up is not sort­ed. This might make find­ing the right item a tough job. This is where the View para­me­ter comes in handy.
    • Cre­ate a view in Share­Point that lists the items in the order you wish and maybe even apply a fil­ter.
    • Use the &View={view GUID} para­me­ter to select this view.  …/owssvr.dll?Cmd=Display&List={put the ID here}&View={List GUID}&XMLDATA=TRUE
  2. To pre­vent mak­ing selec­tions that do not match is is wise to clear the child fields when you have select­ed an item in the par­ent field. Oth­er­wise it is pos­si­ble to change the par­ent field after you have select­ed an item in the child field and come up with an impos­si­ble set of items. To do this cre­ate a rule for the par­ent 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 val­i­datie van een BSN in infopath. Bij het invullen van een BSN is het wenselijk dat direct aangegeven kan wor­den of het ingevo­erde num­mer een geldig BSN is.

De for­mule die hier­voor gebruik wordt is:

(9 * sub­string(., 1, 1) + 8 * sub­string(., 2, 1) + 7 * sub­string(., 3, 1) + 6 * sub­string(., 4, 1) + 5 * sub­string(., 5, 1) + 4 * sub­string(., 6, 1) + 3 * sub­string(., 7, 1) + 2 * sub­string(., 8, 1) — 1 * sub­string(., 9, 1)) mod 11 != 0

Hieron­der leg ik aan de hand van voor­beelden uit hoe deze is te gebruiken in een Infopath for­muli­er.

Open de eigen­schap­pen van een tek­stveld:
 

Klik op gegevensval­i­datie:

E
n kies Toevoe­gen

Kies ver­vol­gens bij Als deze voor­waarde waar is: voor De expressie

En plak hier de for­mule uit het voor­beeld

Vul bij Scher­min­fo in: Geen geldig BSN

Klik 3 x op OK

Klaar!

Dataconnections in InfoPath and SharePoint

Dataconnections in InfoPath and SharePoint

Introduction

This walk­through is based on screen­shots of a dutch envi­ron­ment. This how­ev­er should not be a prob­lem. These screens in any oth­er lan­guage would have the same options on the exact same spot. You even might learn a lit­tle Dutch while you’re at it.

What we are going to do here is cre­ate a dat­a­con­nec­tion and even­tu­al­ly save it to a con­nec­tion­sli­brary in Share­Point as a .udcx file.

Create dataconnection

Cre­ate a dat­a­con­nec­tion in infopath.

Select Data­base (Microsoft SQL Serv­er only)

And click Next

Click op Data­base Selecteren

Select New SQL serverconnection.odc

And Click open

Enter the server­name and select the type of cre­den­tials you would like to use. Click Next.

When you choose to use user­name and pass­word it rec­om­mend­ed to save these cre­den­tials in the file.

Select a table and Click Next.

Select the fields that need to be read.
and Click Next, Next and  Fin­ish.

Convert the dataconnection

Select the con­nec­tion we’ve just cre­at­ed and Click con­vert.

Select the dat­a­con­nec­tion­sli­brary in Share­Point and name the file. Make sure to add the .udcx exten­sion.

And Click OK.

Delete the orig­i­nal dat­a­con­nec­tion and Click Add.

Select search the Microsoft Office Share­Point Serv­er for con­nec­tions.

And Click Next.

By click­ing the Man­age Sites but­ton add the site that holds the dat­a­con­nec­tion­sli­brary.

Open the dat­a­con­nec­tions fold­er and select the new­ly cre­at­ed dat­a­con­nec­tion.

Click Next.

Name the new con­nec­tion and click Fin­ish.



The new dat­a­con­nec­tion is ready for use.

Security settings in SharePoint

Make sure the user has all the required per­mis­sions on SQL Serv­er.

If you have hard­cod­ed the cre­den­tials (in con­nec­tion string, UDCX file, etc), “Allow embed­ded SQL Authen­ti­ca­tion” must be enabled under Cen­tral Admin -> Appli­ca­tion Man­age­ment -> Con­fig­ure InfoPath Forms Ser­vices.

Make sure to have cross-domain access enabled for Infopath Forms Ser­vices under Cen­tral Admin -> Appli­ca­tion Man­age­ment -> Con­fig­ure InfoPath Forms Ser­vices and also have this set­ting enabled in Inter­net Explor­er for the zone InfoPath form loads.

Have both SQL and Win­dows authen­ti­ca­tion enabled on SQL serv­er

If your brows­er enabled form is not work­ing with Win­dows cre­den­tials, try using SQL cre­den­tials.

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 hot­fix. To change the sta­tus that the logo has when you view a form in a brows­er win­dow, do one of the fol­low­ing on the serv­er that is run­ning Forms Serv­er 2007:

  • To turn off the logo, run the fol­low­ing SQL script.
    stsadm -o setformsserviceproperty -pn AllowBranding -pv false

  • To turn on the logo, run the fol­low­ing SQL script.
    stsadm -o setformsserviceproperty -pn AllowBranding -pv true

  • To deter­mine whether the logo is turned on or whether the logo is turned off, run the fol­low­ing SQL script.
  • stsadm -o getformsserviceproperty -pn AllowBranding