SQL Database Integration

Re: Unspecified Error when submitting

geoffc — Fri, 22 Jan 2010 15:01:27 GMT

I am not sure if this matters but I am publishing the form to MOSS 2007 as a content type.

Re: Unspecified Error when submitting

Hilary Stoupa — Fri, 22 Jan 2010 13:54:59 GMT

Hmmm. It is odd, I followed the same steps with a simple table for testing, and do not get an error, even if I don't have my form set to update. It sounds like you have a work-around (manually modify the existing XML), but I hope you don't have too many existing forms that have to be updated.

One thing you could check would be the max version to upgrade in the manifest.xsf file. Save your form as source files, and open manifest.xsf in a text editor. Make a note of the current version number:

<xsf:xDocumentClass trustSetting="automatic" solutionFormatVersion="2.0.0.0" dataFormSolution="yes" solutionVersion="1.0.0.5"

Then, toward the bottom of the file, look to make sure your max version is correct:

<xsf:documentVersionUpgrade>
<xsf:useTransform transform="upgrade.xsl" minVersionToUpgrade="0.0.0.0" maxVersionToUpgrade="1.0.0.5">
</xsf:useTransform>

However, as I mentioned, when I tested locally, even if my template is not set to upgrade, I cannot reproduce this issue....

Re: Unspecified Error when submitting

geoffc — Fri, 22 Jan 2010 13:10:41 GMT

Hilary, I really appreciate the offer (as well as all of your advice), however, I am sure I cannot post the template and xml forms due to their sensitive nature.

I was however able to replicate the issue with the simple template I created so it does not look like this scenario is specific to my one template.

I connected the template to my SQL table and unchecked one of the columns in the "Data Source Structure."
I dragged some controls onto the form and added a button to submit to the main data source.
I then published the template and created and submitted a form to SQL.
I went back into the design template and modified the data connection to check the column that was unchecked in step 1.
I re-published the form(with forms set to automatically update).
I opened the form I previously created in step 3 to try and make an update and submit the change, but I get the "Unspecified Error"
If I go into the XML and add the string I posted previously, the form submits the change OK. If I go and modify the template again to uncheck the column, I get schema validation errors when I re-open the form.

Re: Unspecified Error when submitting

Hilary Stoupa — Fri, 22 Jan 2010 04:24:21 GMT

If it isn't propriatary, can I see:

the template
an xml created from the old template
an xml create from the current template

If not, I understand, not everyone is building stuff they can post publically. If you can, there is a field under the Options tab where you can attach a file or zip folder.

Re: Unspecified Error when submitting

geoffc — Wed, 20 Jan 2010 12:47:13 GMT

Yes, the form is set to upgrade automatically, however, I think that is my problem. The form has the new field listed in the <dfs:queryFields> and <dfs:dataFields>, but it is missing the string below. I am guessing this is because the form data had already been submitted to the database prior to the upgrade with the new column.

<s:AttributeType rs:number="29" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Database" rs:baseschema="dbo" rs:basetable="Table" rs:basecolumn="Column" name="Column"><s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/></s:AttributeType>

As soon as I insert this string into the XML the form submits fine. I tried removing the column from the form by deselecting it in the data source, but then when I push out the update, I get schema validation errors since it looks like the column stays in the <dfs:queryFields> and <dfs:dataFields>

Re: Unspecified Error when submitting

Hilary Stoupa — Tue, 19 Jan 2010 22:22:58 GMT

Is your form template set to update existing xml? Under Tools / Form Options / Versioning you can set the template to automatically upgrade existing xml. That way when you open the xml file with the new template, it will get new fields....

Re: Unspecified Error when submitting

geoffc — Tue, 19 Jan 2010 20:38:32 GMT

Well, after doing a lot of squinting at XML, I was finally able to find the culprit.

A column was added to one of the tables after my form had already submitted to the database. The form template was updated to reflect this new column, however, on the form that had been submitted, the following line was missing for the new column:

<s:AttributeType rs:number="29" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Database" rs:baseschema="dbo" rs:basetable="Table_Name" rs:basecolumn="columnName" name="columnName"><s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/></s:AttributeType>

Is there an easy way I can remedy this situation outside of modifying each form that is missing this line in the XML??

Re: Unspecified Error when submitting

geoffc — Tue, 19 Jan 2010 17:09:34 GMT

Hi Hilary,

Yes, I was able to create a simple template that just submits to that one table and it worked flawlessly. The trigger was working as expected as well. I am still experiencing this same problem and am unable to submit any changes to this one table. I tried it on different machines and still have the same result.

Re: Unspecified Error when submitting

Hilary Stoupa — Wed, 13 Jan 2010 02:13:31 GMT

I guess, just to continue down the path of troubleshooting, have you tried to create a new (simple) form off the same data table? See what happens with a new template? What about different machines? Same issue on all machines?

Re: Unspecified Error when submitting

geoffc — Mon, 11 Jan 2010 13:18:40 GMT

Just tried disabling the trigger on the table and it still has the same unspecified error. Any update to all the other tables works fine though. I am just completely lost with this now.

Re: Unspecified Error when submitting

Hilary Stoupa — Fri, 08 Jan 2010 21:36:07 GMT

I have had trouble in the past with a trigger on a table IP submits to. Temporarily remove the trigger and see what happens....

Re: Unspecified Error when submitting

geoffc — Fri, 08 Jan 2010 16:05:53 GMT

So I just looked at the XML in notepad and I noticed that the table and its values are listed twice. Once at the top and once at the bottom. The values at the top reflect the changes that I made, whereas the values on the bottom reflect the values before the changes. Is this normal??

Re: Unspecified Error when submitting

geoffc — Fri, 08 Jan 2010 12:36:43 GMT

There are a total of 8 tables, but I confined the issue to just one of them by attempting to update the values in all of the tables.

The only thing special about this one table is that there is a database trigger in SQL that copies the existing row of data when an update is made and inputs it back into the table. I did an update in SQL to this same row of data I was trying to update through InfoPath and it worked just fine. Not sure how else to troubleshoot this as the error is very vague. Any help is greatly appreciated!

Re: Unspecified Error when submitting

Hilary Stoupa — Thu, 07 Jan 2010 23:21:01 GMT

What an annoying error! While I can't tell you what it means, I would suggest trying to create a new form, just quick and dirty, that also submits to the same table, and see if it works correctly. That may help you start deciphering the issue.

Is the main data source complex? Or just a single table?

Unspecified Error when submitting

geoffc — Wed, 06 Jan 2010 16:36:10 GMT

Hello All,

I am getting this error when I am trying to make an update to a value on my form which is connected to SQL. As you can see, it isn't very helpful. Is there anything specific this error is related to?

InfoPath cannot submit the form.
An error occurred while the form was being submitted.
Unspecified error