InfoPath Dev

Hi all. I am utilizing the QRules command ExecuteSqlQuery and it works great. But now its giving me some weird behavior? It says its an invalid command for only 2 rows of data that I'm inserting into sql. Obviously the command is good for the other 8 rows of repeating data. so I questioned the data itself, but when I take the SQL insert command to SSMS and run it. Works like a charm. I'm puzzled with this one. Can anyone please assist? Attached is the XML saved form and a screenshot. I posted this in another area and will remove it as it is best suited in the qRules forum.

I can't remove the other post but here is the link. http://www.infopathdev.com/forums/p/39064/119186.aspx#119186

Can anyone please respond? Seems like this might be an issue with qRules. :/

Hi - can you put the Errors node (/QdabraRules/ExecutionHistory/History/Error) from the QdabraRules data source on your form and send us a copy of the error that is displayed? Also, can you send us the syntax for your command?

Hi Joanne, and thank you for responding. My original post actually has the .xml file with everything you have requested. If you need the .xsn file just let me know. :)

Can you reply with the complete error details and attach your XSN? :) Thank you

Attached is the xsn. The button with the command is on the "View Changes" view and is called "Insert into ***" where it sets the submit_prd field to true, then that field runs the command for the RG contained in PRD. I ask that you remove the attachment or delete my reply if need be so that no one else can see it. The XML file I attached on my question above is the saved filed out form with the commands and the errors on the "zData" view for Rows 4 and 7 of the PRD insert. The error node is simply stating "Invalid Command". As I said previously the insert into statement works fine in SQL and works fine for the other 8 rows when ran in InfoPath utilizing the ExecuteSqlQuery command. Please let me know if you have any questions.

Thanks for sending your XSN. We will look into it and hope to have a solution for you soon. I'm afraid I don't have permissions to delete your attachment from your post. Will you be able to delete it? You can also email us directly at Support (at) Qdabra (dot) com. :)

Any luck thus far? :)

Hello CCBSMF,

I suspect that the reason your query is failing on rows 4 and 7 is that in both of those rows (and only these rows), the prd_vndr_part_desc field contains a line break.

I'm not completely sure why this is causing an issue and am unable to reproduce this error myself, but that seems like the most likely culprit.

If it's not important to preserve these line breaks, what I would suggest doing is using the normalize-space() function to turn these line breaks into single spaces when you build the SQL command:

concat("ExecuteSqlQuery /dsname=insert /sql=set implicit_transactions off INSERT INTO [table] (column) VALUES('", normalize-space(field), "' , '", normalize-space(otherField), "')") 

If there is a need to preserve the line breaks, I'm sure there is a way to make it work, but it will most likely be considerably trickier.

In any case please try the approach above and let us know if it makes any difference.

I know Jo-Anne is actively investigating this (I was out of the office for a few days) - but I wanted to ask if you'd tried running a SQL trace to see if it provided any insight? EDITED TO ADD: I see Jimmy replied and I hadn't refreshed the page! A SQL trace still might help pinpoint the issue...

Hi Jimmy and Hilary. It's always a pleasure to see you both. Hope your doing well. I have confirmed that running the SQL statement themselves in SQL as they were generated in InfoPath, runs in SQL perfectly and inserts all rows of data. Jimmy to address your concerns if the SQL field doesn't allow for such, it will just truncate that line at the point of the hard return. I have already addressed this for those fields and am doing something to ensure this doesn't happen again. Joanne, I will try again and utilize the normalize space command at the point of the command for insert to see is this helps. May not be able to do this until Monday Morning. I will update you all as soon as humanly possible and thank you for all of your efforts.

My Apologies for not posting back sooner. The problem was rectified with the normalize-space() command. Although, I must add that adding it to the insert statement did decrease performance, so I applied to the field before the insert was invoked. Thanks again for your help on this, everyone.