SQL Database Integration

Re: Inserting multi selection to database via DBXL

mikeng — Mon, 01 Oct 2012 21:11:25 GMT

This was helpful. thanks

Re: Inserting multi selection to database via DBXL

ZhenYuan — Tue, 16 Nov 2010 04:37:11 GMT

Hi Jimmy,

My two multi selection list boxes are populated by a secondary data source to my database and thus shows current table data. My button swaps selections from both boxes and re-receives data from their respective tables to show the updated selections.

Is this swapping possible without a database commit? ie, I only want to see my selections swap between the 2 MSLB but my database tables are not updated yet. Only on clicking of my form submit button, will the updates be performed via DBXL. Must I copy my table values onto my form datasource?

Thanks!

Re: Inserting multi selection to database via DBXL

ZhenYuan — Thu, 11 Nov 2010 01:11:40 GMT

Hi Jimmy,

As I replied to Hilary,

"I guess I'll use a table trigger to do my updates, but nonetheless, I'm keen to find out what statements can I replace my command with? Insert statements? If I didn't remember wrongly, I did try to run a stored proc before but similarly, it did not execute. Could it be because my SP has an update statement? "

I've already started work with a trigger solution but is just looking forward to understand InfoPath capabilities more. Just like the MSLB, I found a workaround, ugly method... But will now revert back to using the database mapping you taught.

Thanks for your help!

Re: Inserting multi selection to database via DBXL

Jimmy — Wed, 10 Nov 2010 07:14:08 GMT

I have a feeling the UPDATE is not working because of this InfoPath quirk:

http://www.infopathdev.com/forums/p/12799/45297.aspx#45297

The workaround given in that post will probably do the trick, but why not have the trigger do this update instead of running the SQL from InfoPath?

Re: Inserting multi selection to database via DBXL

ZhenYuan — Wed, 10 Nov 2010 06:10:08 GMT

Hi Jimmy,

It worked now. I believe its due to a repeated mapping as seen in attached screenshot. I have another MSLB where instead of removing the selections, I add them to another table. It seems that was causing some confusion for the mapping. I've removed the maps and will map to a new table now.

Now all I need is hopefully run a stored procedure on the mapped table. This will do the updates on the master table based on the engagements in my mapped table. I'm having trouble with the ADOQueryconnections. Meanwhile, I'm using DB triggers to do the updates. Not the most beautiful way but gets the job done :)

Do you happen to know why my sql statement below is not executing? I've put the code below as my button code.

Dim myAdoQueryConnection As AdoQueryConnection = Me.DataConnections("ETS_Group_Engagement")

myAdoQueryConnection.Command = "update formdb..ETS_Group_Engagement set EngagementCode = '1111' where EngagementCode = '0612' "

myAdoQueryConnection.Execute()

*************************

ETS_Group_Engagement is my SDC which uses a database connection instead of a webservice call. Connection to the database table is valid because I have a dummy field pulling data from the table. However, the execute statement does not update my table. No error during debug mode also.

myAdoQueryConnection.Execute()

Re: Inserting multi selection to database via DBXL

Jimmy — Wed, 10 Nov 2010 04:22:17 GMT

Did you add the [. != ''] filtering like I suggested? If EngagementCode is the primary key and there are EngagementCode nodes with blank values (as MSLBs tend to have), that will cause the mapping to fail.

If you try that and it still doesn't work, please attach an updated screenshot, and this time could you make it so the left and right sides aren't cut off?

Re: Inserting multi selection to database via DBXL

ZhenYuan — Wed, 10 Nov 2010 03:13:08 GMT

Hi Jimmy,

I tried adding the dot (.) and the xpath directly, both methods did not work. When I reshred, I get an error, "The value for key value "EngagementCode" is unspecified. All key values must be specified."

It seems my selections in the MSLB are not detected. How did you get your selections to be detected?

Re: Inserting multi selection to database via DBXL

Jimmy — Tue, 09 Nov 2010 13:58:54 GMT

Have the table node path go all the way to my:Engagements_in_Group instead of stopping at my:Group_Engagements_in_Group, and use just a dot as the node path for the EngagementCode column. If DAT won't let you select these from the taskpane, you can enter them manually.

You should probably also append [. != ''] at the end of the table node path to filter out any blank nodes that the listbox is concealing.

Re: Inserting multi selection to database via DBXL

ZhenYuan — Tue, 09 Nov 2010 10:08:05 GMT

Hi Jimmy,

Unfortunately, did not work for me. Attached are some screenshots of my data source and DB mapping. For the mapping I have the MSLB's group holder "Group_Engagement..." and for the EngagementCode which I am trying to store as separate DB rows, I have mapped "Group_Engagement.."'s child node. This worked for a repeating table but when it came to MSLB, I am having NULL inserted for EngagementCode despite checking a few selections in the MSLB.

Did I do my mapping wrongly?

Re: Inserting multi selection to database via DBXL

Jimmy — Tue, 09 Nov 2010 09:38:53 GMT

Yes this should definitely work. Have you tried it yet?

Inserting multi selection to database via DBXL

ZhenYuan — Tue, 09 Nov 2010 08:43:21 GMT

Hi Guys,

Has anyone tried having a multi selection listbox (populated with secondary data source) in their form and pushing the user selected values into a SQL database via DBXL database mapping? Can this work?

So far, I am only able to insert multiple rows into my database table when my multiple fields come from a repeating table. ie, I insert 10 rows into my repeated table and this 10 rows are mapped to my database table via DBXL's database mapping. Now I am trying to achieve the same with my multi selection list box. If I check 5 options in my MSLB, can I map them to my database table as 5 rows?

Thanks for any help!