Not Letting the user input a value that already exists 1

[PatrickB101]

Not Letting the user input a value that already exists..

I am not sure I have done sever vb programs nothing to do with database programs.. So bear with me. I understand i need to do this in a when validate item trigger.
do you know what the syntax for if item equals a item that already exist in the database then ... so on and so forth ?

regards,
Patrick

 

[sem]

Suppose your item is called :emp.empno and you wish to check it against empno field of emp table.

declare
dummy number;
cursor c is select 1 from emp where empno = :emp.empno.
begin
open c;
fetch c into dummy;
if c%notfound then
close c;
else
raise dup_val_on_index;
end if;
exception
when dup_val_on_index then
close c;
-- place your code to process here
when others then
close c;
-- place your code to process here
end;

But you may also create unique key on emp.empno and let server to generate error.

 

[PatrickB101]

this is not compiling right

cursor c is select 1 from KBI_VALID_VALUES where
KBI_VVL_VALDID_VALUE = :KBI_VALID_VALUES.KBI_VVL_VALDID_VALUE.

 

[PatrickB101]

already fixed thanks for your help man

 

[PatrickB101]

hmm well i got it to compile right it still does not work right
i want it to give an error when the value is the same as a value that exists. and i don't want the code to do anything if it is a distinct value.

 

[sem]

Just replace the first "-- place your code to process here" with your code, e.g.
message('Value already exists');
raise form_trigger_failure;

The second "place" is to handle unexpected error.

When your cursor returns no data (your value is distinct), nothig happens, you just return to the caller.

 

[PatrickB101]

it is giving an error on everything no matter if it should or not.

 

[PatrickB101]

i put this in a when validate item trigger is that correct?

 

[sem]

Yes, put it into when-validate-item of KBI_VVL_VALDID_VALUE.

But what is your specific error? It may be related to type mismatch between KBI_VVL_VALDID_VALUE item and KBI_VVL_VALDID_VALUE column causing error in query. Try to catch value_error also. Try to replace the second place with "raise;" to see the real error or with "null;" to suppress other errors. Try also to debug your code.

 

[PatrickB101]

declare
dummy varchar(5);
alert_button number;
cursor c is select 1 from KBI_VALID_VALUES where
KBI_VVL_VALID_VALUE = KBI_VALID_VALUES.KBI_VVL_VALID_VALUE;

begin
open c;
fetch c into dummy;

if c%notfound then
close c;
else
raise dup_val_on_index;
end if;
exception
when dup_val_on_index then
close c;
-- place your code to process here
alert_button := show_alert('LESSTHAN3');
raise form_trigger_failure;
when others then
close c;
alert_button := show_alert('LESSTHAN3');
raise form_trigger_failure;
-- place your code to process here
end;

tell me what you think.. it gives an alert everytime something is changed.. makes no sense to me

 

[PatrickB101]

oops actually i got this

when others then
close c;
--alert_button := show_alert('LESSTHAN3');
--raise form_trigger_failure;
-- place your code to process here
end;

 

[sem]

Is it not interesting to you what error occured?
If your query was not executed properly the goal was not achieved: the duplicate record may exist and you do not know this.

 

[PatrickB101]

How can the duplicate record exists and I not know it.
It displays all the records. It gives the alert even if you put it back to the original value.

 

[PatrickB101]

the program is not giving an error it just doesn't seem to care what value your inputing it stiill gives you an alert. No matter if its a good value or a value that is a duplicate.

 

[sem]

So make your alert to display error message (you may get it from sqlerrm, but BEFORE closing cursor).