SQL Database Integration

Re: Changing existing row and inserting row fails

ek38 — Fri, 23 Jul 2010 15:00:08 GMT

Patrick,

Thanks for your response. We've looked at the accelerator off and on but never took the plunge. One of these days we will.

We found the problem with our form. We had a field called "last Modified" which was a date and time in the repeating table. We were updating this field with the function Today(), however, as you know when you update a field without filter in a repeating table, it updates all the rows. Somehow this was causing our problem. When we removed the rule, the problem went away.

Eric.

Re: Changing existing row and inserting row fails

Patrick Halstead — Thu, 22 Jul 2010 00:16:16 GMT

Hi Ek38

When you created the data connection, did you specify that the field was primary key? You may want to look at the wizard again. I thought there was a specification required for single table cases. Usually, you'll have the repeating table under another table and that should work fine since it knows it's got a foreign key. This should work but may require reshaping your data connection.

Keep in mind that InfoPath SQL data connection has numerous limitations:

Can't use with browser-enabled forms when SQL on a different server
Can't use with XML data types (like images and rich text)
Changes to DB or XSN render previously saved XMLs invalid (not an issue if you disable save)
Doesn't work in an extranet scenario
etc.

That's why the best practice is to use a Web Service. Qdabra freely pays for this forum and our goal is to answer 90% of all posts (some posts are about solving World Peace, etc. which we think is unobtainable given the species). Qdabra sells an inexpensive Web Service accelerator called "DBXL" that you can use to map your forms to SQL. It has many other benefits too (fills lots of SharePoint gaps). Rather than write one Web Service for each form template, DBXL provides a data-driven solution. So, in addition to saving you boat loads of time, you also save money since you can use the same Web Service for all of your InfoPath templates.

Best of luck with your mission,

Changing existing row and inserting row fails

ek38 — Wed, 21 Jul 2010 13:34:14 GMT

One database table with retrieve and submit enabled. Return multiple rows is true.

One repeating table. Can't get much simpler.

Problem:

When we retrieve an existing row and make changes to the row.
Then without submitting it back to the DB, we "Insert a new row" and add different values.
When we submit both rows back to the DB, the values in the first row overwrite the newly inserted row and essentially we have 2 identical rows.

(note: the only value that is different after submitting is the ID column. It is an "Identity field" in SQL and auto increments)

This is 100% repeatable and happens everytime.

If we change a row then submit. Then Insert a row and submit, it works fine. But really we our users shouldnt' have to do this extra step.