InfoPath Dev

Just wondering if anyone can point me at how to do this?  I am unable to create a connection that will let me do more than query the DB. 

From this post by Hilary:

http://www.infopathdev.com/blogs/hilary/archive/2009/10/13/use-dbxl-submit-with-existing-sql-data.aspx

I read the last paragraph:

An Alternate Route

For the programmer, or if you don't mind the maintenance costs of adding code to your forms, there are other alternatives. For example, you can use custom code for your submit that will run a stored procedure or SQL statement to delete any rows that have the same ID as the data loaded into your form prior to the web service submit. This works well for data that has been using unique identifiers for keys. 

 Which makes me think it can be done but my sampling does not make it readily apparent.  I have tried this:

  string deleteTableQry = "DELETE FROM [dbo].[MyTable] WHERE MyKey='" + someKeyVar + "'";

            AdoQueryConnection myAdoQueryConnection =
                 (AdoQueryConnection)(this.DataConnections["DB_CONNECTION_TO_MYDB"]);
            string oldCmd = myAdoQueryConnection.Command;
            myAdoQueryConnection.Command = deleteTableQry;
            myAdoQueryConnection.Execute();
            myAdoQueryConnection.Command = oldCmd;

 The command runs but doesn't seem to change anything in the DB.  

Anyone with suggestions?

Thx,

Rob

 Can you run a SQL trace to get some insight into what might be going wrong? I know with stored procs we have to set implicit_transactions off to get them to execute correctly from IP.... but running a trace should help you see what's going on.

Trace shows me that the commands are running on the server, but nothing is changing.   I copy the SQL out of the BatchStarting and run it in SSMS and the row will delete.  The results of my sql in the trace look like:

SQL:BatchStarting: set implicit_transactions on SET NO_BROWSETABLE ON  (then a batch completed)

SQL:BatchStarting: DELETE FROM [dbo].[MyTable] WHERE MyKey='TestKey'  (then a batch completed)

SQL:BatchStarting: IF @@TRANCOUNT > 0 ROLLBACK TRAN (then a batch completed)

 If the SQL in the 2nd batch statement executes fine in a query window, I am assuming that the 3rd batch statement to rollback tran never happens... bit puzzling.  Any further thoughts?

R.

 I went and took a look at the form where I'd implemented this - I used a stored procedure (I was deleting from 3 tables), and set my data connection to execute it. Looking at my DB - there was nothing special about it either, though in the stored proc I do set implicit_transactions off.

I may have to go that route.   Have you ever done inserts via a SP also?  (I ran a quick test of a subsequent insert statement and same thing, no sign of it executing on the server.) 

Alternatively, does Infopath support using a straight SqlConnection in it's code behind?

Something along the lines of:

                using (SqlConnection connection = new SqlConnection(
                           "Data Source=dbsrvr;Initial Catalog=dbname;User Id=usr;Password=pwd;"))
                {
                    SqlCommand command = new SqlCommand("delete from table", connection);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }

 Sure, if you make the form full trust. The reason to use existing data connections is to not have to be full trust.

What are the implications of doing that?  I see that it needs to be "installed or digitally signed"... I don't have a cert, but the form itself is only intended for 1-2 admin users, is it hard to install it onto a system?

R.

Yes, you'd want to sign it or install it on each machine - if installed on each machine, you need to make sure the users install the latest when you have changes. Here's a link to using full trust forms w/ DBXL.

Going back to the SP suggestion... this seems to also not want to work.  There must be something very obvious I am just missing that is going on.  if I copy out the results of the trace and run in SSMS, it works but for some reason it won't work when comming in from my form.  Hilary can you see anything wrong with this query string?

    string query = "DECLARE	@return_value int, @poNum varchar(25) " +
                    "EXECUTE @return_value = [dbo].[spDeleteCodingElementsByPONum] @poNum = '" + currPONum + "';";
              
            AdoQueryConnection myAdoQueryConnection =
                 (AdoQueryConnection)(this.DataConnections["ELECTRONIC_PO"]);
            string oldCmd = myAdoQueryConnection.Command;
            myAdoQueryConnection.Command = query;
            myAdoQueryConnection.Execute();
            myAdoQueryConnection.Command = oldCmd;

I'd be more interested in the text of the stored proc. Are you setting implicit transactions off in it? 

That got it!  I had a literal transaction in my other SP I was invoking, this one I had missed adding that.  Thanks a bunch Hilary!

 Now to try to do inserts via a SP....

Update: Insert SP worked the exact same, but I didn't need the Implicit trxn off statement.  Removed that line, changed my SP to use insert vs. delete and added wayyyyyyyy more params and it seemed to work.