InfoPath Dev

Hello this is my first time posting to this forum. I have an infopath form that is connected to a SQL database and I am using three tables in the database for my Main connection. I then have a repeating section that has a hyperlink control in it that the user clicks to open a document. This works just fine, what I need to do is track the number of times that these hyperlinks are clicked. I have gotten some simple code to work to select the first instance of this field to get the need identification. The problem comes in when I am trying to submit this data to my database because I am breaking the validation for the rest of the tables in this connection. Is it possible to run an Insert query to just one part of the data connection? Thank you for your help in the matter, I look forward to your thoughts. Sorry if this isn't in the correct area I wasn't sure what this fell under. Thanks, Mark

 Hi - here is an older thread that talks about using the context changed event to track if a hyperlink has been clicked - I don't understand from your post if you have that part figured out or not. I don't understand the second part of your issue - what do you mean "breaking the validation"?

Hi hillary, Thanks for the quick response. I did use the that old thread to get the on context changed part working. As to the breaking validation part of my question. I am trying to update just one of the tables that I have connected via my SQL data connection. I am running an insert query to update with document number and the time stamp when they clicked hte hyper link. The error that I get when infopath tries to execute this command is the following "InfoPath cannot submit the form.\nThe form does not contain any new data to submit to the data source." I think that this is happening because it is trying to submit the whole form and not just the query that I am running. I guess what I am really trying to ask is how do you submit to just one table, in the case the lowest child table, in a SQL data source? Let me know if you need more information and again thank you for your help with this. P.S. the only other way that I though I might be able to do this is to have a dummy form that is just for submitting this information but I would like to avoid this if at all possible. Thanks, Mark

So - are you modifying the main data connection to execute this insert query? You can do this in code - but I'd create another receive data connection and modify that in code instead.

Alright I will give that a try and report back later on today. Thanks again for your help with this.

Hi Hilary, So I haven't gotten anywhere with this. I am wondering if you have any ideas as to inserting a record into my database when the hyperlink is clicked? I am not sure that I understand what you mean by "but I'd create another receive data connection and modify that". What do you mean by modify that? Any guidance that you can provide I would really appreciate. Thanks Mark

So as it turns out no matter what data connection I use I can't get the form to insert a new row into my database. I have even tried creating a stored procedure that does the insert in the database but that doesn't work either. I am stumped. Perhaps I am not fully clear on what I should be doing, would it help to see the code that I am using?

 Hi - sorry, I meant to reply earlier. The reason I suggest creating a data connection to your table is so that you can use that data connection for your insert - that way you don't have to make your form full trust.

To change the data connection's SQL command, try taking a look at this link:

http://msdn.microsoft.com/en-us/library/microsoft.office.infopath.adoqueryconnection.command(v=office.15).aspx

I haven't done this for a while - but I'm pretty sure you can set the command to your stored proc, for example, and execute it.

That is the method that I am trying to get to work but nothing ever inserts into the database. I know that the SP works through testing in SQL management studio. So i would like to show you the code I am using but I am having trouble formatting it in the post. How do I add formatting to these posts?

Oh, getting your code to paste in formatted here is such a pain. I think I used to paste from Visual Studio into Word or a rich text field in InfoPath. Just paste it into a text file and attach it under the options tab - I think you can attach .txt

Alright here it is, let me know if you see something that I don't. This is wrapped in the on_contextchanged event as well

 You've run in debug and verified your code is executing? Can you run SQL Server Profiler while you execute the connection? (in other words - off-hand it looks fine to me...)

I can but what am I looking for in the trace?

 That the stored procedure is what you'd expect and that it is executing?

So it looks like it is executing the SP but after it gets done it rolls the changes back for some reason. I am not sure why it would be doing this.