InfoPath Dev

I have tried everything thus far. My main connection was created with the Data Connection Wizard if that makes a difference.  Also I am using infopath 2007.  All 3 examples below result in an error message when the form is run in debug.  The message is "InvalidCastException was unhandled by user code".  So is it possible to submit to a data connection to an sql database using managed code.  It works just fine when using the built in submit function.

Thanks

 DataConnection myConn = (DataConnection)this.DataConnections["Main connection"];   

FileSubmitConnection mc = (FileSubmitConnection)this.DataConnections["Main connection"];

WebServiceConnection mc = (WebServiceConnection)this.DataConnections["Main connection"];

Hi Flanker,

It's way more complicated than a plain submit because of the update possibilities. In other words, the submit includes a diff gram telling how to update. Your managed code has no clue how to do that since it isn't keeping track of update changes and that's probably why it fails.

Best practice is to use a Web service not a data connection on your main data source. The Web Service can then be modified as your form changes.

Qdabra Software (company that pays for this free forum) has developed a data driven Web service called DBXL that you can try out. It will let you submit the main data source and map the contents to SQL using a data-driven mapping. It supports updates too. It's a cheap option if you have the ability to leverage accelerators. If you have to write it your own, go with the Web service approach.

Thanks Patkick,

This seemingly trivial change to my form has turned into quite an ordeal.  I wrote this message on a previous post in the NewBee forum.

"I have a form with 2 data connections, email and sql database. On submitting the form using the submit button on the toolbar it works just fine.  I use 'custom action rules' in the submit options.  Also a pop up message is displayed (using C# managed code that is triggered when one of the required fields on the form is filled out), once the user clicks the submit button, the pop up message displays (unless it already displayed) and then both data connections then submit just fine.  That is the flow and what I want.   

My problem is now I must add a submit button to the form itself while maintaining the same flow as above.  The submit button on the form works just fine when submitting the email and sql data sources using the 'rules and custom code' option.  But when I try to display the pop up message before submitting the form, I have to click the submit button twice, once to generate the pop up message and once to submit the data connections.   Also the "form submitted successfully" message is not being displayed when the form is submitted to the sql database. 

The control flow of the form behaves differently when using the submit button on the toolbar versus using a submit button on the form. "

So based on the limitations from the above, I decided to 1) display the message, 2) send the email and 3) update the database all in the FormEvents_Submit event handler using managed code.  Both 1 and 2 work but not 3.  And that is why I posted in this forum.    

 Thanks

Hi Franker,

Thanks for clarifying. Seems like your scenario is slightly different from what I had assumed. Might be just a simple rules sequencing issue. Have you looked at defining your rules in the Submit dialog? That might give you a slightly different experience. If you are using InfoPath's out-of-box Main Data Source connection to SQL you will run into problems when you try to update the schema or the data base. Most of these can be resolved by converting the data source, but if you are also saving XML, you will have to migrate those forms. That's why we tell people to use XML schemas (not data sources inferred from SQL) and Web Services. But, that may not have anything to do with your issue.

Hi Patrick,

When you say "Submit dialog" do you mean 'Rules for Submitting Forms"?  If so, I initially tried to add the message to the dialog box as the first rule, and that worked fine, but the message I want to display was way too large for the dialog box and thats what opened up this can of worms.  Can the message box be resized?  To fit a much larger message? 

Thanks