?
Solved

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
5
Medium Priority
?
758 Views
Last Modified: 2012-05-06
Hi,
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.

Problem:

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")

Question:

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
0
Comment
Question by:mchestnut26
  • 4
5 Comments
 
LVL 8

Expert Comment

by:MelMc
ID: 24211232
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?
0
 

Author Comment

by:mchestnut26
ID: 24211574
Thanks MelMc,

Ok,
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
0
 

Author Comment

by:mchestnut26
ID: 24215654
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
      submission.

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
     problem
   - 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,
Mark
0
 

Author Comment

by:mchestnut26
ID: 24215697
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
0
 

Accepted Solution

by:
mchestnut26 earned 0 total points
ID: 24227931
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).
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 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