InfoPath Dev

Hi,

How can I get rid of begin transaction and rollback that Infopath puts around any data change I have in user interface preview form? I would like to be able to test my form.

 Thank

Ali

Any feedback?

I use something like the following code when I need to save data fields on SQL Server:

XDocument.QueryAdapter.Command = "exec dbo.SaveSQLComplexDBinfo " + SQLComplexDBID + ",'" + ComplexName + "','" + DBName + "','" + AppCode + "'";
XDocument.Query();

When I check database activity, I see that something like this will be issued to SQL Server:

set implicit_transactions on SET NO_BROWSETABLE ON
exec dbo.SaveSQLComplexDBinfo 96,SQLDA-CX02','ATT25','ACTT'
IF @@TRANCOUNT > 0 ROLLBACK TRAN

I need the data to be saved. Why does Query() method wrap the actual query in a transaction and how can I prevent that?

Thanks

I do know that when IP queries, the first query call will be to create the schema, the second query call with actually execute the sproc. This post has a little info on that.... When you test the form, does your sproc execute?

Sorry, I don't know what do you refer to as "IP Query" and can't find any similarity between my case and the one in your posting.

Actually, the post I refer to, the second entry, he refers to the behavior of calling the stored procedure twice, once to generate schema. By "IP Query" I meant "InfoPath Query". I apologize for being unclear. What happens when you run your form and it actually calls the stored procedure? Is your data saved to the database?

Thanks for clarification. When I publish the form on sharepoint and fill it, clicking on save button (uses the code specified above), will put implicit transaction before and rollback after stored procedure call.

So when you publish the form and fill it out, the data is not saved to the database, is that correct?

I understand what you are saying about the implicit transaction and rollback, but in my experience InfoPath only does this the first time it makes the database call, to generate the schema, then it runs the procedure again, without the rollback.

Yes, It doesn't save the data.

It always puts rollback after stored procedure call.

You can workaround the rollback by adding a begin transaction/commit transaction to your query command or the stored procedure.

I can't because the outer implicit transaction definition will cause the inner transaction to be rolled back.