| duplicate records [message #84319] |
Mon, 01 March 2004 21:06  |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
hi
for example i've a table called CUSTOMER, and it's primary key is the IC Number. I've entered a record with the ICNO of 00-204554. If i try to enter another record with the same ICNO, an error message will appear at the bottom of the screen saying "unable to INSERT record". My question now is, how to make the message appear as an alert? what will be te coding?
thank you
|
|
|
|
| Re: duplicate records [message #84320 is a reply to message #84319] |
Mon, 01 March 2004 21:30  |
Solveiga
Messages: 36
Registered: March 2003
|
Member |
|
|
You have to create an alert first.
Second step: On-error, or on-message trigger you have to write such code:
declare
n NUMBER
BEGIN
IF msgnum = 40508 THEN
n:= show_alert('Your_alert)'
END IF
END
Mabe another error code is when integrity is violated?
If you need more help, just write
Good luck
Solveiga
|
|
|
|
| Re: duplicate records [message #84329 is a reply to message #84320] |
Tue, 02 March 2004 14:50 |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
thank you for ur reply.
i've tried using the coding but the message box is not coming out. where shuld i put it?
instead, after creating the alert, i run it and try to save a duplicate record and a message box come out stating "FRM-40508 : Oracle error : Unable to insert record". Is there any way to change the system message?
thank you
|
|
|
|
| Re: duplicate records [message #84330 is a reply to message #84329] |
Tue, 02 March 2004 20:42 |
Solveiga
Messages: 36
Registered: March 2003
|
Member |
|
|
Did you try this on form level?
On-error trigger:
DECLARE
errnum NUMBER := ERROR_CODE;
errtxt VARCHAR2(80) := ERROR_TEXT;
errtyp VARCHAR2(3) := ERROR_TYPE;
m NUMBER;
BEGIN
IF errnum = 40508 THEN
m:= show_alert('Your_alert');
ELSE
Message(errtyp||'-'||TO_CHAR(errnum)||': '||errtxt);
END IF;
END;
Other way could be to catch an ORA error number.
I will try to find out and then write to you
Try this code first, mabe it works??
Solveiga
|
|
|
|
| Re: duplicate records [message #84331 is a reply to message #84330] |
Tue, 02 March 2004 21:10 |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
thanks for your reply.
the previoue coding i did it at form level with on-message trigger, but it doesn't work.
On the other hand, the on-error trigger works but after clicking either the ok or cancel button, it loops i.e. the same message box appear again after clicking, is this normal? is there a coding to stop the loop?
thank you
|
|
|
|
| Re: duplicate records [message #84332 is a reply to message #84331] |
Tue, 02 March 2004 21:25 |
Solveiga
Messages: 36
Registered: March 2003
|
Member |
|
|
Try to manage an alert actions, for example
if your alert has two buttons:
n := show_alert('Your_alert');
if n = alert_button1 then message ('Dublicate record, you can not insert it')
else
exit_form; --or commit_form
end if;
Write again if it doesn't work
Solveiga
|
|
|
|
| Re: duplicate records [message #84334 is a reply to message #84332] |
Wed, 03 March 2004 20:10 |
Nitin Arora
Messages: 7
Registered: February 2004
|
Junior Member |
|
|
Dear Annie,
Hi !! Have been reading all the queries and suggestions. Maybe U can try this alternate method if U R not very comfortable with the ON-ERROR trigger. On the save button of your form, select the count of records which have the given customer id in the form, into a variable. In case the count is more than one, then U can call ur alert, else continue.
Sample code for the same
begin
select count(*) into cnt
from buy_sell
where finyear = :buy_sell.finyear
and month = :buy_sell.month
and warehouse = :buy_sell.warehouse
and region = :buy_sell.region
and plant = :buy_sell.plant
and branch = :buy_sell.branch
and category = :buy_sell.category
and longflatflag = :buy_sell.longflatflag
and bsdate = :buy_sell.bsdate;
exception
when no_data_found then
cnt := 0;
end;
if cnt > 0 then
set_alert_property('alert_ok',alert_message_text,'Record already exists, Cannot save !');
a := show_alert('alert_ok');
raise form_trigger_failure;
end if;
I hope this will solve ur problem.
Happy Coding.
Regards,
NitiN
|
|
|
|
| Re: duplicate records [message #84346 is a reply to message #84332] |
Fri, 05 March 2004 20:36 |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
hi
below is my coding, it can work but for the errnum = 40400, it wont work. instead the system message box come out, which says somthing like transaction complete 1 record saved, can it be altered?
DECLARE
errnum NUMBER := ERROR_CODE;
errtxt VARCHAR2(80) := ERROR_TEXT;
errtyp VARCHAR2(3) := ERROR_TYPE;
m NUMBER;
begin
IF errnum = 40508 THEN
message('ERROR : The record with the same ID exist in the database. Please enter another ID or exit this form.');
ELSIF errnum = 40400 then
message('The record was saved.');
ELSIF ERRNUM = 40401 THEN
MESSAGE ('No records were added or modified since the last apply or save.');
ELSE
Message(errtyp||'-'||TO_CHAR(errnum)||': '||errtxt);
END IF;
end;
|
|
|
|
| Re: duplicate records [message #84351 is a reply to message #84334] |
Sun, 07 March 2004 14:02 |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
Dear Nitin
thanks for ur recomendation but i'm not quite sure about the coding, i prefer using the previous coding as recomended by Solveiga.
|
|
|
|
| Re: duplicate records [message #84356 is a reply to message #84346] |
Sun, 07 March 2004 20:51 |
Solveiga
Messages: 36
Registered: March 2003
|
Member |
|
|
Hei,
Actually, these messages are not errors. It is only
informative messages.
If you don't want to see them - you could write
on message trigger
"IF msgnum = 40400 THEN
clear_message;"
Or if you wan't to see an alert, you need to create it first and then to catch that message :)
Solveiga
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
