| Unique constraint violated [message #449343] |
Mon, 29 March 2010 04:42  |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
Hi all, hope you can help.
I have a form that is used to insert data into my database. It has a data block based on the table it inserts data into. To enter the data I am using a save button with some code behind it.
I know that, because of the data block, Oracle will automatically insert the data for me, without needing to use a save button, but I need this button.
The problem is that when I attempt to click the save button I get the error unique constraint violated on either the index or for a constraint that has been set up on my primary key field (HeatNumber). Is this because the save button is attempting to enter the data and Oracle is also trying to do it at the same time, thus having 2 identical records?
I have tried removing both the index and constraint and the data inserts, but inserts 2 identical rows, which is why I think both the save button and Oracle are doing the same thing.
Can I get around this by removing the data block and just using a save button? I thought I would ask incase it wouldn't work and I would then have to re-do it all again.
Thanks for any advice.
|
|
|
|
|
|
| Re: Unique constraint violated [message #449349 is a reply to message #449343] |
Mon, 29 March 2010 04:52  |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
The code is:
BEGIN
INSERT INTO adddatarequest (heatnumber,
requestedby,
description,
typeofquery,
querydescription,
documenttitle,
datacrossref,
datadespatched,
userfollowup,
additionalinfo,
attachedsql,
workcompby,
datecomp,
confirmedby,
confirmeddate)
VALUES (:heatnumber,
:requestedby,
:description,
:typeofquery,
:querydescription,
:documenttitle,
:datacrossref,
:datadespatched,
:userfollowup,
:additionalinfo,
:attachedsql,
:workcompby,
:datecomp,
:confirmedby,
:confirmeddate);
COMMIT;
END;
and I am using the button because it is part of the requriements for my system.
[EDITED by LF: reformatted code]
[Updated on: Mon, 29 March 2010 04:58] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Unique constraint violated [message #449358 is a reply to message #449349] |
Mon, 29 March 2010 05:01 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:Is this because the save button is attempting to enter the data and Oracle is also trying to do it at the same time, thus having 2 identical records?
Yes
Quote:Can I get around this by removing the data block and just using a save button?
Yes, but that's not the way to do it.
Quote:I know that, because of the data block, Oracle will automatically insert the data for me, without needing to use a save button, but I need this button.
Perhaps you need a button, but not INSERT statement behind it.
Once again: what's wrong with default Forms behaviour? Why do you think that you need to INSERT a record, when Forms do that perfectly well?
|
|
|
|
|
|
| Re: Unique constraint violated [message #449371 is a reply to message #449343] |
Mon, 29 March 2010 05:21 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Whenever you issue a commit in forms it applies all outstanding data block changes to the database - inserts, updates and commits. No code is needed on your part for this.
You'd normally only hard-code a DML statement in forms if you want to make changes to some table other than the one the block is based on.
So if you've got a table that logs inserts into the emp table for example you might put an insert statement in the pre-insert trigger on the emp block to insert into the log table.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
