Insert Stored Procedure With Select - No records in select

Omkara — Thu, 28 May 2015 15:37:49 GMT

Hello

I am using stored procedure which does insert followed by a select statement.

I had really hard time to get the infopath form to use the stored procedure after countless hours of searching online and following things i was able to get the stored procedure to be called.

Strange thing is it does the insert but i do not see the result set of select query, the same stored procedure works fine in SSMS

I have Set IMPLICIT_TRANSACTIONS OFF in stored proc

Below is the code for stored procedure

IF NOT EXISTS(SELECT TOP 1 Id from testtable where Id= @ID)
    BEGIN
        --INSERT HERE
        INSERT INTO    [dbo].testtable(Id)
                            VALUES    (@ID)
        COMMIT
        Select Id from [dbo].testtable where Id = @ID

In Infopath form i use the below code and i get null

XPathNavigator myNavigator =
this.DataSources["testtable"].CreateNavigator().SelectSingleNode("//dfs:myFields/dfs:dataFields", this.NamespaceManager)

I really appreciate a quick response.

Thanks in advance

SQL Database Integration

Insert Stored Procedure With Select - No records in select