Dynamic SQL Data connection to InfoPath

There is no possible out of the box way to setup a dynamic SQL data connection to InfoPath form. When we use SQL table as a secondary data source in InfoPath, there is no option to filter data in SQL before loading into InfoPath.

Using InfoPath connection wizard, connection you setup is a plain select statement on table you select. If your backend SQL table has thousands of rows of data, all of it needs to be loaded first to form before you can filter.

But you can write custom C#/VB code on button click event to add the filter string to the connection you created. Here is sample code to do that.Dynamic SQL Data connection to Infopath

I have created a connection named ‘People’ to pull data from my People table in database. Using the code behind, I am adding modifying the SQL query, which is a plain Select statement and adding a filter value. I am getting the value for filter from a field in the form, in this case it is UserName. This code should be added on button click event.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Code Signing Certificate for Admin Approved InfoPath form template

Security level for admin approved code behind InfoPath form templates should be Fully trusted. Code Signing Certificate should be used to sign fully trusted InfoPath form templates before uploading to Central Admin. Go to File and Select ‘Form Options‘ under Advanced options Select ‘Security and Trust‘ in the left pane Code Signing Certificate: You can […]

Read More

The form cannot be opened because either signature is corrupt or the certificate used to sign the form expired or revoked

You get the following message when you try to open an already submitted InfoPath form  – ‘The form cannot be opened because either signature is corrupt or the certificate used to sign the form expired or revoked’ when the certificate used to sign the form has expired or revoked. Even if you try to open the […]

Read More

Clearing Infopath Cache

When you publish new version of InfoPath or update certificate for the form, some users may still see the old version or have issues with new certificate. Clearing InfoPath Cache in users machine resolves this issue. When user access an InfoPath form, it is usually cached in users machine. Whenever there is a new version of […]

Read More