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
Last Modified: 2012-05-06
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.
- Mark
Question by:mchestnut26
    LVL 8

    Expert Comment

    From my experience when you have multiple tables in your Main Connection you have no choice but to submit to all or none. I am sure that this could be done in code, but I try to avoid that. Could you possibly create another form that has a query so that the user can look for the proper item in the Update table and then submit changes/new items?

    Author Comment

    Thanks MelMc,

    I did try this solution by creating a new form from the database connection, I only selected the update table. This way I was able to add records to the update tables via the form.
    The problem however still remains that I am unable to auto fill the form so the user can view the actual data for the "Rep ID" number they chose from the drop down.

     Is there a way in Infopath other than using Repeating controls (which I can only get the main data source to work with) to display the current data for the chosen "Rep ID" from the drop down list box?
    I only want to submit to the "Update" table (Primary Data Source)  but view record in the other table Secondary Data Source.

    If setting up a query will work, how do I go about doing that?

    Thanks Again

    Author Comment

    Hi Again,
    Upon further research I don't think binding a secondary data source to a field in this way is possible.
    Can any of the below processes possibly work for me? If so could someone give me a nudge in the right direction?

    1. Setup custom code on a command button to populate fields in the SQL Database.
       - I only have 7 fields in this table.
       - with this method (if possible) I can have the form load with values from the main data source but
          have the submit button only pass these 7 fields that the user will fill in to the database.
       - with this I should no longer get the "...ID key or timestamp has been changed..." error upon

    2. Setup a command button in Infopath to open an entirely different form based on the value the
         user selects from the drop down list box.
       - The initial form can still have the field bound to the main data source as it will never submit from
          this form
       - The user then clicks the new command button that opens a different Infopath form and passes
         the value selected in the dropdown to the "Rep ID" field on the new form
       - The new forms main data source will only be the "Update" table so submitting will not be a
       - Also since the "Rep ID" is taken from the Initial form and passed in it will not allow user
         modification which will insure it matches the "Rep ID" of the other tables for reporting later on

    Thank you for any help you can provide,

    Author Comment

    Also for Clarification, the user needs to be able to see what is already in the database so they can be sure they are updating the correct "Rep ID"
    - Mark

    Accepted Solution

    I have resorted to creating a new form just for updates. I had this form auto fill a few fields from the main report so the user when know they are updating the correct one.

    It seems to me that this is the only way to add records to only one Table in a database (Without using Sharepoint).

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now