Home » Developer & Programmer » Forms » when item validate trg
- when item validate trg [message #640467] Wed, 29 July 2015 02:08 Go to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hallo all,

I am trying to validate one field in a block. I worte a procedure and call in the respective Trigger in that item.

Code: [Select all] [Show/ hide]
anz number;
begin
 select count (*) into anz
   from table1
   where id = :d_block1.id;
if anz > 0 and :d_block1.id is not null then
 message ('invalid ID !');
end if;
end;

in the block i do have, 5 items field, and one is non-database item which property set to database item = No and Required = No.

And the problem is, when i want to check validity in the respective field, (when user enter some other id in the ID field and press F11) it did not show any message and get error: "FRM-40301: Query caused no records to be retrieved, Re-enter."

Thats true, but i want to display my own message, if invalid ID is entered.

Any suggestion please? Smile

Regards,

Report message to a moderator

- Re: when item validate trg [message #640470 is a reply to message #640467] Wed, 29 July 2015 02:50 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
When-validate-item doesn't fire in enter-query mode.
Move the code to key-exeqry

Report message to a moderator

- Re: when item validate trg [message #640473 is a reply to message #640470] Wed, 29 July 2015 02:59 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hallo CM,

Thank you very much for your reply.

Well, i call the procudure in Key-ExeQry in block-Level, but it still did not work :/

Report message to a moderator

- Re: when item validate trg [message #640476 is a reply to message #640473] Wed, 29 July 2015 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show the current code

Report message to a moderator

- Re: when item validate trg [message #640477 is a reply to message #640476] Wed, 29 July 2015 04:14 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member

It is the same code as i have posted here, but i have deleted the WVI Trigger from Item.ID and create new Trigger KEY-EXEQRY at block Level and call the procedure.
like:

in KEY-EXEQRY at d_block:
check_id;

Report message to a moderator

- Re: when item validate trg [message #640478 is a reply to message #640477] Wed, 29 July 2015 04:25 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
So where's the call to execute_query?
You need one.

Report message to a moderator

- Re: when item validate trg [message #640480 is a reply to message #640478] Wed, 29 July 2015 04:47 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
HI, CM,

thank you very much for your Suggestion and Feedback as well. Smile

sry, i forgot to include execute_query

so now, my code at d_block:

check_id;
execute_query;

but sry, it did not work :/

Report message to a moderator

- Re: when item validate trg [message #640481 is a reply to message #640480] Wed, 29 July 2015 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define did not work.

Report message to a moderator

- Re: when item validate trg [message #640483 is a reply to message #640481] Wed, 29 July 2015 05:51 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
I mean, it did not Show an message, even if i enter the wrong ID.
When I enter Wrong ID in ID field and press F11 then it gives error: "FRM-40301: Query caused no records to be retrieved, Re-enter."

Report message to a moderator

- Re: when item validate trg [message #640484 is a reply to message #640483] Wed, 29 July 2015 06:13 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably it does show the message but it's getting immediately overwritten by the FRM error.
You need to move the execute_query into the procedure so it's only run if the cursor finds a matching record.

Report message to a moderator

- Re: when item validate trg [message #640486 is a reply to message #640484] Wed, 29 July 2015 06:24 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hi CM,

Thanking you again..

Well, I have removed my procedure and written code direct in the key-exeqry, but it gives the same error still.

my current code at d_block:

declare
var number;
begin
select count (*) into var
from table1
where id = :d_block1.id;
if anz > 0 and :d_block1.id is not null then
message ('invalid ID !');
end if;
end;
execute_query;

Any idea please?

REgards,

Report message to a moderator

- Re: when item validate trg [message #640487 is a reply to message #640486] Wed, 29 July 2015 06:54 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hallo,

I have changed some in my code, I have created on_message Trigger in form-Level to trap the error-40301, and it Shows the message, when i give the wrong ID and press F11, but when I give the right ID, and press F11, it Shows the same error as No Record Found and donT' execute query. :/ And my key_exeqry Trigger is as previous, did not Change any.

Any idea please?
Code: [Select all] [Show/ hide]
Declare
	msg_code Number := message_code;
	msg      Varchar2(200) := substr('  ' ||MESSAGE_TYPE||'-' || TO_CHAR(Msg_code) ||': '||Message_Text,1,200);
Begin
	if msg_code = 40301 then
          message('NO Records Found !');
        else 
          message(msg, no_acknowledge);
	end if;
End;

Report message to a moderator

- Re: when item validate trg [message #640488 is a reply to message #640486] Wed, 29 July 2015 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
sanodani wrote on Wed, 29 July 2015 12:24
Hi CM,

Thanking you again..

Well, I have removed my procedure and written code direct in the key-exeqry, but it gives the same error still.

my current code at d_block:

declare
var number;
begin
select count (*) into var
from table1
where id = :d_block1.id;
if anz > 0 and :d_block1.id is not null then
message ('invalid ID !');
end if;
end;
execute_query;

Any idea please?

REgards,



That code runs execute_query regardless of whether the cursor found anything. If you know the ID is invalid you shouldn't be calling it.

Report message to a moderator

- Re: when item validate trg [message #640489 is a reply to message #640488] Wed, 29 July 2015 07:38 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hi CM

Thankx alot for your Suggestion,

Yes, actually you are right, when i know that ID is wrong then i dont't Need to call this Validation, but my mean to use this for Validation is:
there are many id, and if user Input (unknownly) wrong ID then should give this message.

I don't know should it be good to do so or not? I mean to make Validation.

Regards

Report message to a moderator

- Re: when item validate trg [message #640492 is a reply to message #640489] Wed, 29 July 2015 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
The it in my last sentence referred to execute_query. You can't possibly know the id is wrong without doing the validation.

Report message to a moderator

- Re: when item validate trg [message #640493 is a reply to message #640492] Wed, 29 July 2015 08:25 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hallo CM,

I am sorry, i did not understand, what you mean :/ Can you please let me know what, you actually mean? Smile

Well, I tried to put my execute query at first and include on-message Trigger at form-Level,
it Shows the message when id is wrong, but but also Show the message when id is correct and then after execute the query as per that id.

now, I made so:

Key-EXEQRY:
Code: [Select all] [Show/ hide]
 
execute_query;
declare
 var number;
 begin
 select count (*) into var
 from table1
 where id = :d_block1.id;
 if anz > 0 and :d_block1.id is not null then
 message ('invalid ID !');
 end if;
 end;
 execute_query;


and ON-MESSAGE:
Code: [Select all] [Show/ hide]
Declare
       msg_code Number := message_code;
	msg      Varchar2(200) := substr('  ' ||MESSAGE_TYPE||'-' || TO_CHAR(Msg_code) ||': '||Message_Text,1,200);
Begin
	if msg_code = 40301 then
          message('NO Records Found !');
        else 
          message(msg, no_acknowledge);
	end if;
End;


Can you please tell me, where i have made mistake?
thanking you alot,
regards,

Report message to a moderator

- Re: when item validate trg [message #640494 is a reply to message #640484] Wed, 29 July 2015 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 29 July 2015 12:13

You need to move the execute_query into the procedure so it's only run if the cursor finds a matching record.


That sentence would imply that the call to execute_query would need to be somewhere inside an IF/ELSE construct. You already have an IF construct that checks if there are matching records so I'm baffled as how you're struggling here.
Do I really need to tell you that you've placed the call to execute_query so that it'll always be run?
So move the call to execute_query as I suggested above and get rid of the on-message trigger - you don't need it.

Report message to a moderator

- Re: when item validate trg [message #640532 is a reply to message #640494] Thu, 30 July 2015 05:52 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hi CM,

Thankyou very much for your Suggestion and Feedback Smile it works now.....
i just Change my execure_query inside the if Statement.

Report message to a moderator

- Re: when item validate trg [message #640541 is a reply to message #640532] Thu, 30 July 2015 09:10 Go to previous message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
I'm just confused as to why you would write a WVI trigger to validate the value entered when Oracle Forms performs the same basic validation when you execute your query. If all you wanted to do was change the message displayed when Forms didn't find a value, you could have done this in the On-Error trigger. Now, you are making 2 trips to the database for each valid entry instead of 1 trip to the database.

Just my thoughts...
Craig...

Report message to a moderator

Previous Topic: disable text item based on value in previous text item
Next Topic: How Can We Get Actual IP Address Running on Backend in Forms 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 03 07:15:08 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.