Code

Re: Code behind for button to update to a SQL table

imstac — Fri, 19 Mar 2010 15:38:48 GMT

Thanks Jimmy. So I took your advice and added the controls to my ProjectForm form and then tried submitting in my other form. When I tried to open the ProjectForm that was submitted I got a helpful error. The problem was that the new fields in which I added in the ProjectForm had the first letter capitalized. When I updated the xml in the rules in my other form I used all lowercase letters.

Thanks for your help again!

Re: Code behind for button to update to a SQL table

Jimmy — Fri, 19 Mar 2010 00:34:36 GMT

Hello again Stacie,

Could you try going to the Documents tab for the ProjectForm in DAT and opening up one of the newly submitted forms to see if the new field values are present in them? Here I'm supposing you added controls to the view to show their value. That's one way of trying to find out where the disconnect is happening.

Just one note - you can update a doctype's XSN - even if you've added new fields - without deleting and recreating the doctype. You can just attach the new XSN in the General tab and click Save.

Re: Code behind for button to update to a SQL table

imstac — Thu, 18 Mar 2010 16:36:20 GMT

Attached is my Job Survey form.

Re: Code behind for button to update to a SQL table

imstac — Thu, 18 Mar 2010 16:34:38 GMT

Hey Jimmy,

Sorry to bother you again but I am trying to add some additional fields to be populated in my Projects table and I can't get the data to populate in the additional fields. Here's what I have done so far:

Opened the ProjectsForm in design mode and added two new fields to my main data source; Market and Location
Saved the form.
Went to the DBXL Admin Tool and deleted the ProjectsForm form and then readded it.
Went to the database tab and mapped my Location and Market fields in my database to the form fields. Saved my configuration
Opened my Job Survey Infopath form and updated my data connection to the updated template.xml file.
Right-clicked on the Create Project button and updated the my Set XML to Submit rule as follows:

concat('<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:ProjectsForm:-myXSD-2010-02-27T08-03-19" href="manifest.xsf" solutionVersion="1.0.0.3" productVersion="12.0.0" PIVersion="1.0.0.0" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-02-27T08:03:19" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"><my:name><![CDATA[', tbox_jobnamepost, "]]></my:name><my:status>3</my:status><my:branch_office>", ddl_branchoffice, "</my:branch_office><my:owner>", ddl_ownername, "</my:owner><my:location>", tbox_location, "</my:location><my:market>", ddl_Market, "</my:market></my:myFields>")

I tested createing a new project in my Job Survey form and it updates the record but it doesn't update the additional fields I added; only the ones that you originally coded for me.I checked the messages in my eventviewer and did not see any errors.

Am I missing a step?

Re: Code behind for button to update to a SQL table

Jimmy — Fri, 05 Mar 2010 00:38:26 GMT

Hey Stacie,

I'm glad you got it straightened out. Happy DBXL'ing! :-)

Re: Code behind for button to update to a SQL table

imstac — Thu, 04 Mar 2010 17:40:31 GMT

Hey Jimmy,

I figured it out. I did a search on the InfoPath Dev site and found a post that said the error happens when there is C# code specified even if there is no code within the form. I went to Form Options | Programming and clicked the Remove Code form, saved and reposted to doctype in DBXL and it worked.

Yippeee!

Thanks so much for all your help...you're a lifesaver!

Re: Code behind for button to update to a SQL table

imstac — Thu, 04 Mar 2010 17:13:08 GMT

Jimmy,

I have added the ProjectForm.xsn and mapped it to my project table as instructed. I also added my other form (Job Setup Form) as a doctype and mapped its fields. When I try to create a new document from the Job Setup Form doctype I get the following error:

InfoPath cannot open the selected form because of an error in the form's code.
One or more required components of InfoPath or the form template cannot run because of a security restriction.
Contact your system administrator or run Setup again to confirm that .NET Programmability Support is installed on your computer.
Request for the permission of type 'Microsoft.Office.InfoPath.InfoPathPermission, Microsoft.Office.InfoPath.Client.Internal.Host, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' failed.

   at Microsoft.Office.Interop.InfoPath.SemiTrust.XDocumentsCollectionWrapper.NewFromSolution(Object varURI)
   at AdminTool.Configuration.btnNewDocument_OnClick(DocActionEvent e)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._ButtonEventSink_SinkHelper.OnClick(DocActionEvent pEvent)

Do you think this has something to do with the setup we did to create a new project? This coding shouldn't be accessing my individual computer, correct?

Re: Code behind for button to update to a SQL table

Jimmy — Thu, 04 Mar 2010 14:46:19 GMT

You should add the ProjectForm.xsn form as a doctype in DBXL, and map that to your project table, but you don't need to add or use its template.xml file as a secondary source in your Job Setup Form (the data source called CreateProject in your form right now).

Your rules build the XML to submit and submit them using the SubmitDocument-Projects data connection.

Does that make sense?

Re: Code behind for button to update to a SQL table

imstac — Wed, 03 Mar 2010 14:53:23 GMT

Thanks Jimmy. So if I'm not using the secondary source (ProjectForms.xsn) How do I do the mapping in DBXL? Do I use Job Setup Form twice?

Re: Code behind for button to update to a SQL table

Jimmy — Wed, 03 Mar 2010 03:12:04 GMT

I'm also uploading a copy of your form with the changes in it. Remember to update the concat() formula to include your project name.

Re: Code behind for button to update to a SQL table

Jimmy — Wed, 03 Mar 2010 03:10:59 GMT

Hello Stacie,

I'm using the concat() formula in the second action of the Set Values rule on the button (see attached image).

If all of the requisite values are already in your main data source, there isn't even any need to create a secondary data source to hold them. You can just set up the concat() formula to pull the field values from the main data source. In this situation, you would hardcode the Status value of 3 into the concat() function and omit the rule that sets it in the secondary data source. The resulting concat() formula would look like this:

concat('<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:ProjectsForm:-myXSD-2010-02-27T08-03-19" href="manifest.xsf" solutionVersion="1.0.0.3" productVersion="12.0.0" PIVersion="1.0.0.0" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-02-27T08:03:19" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"><my:name><![CDATA[', "ADD THE FIELD FOR YOUR PROJECT NAME HERE", "]]></my:name><my:status>3</my:status><my:branch_office>", ../my:Job_Information/my:ddl_branchoffice, "</my:branch_office><my:owner>", ../my:Job_POCs/my:ddl_ownername, "</my:owner></my:myFields>")

I wasn't able to find a field in your form that would seem to be a place for the user to type in a master project name, so that's marked in red with a placeholder in red above. You would need to replace this (quotes and all) with whatever field you're using for the project name.

Re: Code behind for button to update to a SQL table

imstac — Tue, 02 Mar 2010 20:06:12 GMT

Hey Jimmy,

Have a couple more questions for you.

"I'm using a rule on the button that utilizes the concat() formula to build the XML to submit using the values in the ProjectsDom data source. That full formula looks like this:"

I looked at the rules in your button and don't see where you added the concat() formula.

Also, on my main form, the fields that are collecting the Name, Branch and Owner are located in the Main source, do I need to pull the fields from the secondary source? If so, how do I handle the fields for my Branch and Owner as they are dropdown lists that are being populated by external data? I've attached my forms so that you can see what I mean. In my Job Setup Form, the Job name textbox value will populate the project name, Owner Name dropdown list value will populate the owner and the Branch Office dropdown will populate the branch field.

Thanks so much for your help on this!

Re: Code behind for button to update to a SQL table

Jimmy — Tue, 02 Mar 2010 02:03:48 GMT

Open up ProjectForm.xsn in design mode, and from the File menu select Save as source files... and choose a directory for the extracted source files.

Then in your main form, go to the Add Data Connection dialog, select the option to add an XML secondary data source and select the template.xml from the directory where you extracted ProjectForm.xsn's source. Use all the default options "Store the file as a resource, query on open" but give the data source a more meaningful name than just "template."

Re: Code behind for button to update to a SQL table

imstac — Mon, 01 Mar 2010 22:51:04 GMT

Thanks Jimmy.

Can you explain how I do this part?

"I added the template.xml file from ProjectForm.xsn's source as a secondary XML data connection in SubmitProjects.xsn. This allows you to store the project values in a location outside the main data source before they are submitted."

Re: Code behind for button to update to a SQL table

Jimmy — Sat, 27 Feb 2010 11:25:02 GMT

Please observe the forms in the attached ZIP. Please add both forms as new doctypes in DBXL. Use the doctype name ProjectForm for the ProjectForm.xsn file, and any name of your choosing for the other form.

The ProjectForm will have a mapping to your Projects table in the database (please add a mapping for it)
The other form will demonstrate how to submit forms to this doctype without submitting the main data source. Please open this form from DBXL, fill in a few values, and click Create Project. This should cause a form to be submitted to DBXL.

Now please open the SubmitProjects.xsn form in the InfoPath designer to see how it works. There are several things to note here:

The forms are being submitted using a Receive (instead of Submit) data connection to DBXL's SubmitDocument web method. It's possible to use either type of data connection, but using a Receive connection offers the benefit of being able to set the arguments using rules (instead of binding them to fields), and easily accessing the result and errors returned from the webmethod.
When setting up the SubmitProject data connection, I specified "ProjectForm" as the doctype name so it will always have this value by default, and unchecked the "Query data when the for is opened" option.
I added the template.xml file from ProjectForm.xsn's source as a secondary XML data connection in SubmitProjects.xsn. This allows you to store the project values in a location outside the main data source before they are submitted.
I'm using a rule on the button that utilizes the concat() formula to build the XML to submit using the values in the ProjectsDom data source. That full formula looks like this:

concat('<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:ProjectsForm:-myXSD-2010-02-27T08-03-19" href="manifest.xsf" solutionVersion="1.0.0.3" productVersion="12.0.0" PIVersion="1.0.0.0" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-02-27T08:03:19" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"><my:name><![CDATA[', xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:name, "]]></my:name><my:status>", xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:status, "</my:status><my:branch_office>", xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:branch_office, "</my:branch_office><my:owner>", xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:owner, "</my:owner></my:myFields>")

Though it may look intimidating, all I've done here is take the XML from the template.xml file of ProjectForm.xsn's source, removed all the line breaks, and split it up in the location where the values go. The XML is surrounded by single quotes to represent it as string values, and this is interspersed with XPath to ProjectsDom's values (I added these using the Insert Field or Group... feature). Notice that I've also removed all of the xsi:nil="true" attributes, and added <![CDATA[ and ]]> before and after the location where the name value goes, to prevent any problems with XML symbols that might be present in the name.

Please give this a try, and let me know if you have any further questions.