Secondary data connection gets queried twice

[aldom]

I am currently developing a Purchase Order form using InfoPath that is
publishing to a MOSS2007 forms library. (using Infopath 2003 currently)

The issue I am having is with generating unique, incremental PO numbers
automatically when a new form is created (ideally when the form is opened,
not when submitted). Having browsed around a lot I have determined that many
others have the same problem. The solution that I was hoping to use is to
have a SQL stored procedure that basically adds a row to a table that has an
identity column and then retrieve the SCOPE_IDENTITY() to return the PO
Number. This works well except for one annoying fact. For some reason, the
connection is always queried twice. This, of course, results in the PO Number
incrementing by 2 each time which is a bit annoying. I have tried both
programmatically running the query when the form opens (OnLoad) and also just
using the checkbox in the Data Connection configuration, but both have the
same effect.

I have run a SQL trace and can see the sp being called twice. The first time
it is sandwiched by a SET IMPLICIT_TRANS ON... ROLLBACK TRANS which I assume
is being added by InfoPath. Has anyone else run into this? Any other
solutions available for creating a simple identity field in a form?

Thanks,

Dana

 

[Bryan Phillips]

The call with ROLLBACK TRANS is being used by InfoPath to get the schema
of the resultset without returning any data. Unfortunately, that
rollback does not rollback the identity value too for performance
reasons.

--
Bryan Phillips
MCT, MCSD, MCDBA, MCSE
Microsoft MVP - Client Application Development
Blog: http://bphillips76.spaces.live.com
Web Site: http://www.composablesystems.net

 

[aldom]

Thanks Bryan. In the end I resorted to writing a web service to run the
stored procedure and that seems to be working fine. It seems like a pretty
convoluted process, just to generate and sequential numbers, but at this
point I'll take whatever works .

Thanks for at least confirming that I wasn't going crazy.

Dana