InfoPath Dev

Hello,

I recently used the migration tool to move my form, files and mappings to a new workstation.  I backed up and restored the DB to this new workstation as well.  Everything looks good, the form, the database tables and diagram, the DBXL mappings.  But when I create a new document and submit it, I get the following error:

I checked the column in the table that has Identity (it's a form ID column that is to increment by 1 as the docs are added by the form) and the Identity and Spec is set to Yes with 1 and 1 for Increment and Seed. I read some other SQL posts about manually setting the Identity_insert to on but I'm not sure if that's complete solution or a 1 time connection thing and would have to be reset every time someone wants to shred a doc.  Any ideas?

Thank you!

Phil.

Hi, Phil:

In your mapping for your form, are you trying to shred form data to that Identity column? Open your DocType configuration in DAT, check the Database tab and see if you have a column mapped to the column.....

Hi Hilary,

The Form_ID column is mapped to DBXL::DocID.  My mapping is LOOOONG and I created months ago so I don't remember much of this particular field as it relates to the form.  I hope that helps. I can give you screenshots if that will help...

Thanks,

Phil.

Sorry Hilary, I didn't quite understand your note but I think I know where you're going with it ... Yes, the Form_ID is the primary key that is mapped to the DBXL node path but it's also set up as a foreign key.  there is a column in every table called Form_ID.  This worked initially as a good way to be able to correlate all the information in the nested and repeating sections and tables.  Hope this bit of info helps; I'm at a loss as it looks identical to the mapping and form that works from my laptop.

Thank you!

Phil.

What I mean is -- in your database, the column Form ID -- do you have that set as an identity column? In SQL manager, if you right click the table and pick Design or Modify (depending on your service pack) the table opens in design mode. If you select the column, the column properties window will show the properties for it. Look for the Identity Specification -- you want to make sure that is set to 'No'. You are providing the identity with the DBXL DocID. So your mapping is probably fine -- it is your SQL table that is most likely the issue...

Gotcha.  Yes, the column Identity properties were set to 'Yes'.  I had thought this was required so that the form would maintain a relationship between all the tables (repeating sections, tables) and the form.  If I reshred the doc with the Identity to 'No', I get an error 'I9999: An unhandled exception was thrown: Cannot insert the value NULL into column 'Form_ID', table 'so and so'; column does not allow nulls. Insert Fails.'

Thank you for your help.

Phil.

*** I just checked my original form and database on my laptop and while it was working before the migration, it does not now.  I get the same error as I do on my new workstation.  I'm not saying the migration caused it to fail, but it was working before hand and after I migrated it I was working exclusively on the PC.

If there's anything you need me to run/test/screenshot, let me know.

And form id is mapped to DBXL Doc Id for all the tables in your mapping? 'Cause you shouldn't be getting a 'NULL' value for Doc Id....

Mapped to DBXL::DocID in the DAT Mappings?  I do not have a column name for DocID, i was using SQL to generate incremental numbers and then feeding that info to the DocID so that I can view it in the DAT.   

I am officially completly baffled. Let me try to explain what I mean a bit better, and perhaps that will help us both.

When doing your database mapping in DAT, on the left side we have a table or column, then on the right, the xpath to a form field that will provide the value for the column. We also have the option of selecting DBXL Mapping Tokens like DocId, which is a unique identifier generated and used by DBXL for identifying individual documents (filled out forms). This is why I don't understand what you mean when you say:

i was using SQL to generate incremental numbers and then feeding that info to the DocID so that I can view it in the DAT

A DocId will be generated for each XML file submitted to DAT. That is what is nice about using DocId for the unique identifier for your form -- it will remain the same for the life of the XML file. Does that make sense?

So, in your database mapping, I'd expect to see that you had your Form_Id column selected on the left, and DBXL:DocId selected on the right, storing the DBXL generated Doc Id in you data table....

Hi Hilary,

I'm sure I'm not articulating well enough and that's confusing us both.  I do have it setup like you say, a Form_ID column mapped to the DocID.  In my DAT Mapping and DB Table design, there is a column in every table called Form_ID this is to associate all the repeating tables and sections to that uniqie Form_ID. That is why there was an incremental integer associated with the Form_ID in my DB so that if one doc/form had multiple  lines in a repeating section, they would all be associated to that form name by it's Form_ID.  I hope that made sense, it did in my mind ;-)

I had thought that the Doc_ID was getting that Form_ID information from the database.  So that's where I was confused and mis-spoke.  I can send you screenshots of the DAT Mapping if that will help.

Thanks again for your help...

Phil.

Yes, please do send screen shots. I want to be sure you have foreign keys set for your child tables... :)

The DBXL DocID is generated by DBXL and is never obtained from the user database.  The top level table can use the DBXL DocID or an Identity column as the primary key.  If using an identity column, you can still store the DocID in the table, but it will not be the primary key.