Firstly, huge kudos to Iain for reminding me of this today

When you want to use teh 'Changed' event on a drop down list (or other control) in a browser based InfoPath form, you have to set the postback options to 'Always' = otherwise your event doesnt fire!

Thanks Iain

Whilst InfoPath has dataconnection capabilites to 'talk' direct to SQL & retrieve data, these can be a little problematic. An alternative approach is to implement a simple webservice in front of SQL.

The following example shows a simple service... (note - the code is not optimised & is passing the SQL query over http!)

We can create an InfoPath data connection now to talk to this service.

A similar approach can be taken to 'write' to SQL. This can be useful where you need InfoPath to submit only partial data to SQL rather than the whole form

To use, you can create 'Read' type web service

After configuring the Type (Web Service) and path, your service will be available as a secondary data source. Importantly, remove the check box on 'Automatically retrieve data'

Once completed, you can dynamically set the SqlStatement node (e.g. via a button rule)

Although Infopath designer can create most (much ?) of the User Interface, there can be times when it doesnt quite do what you need.

As an example, for drop down lists, you can pull in the values from an external data source

The dialog show the external connection and the value / display name as shown below

But what if you wanted to display both the id AND display name? The solution here is to drop into the XSL. Infopath uses XSL extensively & we can edit it if needed (Caution though, it is not as straight-forward as the normal editor & requires knowledge of XSLT). Note that the Infopath project must be saved as source files (if using Infopath.exe) or to access it in Visual Studio, close the manifest.xsf file.

By default, you will have View1.xsl - the xsl for the main view. Opening it will reveal something similar to that below, the xsl to display the drop down list and options

 The part we are interested in is the <option></option> values...

By changing this to the code below, we can show both the id and name values...

This will give us the following result

UPDATE

The example above was intended for Infopaht Client. AFIK the technique 'should' work for Forms server, but a quick test based upon a comment shows that the XSL may not render correctly

I'm a big fan of using WebServices to take the code and logic away from InfoPath.

Although you can use managed code inside an InfoPath Project, for me it makes sense to use simple button / control rules (that can be defined in the InfoPath designer) and use a WebService to do the hard work.

As an example, we needed recently to be able to check for Sharepoint document libraries and optionally create them from within InfoPath.

We created a WebService to do this. The service could run on the Sharepoint server, and hene use the Sharepoint Object Model. This gave us a relatively simple service...

(note the following code has been simplified for the blog purpose)

Using this example, we could then create InfoPath data connections to consume this service. The connection type can be a simple 'read' service and the button / control rules can set the service input parameters...

Button Rules (xpath removed for simplicity)

Rule1
Set a Fields Value, Field = WebName, value = "the SPWeb Name"
Set a Fields Value, Field = FolderURL, value = "Folder URL or SubFolder URL (e.g. folder/subfoler)
Set a Fields Value, Field = CreateFolder, value = false

Rule2
Query using a Data Connection, connection = "Web service connection"

Rule3
Set a Fields Value, Field = my:Fields/my:FeedbackNode, value = message returned from webservice

Within the example above, two things to note:

a) We are using RunWithElevatedPrivileges to let the service run with full privilages

b) The AllowSafeUpdates = true had to be set to allow folder creation, then revoked at the end of the service.

Of course, the service can be used now by any InfoPath or other project.

We've been doing a lot of work recently with Sharepoint and InfoPath, both the client application and Forms Server.

Data Connections are a great way of centrally managing the connections to external data sources, whether they be Sharepoint Lists, Webservices or other. However, like most things, they need some thought and planning.

A good introductory article has been posted by the Infopath team http://blogs.msdn.com/infopath/archive/2007/03/21/infopath-data-connections-part-1.aspx

Some quick things that we have found with connections....

a) Create them first (create a .udcx file) and submit to the server before starting work on the forms.

b) Create them using either this template (courtesy of the InfoPath team) or another editor. Although you can use the InfoPath connection wizard to convert connections on the server, we encountered some problems with this approach in the earlier days of forms development.

c) When developing for InfoPath client, be mindful of the need for central connections. The client form will download the connection details each time it is opened adding potentially several seconds to each tiem a form is opened. This was found to be not so much of an issue on forms server.

d) Consider if a data connection is really necessary or if, for simple lists (e.g. drop down selections) a 'hard coded' set of options is more efficient.

e) Store the data connections in a high level location (so they are accessible by forms at any lower level of a MOSS heirarchy) and keep the connection paths as server relative

f) Consider using 'read' type webservices for submitting data to external locations, rather than 'submit' type services. We found submit services to be greate where all of the InfoPath form had to be submitted, but using read type services allowed us to submit small sections of data, and even use button rules rather than code.