Data Connections

Re: Submit InfoPath Data to Multi tables in MS-SQL Server

jinh@globaledgesys.com — Wed, 03 Mar 2010 20:24:59 GMT

Thank you Patrick!

Re: Submit InfoPath Data to Multi tables in MS-SQL Server

Patrick Halstead — Wed, 03 Mar 2010 18:39:21 GMT

Hi Jinh and welcome to the forum!

InfoPath does not natively support submitting to multiple tables from a data source created from an XML schema (the default option when creating a form). You have to either re-create your form from the database or use a Web Service like Qdabra's DBXL. Or, YOU CAN WRITE CODE! :) ... seems like that is the option you are taking. The challenge here is locking and synchronizing data to prevent overwrites and make sure you update the relational DB schema correctly. Certainly, the codeless approach is simpler and will be less maintenance down the road. Of the two "codeless" approaches, the best practice is to use a Web Service because it separates the presentation layer from the data representation layer better. With a Web Service in the middle, you can update the form or the database without breaking the submit process.

I hope this was a tad helpful. Sorry it couldn't be more so.

Submit InfoPath Data to Multi tables in MS-SQL Server

jinh@globaledgesys.com — Mon, 01 Mar 2010 20:53:53 GMT

Hello All,

Hoping that I put this in the right post, if not please assist.

I have a client require me to create an InfoPath form base on their existing database.

The client has total of 3 tables which listed as follow:

Table 1 = Client with (ClientID [int], [PK]), (ClientName [varchar(50)])

Table 2 = Country with (CountryID [int], [PK]), (CountryName [varchar(50)], (ClientID [int], [FK])

Table 3 = State with (StateID [int], [PK]), (StateName [varchar(50)], (CountryID [int], [FK])

As you can see there're some foreign keys which has their relationship point from (many[Country] to 1[Client]), and (many[State] to 1[Country])

However, I can create a database connection via UDCX to pull the datas, but I'm having problem to create a submitting data.

What I've done so far is that I be able to submit the data into just one table which is the [Table 1], but I can't submit the data to their related tables.

I used the Stored Procedures to submit to the database via the UDCX connection.

What I don't know how to do is......

I don't know how to create a right Stored Procedures that will allow me to insert with the following sql query statement from within the c# coding.

"exec AddClient '" + clientName + "', '" + countryName + "', '" + stateName + "'";

AddClient is the Stored Procedure name, clientName, countryName, & stateName are the field value which collected from the infopath "selectSingNode" statement.

Everytime I debug, I got a sql conn.Execute() error which doesn't make any sence to me.

Please help me out as I've been trying with a dead-end on google search.

Thank you very much in advance!