SQL Database Integration

Re: Inserting into IDENTITY columns via stored procedure causes IDENTITY to increment at least twice

Greg S — Mon, 15 Dec 2008 19:16:49 GMT

Stephen Kent:

i.e don't have the data connection called on open and instead assign it as a submit rule....

I don't have the data connection set to be called on open. It's only executed once in code behind. InfoPath is still doing some sort of "pre-execute validation check" even in that scenario.

Great article by Hilary. I might implement an approach like that in the future.

-Greg

Re: Inserting into IDENTITY columns via stored procedure causes IDENTITY to increment at least twice

Stephen Kent — Tue, 09 Dec 2008 23:42:12 GMT

Just call your stored procedure when you need to assign a unique value to the form...

i.e don't have the data connection called on open and instead assign it as a submit rule....call the web service to invoke the stored proc, return the value and assign to your form on submit preserving the sequential numbering.

Re: Inserting into IDENTITY columns via stored procedure causes IDENTITY to increment at least twice

Hilary Stoupa — Tue, 09 Dec 2008 19:07:49 GMT

The first call is to generate the schema, which is why it only occurs once per session.

Now, for the really crazy part.... If you modify your sproc so that it generates a message (actually, don't know if *any* message will do, that would be interesting to test) by adding a commit without a begin, you may not see the 'every other id' feature. I've got a blog post here that mentions this behavior. I would LOVE to know why that works...

Inserting into IDENTITY columns via stored procedure causes IDENTITY to increment at least twice

Greg S — Tue, 09 Dec 2008 17:33:16 GMT

More of an FYI, but it's an interesting idiosyncrasy.

If you have a data connection that inserts records into a SQL table using a stored procedure, and that table uses a field with an IDENTITY (i.e. one that auto-increments), then you might notice that the table skips IDENTITY values (e.g. 1, 3, 5, 6,7, 9 etc.). After looking at the data tables I thought I had a bug in my code somewhere until I realized InfoPath was calling the procedure before any of my code was.

Just FYI: IDENTITY columns are incremented when a record is inserted – even before the transaction completes. If the transaction is cancelled, or a record is deleted, that particular IDENTITY will never be re-used.

I can only assume that InfoPath calls the stored procedure once in a transaction, and then cancels it, before the data connection is executed by the form itself. It may do this to test the output structure – who knows. I’ve given up trying to make sense of how InfoPath behaves in certain circumstances with SQL databases.

It only does this the first time the data connection is executed for a given session. All subsequent executions while the form is open don’t do this.