Infopath 2003 with SQL Server 2000 DB - Need to update single table in Main Datasource or make secondary data source repeating
Posted on 2009-04-22
Is there any way in Infopath 2003 to have a secondary data source act as a repeating control? Or allow submission to only one table in the main data source
Below is what I am trying to accomplish:
1. I have and infopath form that submits to an SQL Database - This works great, there are 5 tables in the database that get written to. Everytime a new report is completed the "Rep ID" field gets incremented as it is the primary key.
2. I now need to add the ability to update thes "Reports" once they are already submitted. I have created an "Updates" table in the SQL database
- This table also has a "Rep ID" field that references the main table, in the relationship I removed the check box for "Enforce relationship fir replication". I had to do this before I was able to add data to the table from submit in Infopath.
3. I have added the ability to choose a completed report from a drop down list box that points to the database via a secondary datasource. This list box is bound to the Main Datasource "Rep ID"
- When choosing a report number from this drop down list box it populates all the already completed infomation (all tables except the new update table I created)
4. Once a report is selected to be updated here I have a command button that switches to a view that only has the Entries for the new Update Table (The "Rep ID" selected from the dropdown is passed on submit to the "Rep ID" for the Updates Table.
- Once the info is entered in the form a Submit button will (should) add these valuse to the Updates table on the database - but it doesn't.
I get the following error: Infopath cannot submit the form. An error occurred while the form was being submitted. The value of an ID key or timestamp has been changed. Id keys and timestamp columns are automatically generated by the data source and cannot be changed.
I'm pretty sure my problem is that I need to use the main data connection to populate all of the already completed fields (Each field needs to be bound to the main datasource as it is "repeating", because of this the "Rep ID" with is the Primary Key on the main table will not allow it to be updated (I dont want it to update anything but the new "Updates Table")
Is there a way to populate these fields with a secondary data source?
- when I try to change binding I get "You have chosen to bind a non-repeating control to a repeating field or group. This control will be bound to the first item in the repeating field or group."
If I cant do above is there a way to tell INFOPATH on Submit to only submit to the one "Updates" Table, even though the main data source has all tables selected?
Also I am not a programmer in any way and have therefore not done any coding on the submit button.
Thank you to anyone who can help with this, I joined up just for this.