Data Connections

Re: Getting New Primary Key Value After Initial Submit

Hilary Stoupa — Wed, 13 Feb 2013 22:23:00 GMT

Thanks so much for posting back with details - it will probably save someone else some frustration. Glad you have it sorted out! :)

Re: Getting New Primary Key Value After Initial Submit

abcolson — Wed, 13 Feb 2013 22:14:19 GMT

Problem Solved!

Actually, this is an issue I wish Microsoft would figure out but it may be that this is by design.

Anyways, when I was submitting to the database my table had insert triggers and in the triggers I was sending an email. Not a problem in SQL 2000 but they changed the mechanism (in SQL 2005, I think) to use DB mail (sp_send_dbmail). I was sending the email after the table was updated with new records and when I called sp_send_dbmail it changed the value of @@IDENTITY. Hence, InfoPath was picking up the wrong value for the new primary key created.

The solution was to call sp_send_dbmail in the INSTEAD OF INSERT trigger before the table was updated. In the event that there were any failures in the insertion process the email would be aborted which means no stray emails (and this is by design).

This type of thing has bitten me before. Very frustrating. But now we know...

Tony

Re: Getting New Primary Key Value After Initial Submit

abcolson — Tue, 12 Feb 2013 22:04:34 GMT

Yes, Hilary, you are correct. I tried my original test form on a different Windows XP machine with InfoPath 2010 and got the same erroneous behavior. Back on my Windows 7 development machine, I then modified the adapter to point to our old SQL Server 2000 database and I got the correct expected behavior.

Now I need to find out what is different with SQL 2008 vs. SQL 2000. Maybe the link you provided will help me.

Thank you,

Tony

Re: Getting New Primary Key Value After Initial Submit

Hilary Stoupa — Tue, 12 Feb 2013 20:53:06 GMT

So - when you tried this and it didn't fail, was it also with IP 2010? On a Windows 7 machine? There is an ADO.NET bug in Windows 7 that causes weird behavior in forms with Insert and Update statements - not sure if it may be what you are running into or not, but here's the KB link:

http://support.microsoft.com/kb/2591857

Re: Getting New Primary Key Value After Initial Submit

abcolson — Tue, 12 Feb 2013 20:44:11 GMT

New Development...

The prior examples and behavior were when my form was connected to our new SQL Server 2008 database. When tried with a SQL Server 2000 database, submission of the forms behaved as expected, i.e. the records did not change and the new id was populated as expected. So, it appears that the problem is not with InfoPath 2010 (Whew!) but rather with the back end database...

Well...out of the frying pan and into another frying pan...

If/When I figure it out I'll post the fix since others may run into the same situation.

Re: Getting New Primary Key Value After Initial Submit

abcolson — Mon, 11 Feb 2013 23:04:30 GMT

Some further clarification:

I made a very simplified test case example. I made a connection to a SQL Server database for a Filler Form, no managed code, no rules, form stays open on submit, made it trusted, and included the 3 fields:

id
Submitted On
Submitted By

The form looks like this after I fill in data (sorry, I couldn't figure out how to paste an image):

id: ___________
Submitted On: 2/12/2013
Submitted By: This is me

When I clicked submit, the submission is successful and the form automatically updated, only I expect the id (primary key) to be populated with the next value in the table, 48. Instead, the form refreshes with the record of id 46 as follows:

id: 46
Submitted On: 2/11/2013
Submitted By: rr

In this test form the difference in id is always 2 (I expected 48 but got 46). In my original problem form the difference is always 5.

Can anyone explain this behavior? Or more to the point, how can I get the form to update with the correct record that I just submitted?

Thanks

Tony

Getting New Primary Key Value After Initial Submit

abcolson — Sat, 09 Feb 2013 02:12:23 GMT

In InfoPath 2003, after submitting to a SQL database via code as

ADOAdapter2 adapter = GetDataAdapter(DOM_MAIN);
adapter.Submit();

the IXMLDOMNode representing the primary key value would be automatically set. This was handy as if I made a change and then re-submitted, the original record just created would be updated instead of a 2nd record being created.

With InfoPath 2010, I am trying to submit in code as

AdoSubmitConnection submitter = (AdoSubmitConnection)DataConnections[DOM_MAIN_SUBMIT];
submitter.Execute();

and the "id" attribute (representing the primary key) of my XPathNavigator does not get automatically set. I have even tried re-creating the navigator with CreateNavigator() and no change.

Is this behavior by design? Is this a departure from the behavior in IP 2003? In any event, how am I supposed to capture/access the new value of the primary key after a new record is submitted? (BTW, the attribute of "id" is "d:optional_long" in the schema).

Thank you

Tony