InfoPath Dev

I have a IP 2003 form that is used to request leave time.  This form is tied to 2 tables in SQL Server, a parent table (tblPendingLeave) with an Autonumber ID field as the Primary Key and a child table (tblPendingLeaveDetail) with an Autonumber ID field as the Primary Key with ParentID linked to the parent table’s ID field as a Foreign Key.    The form’s repeating table data source is tblPendingLeaveDetail.  It has its number of rows added programmatically using Visual Studio 2005 vb based on the number of days off requested.  So if the employee asks for vacation from 7/16 to 7/18, the vb code inserts 3 detail records where the user can enter additional information for each day.

When the user clicks on the Submit button, the following error is displayed.  (I am using an ADODataAdapter to do the submission).

System.Runtime.InteropServices.COMException

InfoPath cannot submit the form.

An error occurred while the form was being submitted.

The parameter is incorrect.

   at Microsoft.Office.Interop.InfoPath.SemiTrust.ADOAdapter2.Submit()

   at Microsoft.Office.Interop.InfoPath.SemiTrust.ADOAdapterObjectWrapper.Submit()

   at LeaveForm.LeaveForm.OnSubmitRequest(DocReturnEvent e)

   at Microsoft.Office.Interop.InfoPath.SemiTrust._XDocumentEventSink2_SinkHelper.OnSubmitRequest(DocReturnEvent pEvent)

By leaving the form open, I can see that the ParentID field of the first row in the repeating table is filled in correctly but the following rows are not filled in even though I have set the ParentID field to default to the ID field assigned to the new record in tblPendingLeave.  If the user clicks on the OK button to close the error message and then clicks on the Submit button again the data is submitted successfully.  With the form open, I can see that it has deleted all of the detail rows except for the first one and shows the ParentID of the record that was just inserted into the tblPendingLeave table and the ID of the first record inserted into tblPendingLeaveDetail.

If I remove the code to insert the rows into the repeating table and manually add them, the system will insert the records into the 2 SQL Server tables with no problem.

Below is a portion of the code I have tried.  I have tried both the new code and the old code shown below and both give the same problem. 

What am I doing wrong?

The code then adds 1 to the NextDate variable and cycles until it reaches the toDate, which is the Ending Date entered by the user.

do both of your tables have primary keys set?

Yes they both have Primary Keys set as Autonumber fields.  The tblPendingLeaveDetail child table also has a field named ParentID that is set equal to the ID Autonumber field in the tblPendingLeave parent table.  The Data Connection shows that it knows the 2 tables are Parent Child and are linked by ID field in tblPendingLeave = ParentID field in tblPendingLeaveDetail

 So I am creating records similar to below.

tblPendingLeave - Primary Key field = ID

If the parent records ID field = say 1230, then the child table -  tblPendingLeaveDetail would have records with a value of 1230 in its ParentID field and its Primary Key ID field set automatically by the SQL Server to the next number.

 As I stated earlier, if I remove the vb code that automatically add the repeating table entries and instead add them manually, then the Submit works every time.  I only get the error if use the code I showed to add the records.

Also, I should mention that if only 1 table entry is created by the code because the user only asks for 1 day off, then the Submit works each time as well.  The Submit only fails if there is more than 1 repeating table entry added via the vb code.