InfoPath Dev

Infopath 2010 (part of MS Office Professional Plus 2010) version 14.0.61269.5000 (32 bit)
SQL Server 10.50.1600

Snapshot describing the table structure, InfoPath myFields panel and sample demonstration of issue:

 

http://postimage.org/image/ysnk6r5mb/

Hi,

I'm using Infopath (database template) as interface for 2 tables in a SQL database. The first table (let’s call it Config Table) is a dimension table (contains various attributes like metric name, goal etc.). The second table is a fact table (let’s call it Data Table) and stores the actual data periodically. The Data Table has a Foreign Key that binds to the Primary Key of Config Table.

I’ve been successful at creating the InfoPath form such that users can create new metrics in Config Table and later on, populate Data Table with data points on weekly basis (using Repeating Table inside Repeating Table).

The issue I’m facing is that while updating (adding or updating the existing data points) in the Data Table, InfoPath overwrites some of the data points in Data Table (thus creating duplicate rows).

I have tried creating multiple databases on different systems and tried different InfoPath forms (SQL Server 2008 R2, SQL Server 2012, InfoPath 2010, InfoPath 2013) and can replicate the problem repeatedly. Most likely it has something to do with how InfoPath parses the Insert & Update queries to SQL.

 

Has somebody encountered the same issue before? Is this a known issue with InfoPath? And if it is a bug, can somebody help me report it to Microsoft? Any workaround in the meantime would be appreicated too. Thanks for all the help.

Cheers!
Harsh

I am having a similar issue...when trying to add(insert) a row and update a different row I get a primary key violation...looks like it is trying to insert the row i made an update to...I am having to tell my users to seperate the inserts from the updates...this worked in IP2007...???

 Harsh - try IP2007 if you can...I am using IP2010 and SQL2012

This may be this bug. Everyone using Windows 7?

That's bull's eye, Hilary! Impressive!! :)

The symptoms match what we have in the KB (on multiple Windows 7 machines). Two different but similar forms work fine on Windows 8 (they reliably fail on Windows 7). One test machine with Windows 7 started working fine after hotfix in KB was applied.

Apart from pushing the hotfix or advising all users to move to Windows 8, is there something I can do in Infopath form to address the issue?

The KB does mention a workaround ("configure the Resync property in the ADCPROP_UPDATERESYNC_ENUM enumerated constant to perform only insert operations or update operations when the UpdateBacth() method is called."), I'm not sure how to implement that in Infopath though.

Or maybe you would know of a completely different solution altogether (imagining the issue would disappear if we can violate any 1 of the 5 different conditions mentioned in KB)..

Thank you for your help and hats off to your knowledge!

To the best of my knowledge, there is nothing in InfoPath that you can do to address this - I think you have to push the hotfix. At least that's the approach I'd take.

Cool. Thanks again, Hilary. Marking your earlier response as the answer. Great day ahead :)

Cheers!
Harsh