InfoPath Dev

 Venturing into the realm of InfoPath to create a form which will be published to a SharePoint site. I have gone through a lot of learning of creating the form, using secondary connections and filtering its data, and just trying to be able to find a way for the form to dump the information into a SQL table. Probing around I found a solution which is suppose to work, by using a stored procedure to perform the insert and passing through the variables when called for by some button code (found here: http://www.bizsupportonline.net/infopath2007/use-receive-data-connection-execute-sql-server-stored-procedure.htm).

 The issue I am having with this solution is that it will trigger the Identity function on a ID column and make the table increment by one, but will not actually insert the record into the table.  When I manually call for the Stored Proc passing through the variables, the table captures the data and the tigger increments again just fine. You can see the gap of the "phantom" record from the form in the identity column: 1, 2, 4.

 I have tried using simple execute commands, to more involved commands trying to get the new data to stick in the table to no avail. If anyone can offer some advice on the issue, I would greatly appreciate it.

 Stored Proceedure:

 ALTER PROCEDURE [dbo].[INSERT_MACRO_DSX_ORDER_CLOSER]
@FORM_JOB_NUM varchar(6), @FORM_REP_NAME varchar(50)
WITH EXEC AS CALLER
AS
BEGIN
INSERT INTO WDIS.MACRO.DSX_ORDER_CLOSER (FORM_JOB_NUM, FORM_REP_NAME) VALUES (@FORM_JOB_NUM, @FORM_REP_NAME)
END

Button Code:

' Retrieve the name of the Office application to add to the database table
Dim dom As XPathNavigator = MainDataSource.CreateNavigator()

Dim parmJobNum As String = dom.SelectSingleNode( _
"//my:txtJobNumber", NamespaceManager).Value

Dim parmRepName As String = dom.SelectSingleNode( _
"//my:txtRepName", NamespaceManager).Value

' Retrieve the connection to the database table
Dim connection As AdoQueryConnection = DataConnections("DSX_ORDER_CLOSER")

' Save the original statement that is stored in the Command property
Dim originalCommand As String = connection.Command

' Execute the stored procedure
connection.Command = "BEGIN TRANSACTION DECLARE @FORM_JOB_NUM varchar(6), @FORM_REP_NAME varchar(50) EXEC [dbo].[INSERT_MACRO_DSX_ORDER_CLOSER] @FORM_JOB_NUM = '" & parmJobNum & "', @FORM_REP_NAME = '" & parmRepName & "'  COMMIT TRANSACTION  "
connection.Execute()

' Restore the statement of the Command property to its original value
connection.Command = originalCommand

The missing ID numbers are standard InfoPath behavior, believe it or not. I mention it at the end of this blog post. You'll find more info here.

But I wonder if I'm not reading your right - is your command actually failing to execute from InfoPath? That is, you aren't getting data in the table at all from this? In that case, again at the end of my blog post, Jimmy had mentioned that setting implicit_transactions to off inside the stored procedure allowed InfoPath to execute it.

 Interesting read, but not exactly what I am looking for (I think). The form it self has no bearing on what ID number is used or if used at all. I am looking for the form to insert two fields into the table, and let the table's Identity feature make the unique key for the row. An application using the data inserted later will care about the ID, just need the form to hand over those two items.

 Thank you for the quick response by the way. Still look forward to some insightful information.

So, I guess I'm having trouble understanding your question.

Is the stored procedure, when executed from you code in InfoPath, not inserting data into your database?

If that is the case, what version of InfoPath (including service pack) are you using?

Correct, when calling the proc from the code above the data is not written to the table, bit it does trigger the identity trigger to increment. When I call the proc from a SQL editor, the data is written and the identity increments just like it should, using the exact same command. InfoPath 2010, don't think it is SP'd yet.

And is implicit_transactions set to off in your stored proc?

 After seeing mention of it on the blog post you provided, I half heartly tried it last Thursday to no avail. But it was late in the day and I was ready to get out for a three day weekend.

 Fast forward to today, I tinkered around a little more and it looks like I finally got it working! Below is the updated Store Proc that I finally got to work:

 

USE [WDIS];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET NOCOUNT ON
GO
Set IMPLICIT_TRANSACTIONS OFF
GO
ALTER PROCEDURE [dbo].[INSERT_MACRO_DSX_ORDER_CLOSER]
@FORM_JOB_NUM varchar(6), @FORM_REP_NAME varchar(50)
WITH EXEC AS CALLER
AS
BEGIN
INSERT INTO WDIS.MACRO.DSX_ORDER_CLOSER (FORM_JOB_NUM, FORM_REP_NAME) VALUES (@FORM_JOB_NUM, @FORM_REP_NAME)
COMMIT
END
GO

Hey, great, and thanks for the update!

I won't suggest returning any results from the trigger unless you are ok with making changes to the code when the feature is deprecated. as per the BOL the option will be deprecated in the future versions.