EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Inserting and Updating

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Inserting and Updating

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

caldew
10-25-2012 @ 1:25 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 74
Joined: Aug 2005

Hi

I am working with 2 Access DB tables:

mJob
mJobProgress

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\calderfoods.co.uk\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
VENDORERRORCODE   -1605
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.

Ronnie


Website Designed and Developed by Pablo Varando.