InfoPath Dev

I am trying to execute a stored procedure from my Infopath 2010 two ways . And I get the errors pointed with arrows below. What am I doing wrong. 

using Microsoft.Office.InfoPath;
using System;
using System.Windows.Forms;
using System.Xml;
using System.Xml.XPath;
using mshtml;
using System.Data;
using System.Data.SqlClient;
using System.Data.Linq;

            AdoQueryConnection conn = DataConnections["tts"] as AdoQueryConnection;
            string originalCommand = conn.Command;
            string query = "EXEC Request_SP_Test '" + FileNbr + "'";  
            conn.Command = query;
            conn.Execute();
            conn.Command = originalCommand;
            RunQuery(query);   ---> Error The name "RunQuery" does not exist in the current context.

 Second Method:

 XDocument.QueryAdapter.Command = "EXEC FTS_User_Request_SP_Test '" + FileNbr + "'";  ---> Error The name "XDocument" does not exist in the current context.
 XDocument.Query();                                                                                                              --->  Error The name "XDocument" does not exist in the current context.                                            

 In your first method, if you remove the RunQuery(query) line, what happens? Off-hand, I'd assume conn.Execute is going to run the query. Your second method is using an older version of the InfoPath Object Model.

Hi Hilary, Nothing Happens, when I hit the Search button, the code does not bring back any rows back to the repeating table. Not sure what the issue is. The Data Connection named Request_Test is a secondary connection in the form. I am just trying to bring back the rows of data based on what the user selects as an Id on a clicked button. Thanks! 

XPathNavigator dom = MainDataSource.CreateNavigator();

string Id= dom.SelectSingleNode("/dfs:myFields/my:Id", NamespaceManager).Value

MessageBox.Show(Id);

AdoQueryConnection conn = DataConnections["Request_Test"] as AdoQueryConnection;  ---> Secondary Connection

string originalCommand = conn.Command;

string query = "EXEC User_Request_SP_Test1 '" + Id + "'";

conn.Command = query;

conn.Execute();

conn.Command = originalCommand;

 Can you run SQL Profiler and see if the stored proc is being executed?

Yes, I ran the profiler and it says:

 EventClass                       TextData

SP:StmtCompleted           SELECT  *   FROM   dbo.vRequest_Test where id = @Id

SP:Completed                   EXEC User_Request_SP_Test1 '3342671'

SQL: BatchCompleted      EXEC User_Request_SP_Test1 '3342671'

 But for some reason, the repeating table Request_Test is not  getting populated with the data on the InfoPath form. Let me know if you need more information. Thanks!

 And I'm sure you've done this already... but if in SSMS, you execute this command:

EXEC User_Request_SP_Test1 '3342671'

You get data rows back? When you created the data connection, did you use a table that returns the same columns as your stored proc? 

Hilary ,The sp was calling a view, but I changed that to a table and it now works...does a view cause this issue. Next is to feed this ID into another sp, that writes to a table. Thanks

HIlary, Part two is not working...after selecting an Id number from the first repeating table, then I want them to select a file number from that list using a select button. Once I have the filenumber then I feed it into another sp, which writes some more data to a second repeating table. I have used this code below, the SSMS profiler shows that the sp was executed. This time I use all tables and not views, but the sp does not insert a new row of data into the second repeating table after the sp has completed...not sure why its not wrting to the second repeating table...can you please take a look...Thanks!

exec  FTS_User_Request_SP_Test 43916   this works in SSMS.

public void CTRL27_7_Clicked(object sender, ClickedEventArgs e)
        {
            String FileNbr = e.Source.CreateNavigator().SelectSingleNode("@file_nbr", NamespaceManager).Value;
            MessageBox.Show(FileNbr);
            AdoQueryConnection conn = DataConnections["REQ_Test 2"] as AdoQueryConnection;
            string originalCommand = conn.Command;
            string query = "EXEC User_Request_SP_Test '" + FileNbr + "'";
            conn.Command = query;
            conn.Execute();
            conn.Command = originalCommand;
            this.ViewInfos.SwitchView("REQ");

}

 Try setting implicit_transactions to off in your stored procedure - I don't recall all the details on why, but I think this will get your sp to not roll back.

Hilary, Yes that worked...you are a genius...Thank you so much....

Good to know that worked - the genius label needs to go to Jimmy Rishe, tho, as he was the one who showed me that. :) All I did was manage to remember it....

Glad everything is working the way you need it to!