Code

Re: InfoPath - Calling Stored Procedure with parameters from C#

Hilary Stoupa — Tue, 26 May 2015 19:46:02 GMT

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!

Re: InfoPath - Calling Stored Procedure with parameters from C#

SAM2015 — Tue, 26 May 2015 19:22:24 GMT

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

Re: InfoPath - Calling Stored Procedure with parameters from C#

Hilary Stoupa — Tue, 26 May 2015 19:02:47 GMT

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.

Re: InfoPath - Calling Stored Procedure with parameters from C#

SAM2015 — Tue, 26 May 2015 18:48:40 GMT

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");

}

Re: InfoPath - Calling Stored Procedure with parameters from C#

SAM2015 — Tue, 26 May 2015 17:36:25 GMT

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

Re: InfoPath - Calling Stored Procedure with parameters from C#

Hilary Stoupa — Tue, 26 May 2015 17:14:36 GMT

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?

Re: InfoPath - Calling Stored Procedure with parameters from C#

SAM2015 — Tue, 26 May 2015 17:06:09 GMT

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!

Re: InfoPath - Calling Stored Procedure with parameters from C#

Hilary Stoupa — Tue, 26 May 2015 16:13:51 GMT

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

Re: InfoPath - Calling Stored Procedure with parameters from C#

SAM2015 — Tue, 26 May 2015 16:08:44 GMT

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;

Re: InfoPath - Calling Stored Procedure with parameters from C#

Hilary Stoupa — Tue, 26 May 2015 15:43:41 GMT

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.

InfoPath - Calling Stored Procedure with parameters from C#

SAM2015 — Sun, 24 May 2015 22:15:30 GMT

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.