Tips and Tricks for:  InfoPath, SQL Server

The Problem:   InfoPath (f.k.a XDOCS) doesn't allow a button to submit to the SQL Server database.

When creating a form that's connected to a SQL Server database InfoPath doesn't always allow a button to have the submit action with the target of the database.   It also doesn't allow Delete and Submit.

Solution: There's always a reason given.  You can find it on a wizard screen.

There's always a reason for this problem that involves one of these possibilities:

• A stored procedure was used to query the data
• The user doesn't have permissions to modify the table (or columns)
• Use of a column that InfoPath doesn't support such as Image, TEXT or NText.

The key to not getting caught up in the problem is finding the message about this when running the form creation wizard.  It's in such small type in such an innocuous spot that it's easy to miss.  The picture below shows the Data Source Setup Wizard dialog box with the message highlighted with a red box. I added the box and I think Microsoft would do us a favor if they put the text in a red box on the form instead of leaving it in such small and easy to miss text.

I've written an article about connecting InfoPath and SQL Server.    It's my monthly article on DatabaseJournal.com.

Good sources of information about InfoPath include the microsoft.public.infopath news group and the InfoPath FAQ web site.