Topic: Inserting and Updating

caldew    -- 10-25-2012 @ 1:25 AM

I am working with 2 Access DB tables:


A key field in both tables is the JobStatus (Current, Backlog, Completed etc).

When I post a JobProgressTable transaction, I do an INSERT to create a new record in JobProgressTable and I also do and UPDATE to update the JobTable with an amended or new status.

This works for the first job progress transaction, but fails on the next one (for the same job number), with the error message:
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.  
The error occurred in C:\Inetpub\\web\content\EquipmentMaintenance\viewMaintenanceJobTrans.cfm: line 80

78 :     <cfelse>
79 :     NULL
80 :   </cfif>
81 :   )
82 :   </cfquery>


SQL    INSERT INTO mJobProgress (ID, JobNumber, ReportedByUsername, ItemID, ItemDescription, ProblemDescription, PriorityCode, ReportDate, StatusID, WorkDoneComment) VALUES ( 71 , 71 , 'External Engineer' , 'CF0000' , 'General Maintenance Request' , 'This is a test job by ARA. ' , 'I' , '23 October 2012' , 'Completed' , 'New status changed to Backlog' )  
DATASOURCE   calderfoods
SQLSTATE   23000


The UPDATE to the mJob table is working but the INSERT to the mJobProgress table is failing.

Is it legitimate for me to do and UPDATE on one table followed by an INSERT on another table one after the other on the same page?  If not, please can you suggest an alternative way of dealing with this essential task (i.e. changing the original MJob status and writing an new mJobProgress transaction record?

Thank you in anticipation.


EasyCFM.COM ColdFusion Forums :