| Validating a field with data in a database [message #470007] |
Fri, 06 August 2010 13:57  |
jimbob123
Messages: 6
Registered: August 2010
|
Junior Member |
|
|
Hey guys I was wondering if you could please help me. I currently have a field in which the user inputs a number, but before this number is allowed to be saved to the database it has to be checked to see if the number is already stored in a different field within the database. For example, if the number is not in the database the user cannot put that number into the field.
Perhaps I am going about this completely the wrong way though, I do want it to be user input so I don't want a LOV linked to the appropriate field in the database.
Is below anyway close to the way it should be done? For some reason seq_rec.seq seems to give me 3 and doesn't seem to compare it with each of the values in the database.
DECLARE
cursor seq_curs IS SELECT SEQ FROM ZZJOB;
seq_rec seq_curs%rowtype;
none_existing_seq EXCEPTION;
invalid_seq EXCEPTION;
return_alert NUMBER;
BEGIN
IF (:CREATED_BY > :SEQ) THEN
RAISE invalid_seq;
END IF;
open seq_curs;
fetch seq_curs INTO seq_rec;
close seq_curs;
If :created_by != seq_rec.seq THEN
RAISE none_existing_seq;
End if;
EXCEPTION
WHEN none_existing_seq then
return_alert := SHOW_ALERT('CREATED_BY_INVALID');
RAISE form_trigger_failure;
WHEN invalid_seq then
return_alert := SHOW_ALERT ('CREATED_BY_ALERT');
RAISE FORM_TRIGGER_FAILURE;
END;
Just say the field in my database has numbers: 1 2 3 4 and 5
in the field that i allow the user to input data they can only enter 1 2 3 4 or 5. Hence the check needs to be made with te database field to check if the input number is already in the database.
P.S. they are separate fields for different things, the numbers just need to be in one field before they can be added in the other.
|
|
|
|
| Re: Validating a field with data in a database [message #470009 is a reply to message #470007] |
Fri, 06 August 2010 14:41  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I didn't quite get all you said, and I don't know what should be a purpose of declaring a cursor and fetching a single value from it (when the same could be done with a simple SELECT statement).
Anyway: I believe that the following code (put into the WHEN-VALIDATE-ITEM trigger might do the job:declare
l_seq zzjob.seq%type;
begin
select z.seq
into l_seq
from zzjob z
where z.seq = :form_item;
exception
when no_data_found then
message('That value does not exist in the SEQ column. You can't use it here');
raise_form_trigger_failure;
when too_many_rows then
null;
end;
What does it do? It selects a value from the ZZJOB table that is equal to value you have entered into a form item (WHERE z.seq = :form_item). If query returns a value, everything is fine - you are allowed to use that value. If, on the other hand, query returns nothing (i.e. raises NO-DATA-FOUND exception), stop form execution.
If, on the other hand, query returns more than a single value, no problem - the value exists and everything is fine (so do nothing (null;).
[EDIT: fixed non-existing variable]
[Updated on: Fri, 06 August 2010 15:15] Report message to a moderator |
|
|
|
| Re: Validating a field with data in a database [message #470011 is a reply to message #470009] |
Fri, 06 August 2010 15:02 |
jimbob123
Messages: 6
Registered: August 2010
|
Junior Member |
|
|
Thanks for that, and thanks for the explanation of the code, very useful. Makes perfect sense now.
only one thing I'm unsure about is something minor - just wondering if you left out the declaration for cb_exists or is that something built into forms?
also- on another note, just say the user has called up the form and entered say maybe 3 records but has not committed them to the database yet would there be a way to check them values too, although there not committed there still data about to be committed.
Example - user has added records with values 1 2 and 3, but not committed the data yet, the records are waiting to be saved. but when the user goes to add a 4th record in the field that is dependant validating against the database he wants to add the value 3, which is in a record waiting to be committed but hasnt been committed yet. Is there a way to check it against those records created but not committed.
That may not be explained the best so sorry if its difficult to understand.
Thanks again.
[Updated on: Fri, 06 August 2010 15:11] Report message to a moderator |
|
|
|
| Re: Validating a field with data in a database [message #470012 is a reply to message #470011] |
Fri, 06 August 2010 15:18 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
jimbob123 wrote on Fri, 06 August 2010 16:02
only one thing I'm unsure about is something minor - just wondering if you left out the declaration for cb_exists or is that something built into forms?
What is cb_exists? It is mentioned nowhere else in this thread.
[added] As for your other question, I was a little lost, but I think maybe you want to look at the POST command.
[Updated on: Fri, 06 August 2010 15:20] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
