EXEC SP from Button

[INTP56]

I have a situation where I want to execute a stored procedure in SQL Server
2005 when a user presses a form button. I have a stored procedure which
simply writes a line into a table. If I run it from SSMS, it works fine.

I created a form from a data connection and also a secondary connection to
the same table (named SPConnection).

Drag data fields to data section as repeating table and created a button. In
the form code, I put the following in the click event.

XDocument.DataObjects("SPConnection").QueryAdapter.Command = "EXEC
dbo.WriteLineToSAK_Table";
XDocument.DataObjects("SPConnection").QueryAdapter.Query();

Now, I can manually insert lines into the table, so access to the table is
not a problem. But when I press the button, nothing happens on the infopath
side ... no error, just no apparent action. If I look at the database table,
no line has been inserted.

But, if I run the SP manually, or insert a line manually from the form, I
can see the identity field is not sequential.

That's telling me the SP is getting run, and an attempt has been made to
insert into the table which has been rolled back. I don't know how to debug
this because it works when I run the SP manually from SSMS.

All the examples I've found talk about how to do this to return data from
the SP, I haven't found any where I just want to exec a SP. Can anyone give
me a clue or point me where to look?

TIA, Bob

 

[S.Y.M. Wong-A-Ton]

It looks like you're using InfoPath 2003? I just wrote an article about this
very topic, but then for InfoPath 2007 (see
http://www.bizsupportonline.net/inf...ction-execute-sql-server-stored-procedure.htm).
Try adding a COMMIT to your stored procedure. I was having the same issue
you're currently having, and an explicit COMMIT did the trick.