Home » Developer & Programmer » Forms » error in inserting records from forms6i (strange)
error in inserting records from forms6i (strange) [message #268025] Mon, 17 September 2007 03:18 Go to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m making message system.In the compose form which is used to send message to another person.i m facing a problem.when i click button to send message,error occurs.

FRM-40735:WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403

i m writing this code to save in database.

begin
insert into mesage(msg_no,teacher_id,receiver_id,msg_subject,mesage,msg_date)
values (:mesage.msg_txt,:mesage.teacher_id,:mesage.receiver_id,:mesage.msg_subject,:mesage.mesage,:mesage.msg_date);
end;



plz tell me any solution if any idea
thnks

[Updated on: Mon, 17 September 2007 03:19]

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268035 is a reply to message #268025] Mon, 17 September 2007 04:58 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

ORA-1403 is a no data found error.

Are you doing any selects before this insert statement?
Re: error in inserting records from forms6i (strange) [message #268065 is a reply to message #268025] Mon, 17 September 2007 07:28 Go to previous messageGo to next message
petku
Messages: 10
Registered: September 2007
Junior Member
or is there an insert-trigger on table message?
Re: error in inserting records from forms6i (strange) [message #268252 is a reply to message #268065] Tue, 18 September 2007 01:15 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do you have a pre-insert trigger on the 'mesage' table?

David
Re: error in inserting records from forms6i (strange) [message #268266 is a reply to message #268252] Tue, 18 September 2007 01:45 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
yes i m using "pre-insert" triger and also doing selects.


i have written code to generate sequence number for "msg_no" in pre-insert trigger of form level.(this code is correctly generating sequence number)

select nvl(max(msg_no),0)+1 into :mesage.msg_txt
from mesage;


i have written this code in "when-new-form-instance" to get the person ID in a textbox, whose login ID is 1.(i m getting correct person ID in textbox)

select t.teacher_id into :mesage.teacher_id
from teacher t
where t.login_id = 1;


now i want to store all this information in database.
i m writing this code in "when-button-pressed "trigger against a button (send).




begin
/*to select person's ID against the name entered in the  textbox(name)*/
	
        select t.teacher_id into :mesage.receiver_id
	from teacher t
	where t.name = :mesage.name;

/*code for inserting vaules in database table  (Mesage)*/

insert into mesage(msg_no,teacher_id,receiver_id,msg_subject,mesage,msg_date)
values (:mesage.msg_txt,:mesage.teacher_id,:mesage.receiver_id,:mesage.msg_subject,:mesage.mesage,:mesage.msg_date);
end;


but it is not inserting vaules in mesage table..it is generating error message.

FRM-40735:WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403


MESSAGE TABLE(where i want to save this information)

SQL> describe mesage;
 Name                            Null?    Type
 ------------------------------- -------- ----
 MSG_NO                          NOT NULL NUMBER
 RECEIVER_ID                              NUMBER
 TEACHER_ID                               NUMBER(12)
 MSG_SUBJECT                              VARCHAR2(50)
 MESAGE                                   VARCHAR2(500)
 MSG_DATE                                 DATE


plz tell me any solution
thnks






[Updated on: Tue, 18 September 2007 01:53]

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268272 is a reply to message #268266] Tue, 18 September 2007 01:55 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Is your block based on 'mesage'? If so, why are you MANUALLY doing the insert statement?

It is not the insert that is failing because ORA-01403 means "no data found".

Place 'exception' blocks after ALL of your 'select' statements which activate alerts or message/pause pairs so that you can find out which is the one that is failing.

David

Re: error in inserting records from forms6i (strange) [message #268274 is a reply to message #268272] Tue, 18 September 2007 01:55 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you run your form in debug mode yet?

David
Re: error in inserting records from forms6i (strange) [message #268450 is a reply to message #268274] Tue, 18 September 2007 12:51 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
yes ,it is based on mesage table.But if i write "commit_form " on the "send" button to save it in database.It gives error"unable to insert .....".

Because one column does not exit in mesage table but it is on interface (textbox where i write receiver name,).There is no column in mesage table to save receiver name.In table receiver ID column exist.But on interface i want that user should write receiver name not his ID.

So i m doing manually inserting.

/*code for inserting vaules in database table  (Mesage)*/

insert into mesage(msg_no,teacher_id,receiver_id,msg_subject,mesage,msg_date)
values (:mesage.msg_txt,:mesage.teacher_id,:mesage.receiver_id,:mesage.msg_subject,:mesage.mesage,:mesage.msg_date);
end;


any solution?
waiting for reply
thnks

[EDITED by LF: removed a huge BMP. Attach a JPG instead]

[Updated on: Tue, 18 September 2007 16:00] by Moderator

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268456 is a reply to message #268450] Tue, 18 September 2007 13:22 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i m attaching interface here and in interface u will see that on th top "oracle forms..." is written and below the toolbar "window 1". i want to eliminate it and want to write something else so plz tell me its solution for it,and solution of my previous problem.

thnks

[EDITED by LF: removed a huge BMP. Attach a JPG instead]

[Updated on: Tue, 18 September 2007 16:01] by Moderator

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268470 is a reply to message #268456] Tue, 18 September 2007 16:05 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sams: it is about time for you to read the OraFAQ Forum Guide and learn how to write an acceptable message. In last two posts, you've attached BMPs which were almost 3 MB in size. This is absolutely unacceptable and I have deleted both of them. Attach a JPG or PNG instead, please.
Re: error in inserting records from forms6i (strange) [message #268493 is a reply to message #268470] Tue, 18 September 2007 22:25 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
sorry for that but i have not any idea that there is size limit for attaching pic.
i will not do it again.
Re: error in inserting records from forms6i (strange) [message #268524 is a reply to message #268493] Wed, 19 September 2007 00:14 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sams,

Make the 'receiver name' a NON-database field, use the When-Validate-Item trigger on it to populate the hidden database field 'receiver_id'. You would also use the post-query trigger (at the block level) to populate the 'recever name' from the retrieved 'receiver_id' during queries.

David
Re: error in inserting records from forms6i (strange) [message #268540 is a reply to message #268524] Wed, 19 September 2007 01:04 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i have tried your suggestion (post query trigger) and getting correct result but now i want to insert entered records(msg_no,teacher_id,receiver_id,msg_subject,mesage,msg_date) in database.i dont want to save name in database table(mesage).

MESSAGE TABLE(where i want to save this information)
SQL> describe mesage;
 Name                            Null?    Type
 ------------------------------- -------- ----
 MSG_NO                          NOT NULL NUMBER
 RECEIVER_ID                              NUMBER
 TEACHER_ID                               NUMBER(12)
 MSG_SUBJECT                              VARCHAR2(50)
 MESAGE                                   VARCHAR2(500)
 MSG_DATE                                 DATE


i m writing this code on button to save data in database table

begin
insert into mesage(msg_no,teacher_id,receiver_id,msg_subject,mesage,msg_date)
values (:mesage.msg_txt,:mesage.teacher_id,:mesage.receiver_id,:mesage.msg_subject,:mesage.mesage,:mesage.msg_date);
end;


and getting error mesage
FRM-40735:WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403

if u know any other solution to insert data in database plz tell me reply
thnks

[Updated on: Wed, 19 September 2007 01:07]

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268542 is a reply to message #268540] Wed, 19 September 2007 01:08 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sams,

I want you to create a new form. Base the block on the 'mesage' table, FORGET about the 'receiver name' for the moment. The records can be inserted, deleted, updated, and queried without ANY manual code.

Add the non-database 'receiver name' item, populate it in the post-query trigger. Hide the 'receiver id' field and populate it in the when-validate-item trigger on the 'receiver name'.

That is all you have to do.

David
Re: error in inserting records from forms6i (strange) [message #268565 is a reply to message #268542] Wed, 19 September 2007 01:58 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
i have tried the solution u told but not getting error msg,so now i m attaching interface so plz tell me wats the problem and its solution.

Database tables used in form


Mesage table(where i want to store date)

create table Mesage (msg_no number(*) , receiver_id number(*) ,teacher_id number(*) ,msg_subject varchar2(50),mesage varchar2(500),
msg_date date,
constraint pk_msg_no primary key(msg_no),
constraint fk_mt_id foreign key(teacher_id) references Teacher(teacher_id));


Teacher table(to get "name")

create table Teacher(teacher_id number(*) ,name varchar2(30),last_name varchar2(30),
constraint pk_Teach_id primary key(teacher_id));



Insert statments

insert into teacher1
values(11,'david','littlefoot');


insert into teacher1
values(12,'djmartin','maheer');


reply
thnks



[Updated on: Wed, 19 September 2007 02:00]

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268570 is a reply to message #268565] Wed, 19 September 2007 02:11 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Where is the definition for 'teacher1'?

David
Re: error in inserting records from forms6i (strange) [message #268582 is a reply to message #268570] Wed, 19 September 2007 02:23 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
ohh sorry its teacher not teacher1.
please change it


insert into teacher
values(11,'david','littlefoot');



insert into teacher
values(12,'djmartin','maheer');
Re: error in inserting records from forms6i (strange) [message #268588 is a reply to message #268582] Wed, 19 September 2007 02:27 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Then what about 'login_id' in the WHEN-NEW-FORM-INSTANCE trigger?

David
Re: error in inserting records from forms6i (strange) [message #268761 is a reply to message #268588] Wed, 19 September 2007 11:08 Go to previous messageGo to next message
sams
Messages: 100
Registered: August 2007
Senior Member
SQL> describe login;
 Name                            Null?    Type
 ------------------------------- -------- ----
 LOGIN_ID                        NOT NULL VARCHAR2(20)
 USERNAME                                 VARCHAR2(30)
 PASSWORD                                 VARCHAR2(30)
 ROLE                                     VARCHAR2(30)

SQL> describe teacher;
 Name                            Null?    Type
 ------------------------------- -------- ----
 TEACHER_ID                      NOT NULL NUMBER(12)
 LOGIN_ID                                 VARCHAR2(20)
 NAME                                     VARCHAR2(30)
 LAST_NAME                                VARCHAR2(30)

SQL> describe mesage;
 Name                            Null?    Type
 ------------------------------- -------- ----
 MSG_NO                          NOT NULL NUMBER
 RECEIVER_ID                              NUMBER
 TEACHER_ID                               NUMBER(12)
 MSG_SUBJECT                              VARCHAR2(50)
 MESAGE                                   VARCHAR2(500)
 MSG_DATE                                 DATE
Create

Table description:
create table login (login_id VARCHAR2(20),username VARCHAR2(30),password VARCHAR2(30),role varchar2(30),
constraint pk_log_id primary key(login_id));



create table Teacher(teacher_id number(12),login_id ,name varchar2(30),last_name varchar2(30),
constraint pk_Teach_id primary key(teacher_id),
constraint fk_log_id foreign key(login_id) references login(login_id));


create table Mesage (msg_no number(*) , receiver_id number(*) ,teacher_id number(12) ,msg_subject varchar2(50),mesage varchar2(500),
msg_date date,
constraint pk_msg_no primary key(msg_no),
constraint fk_mt_id foreign key(teacher_id) references Teacher(teacher_id));



insert statements:

for login table:
insert into login
values(1,'david','david','admin');

insert into login
values(2,'djmartin','djmartin','teacher');


for teacher table:

insert into teacher
values(11,1,'david','littlefoot');

insert into teacher
values(12,2,'djmartin','maheer');


This time nothing missing.ok








[Updated on: Wed, 19 September 2007 11:24]

Report message to a moderator

Re: error in inserting records from forms6i (strange) [message #268862 is a reply to message #268761] Wed, 19 September 2007 23:40 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Okay Sams,

First things first. Always put 'begin' and 'end;' into ALL of your triggers. Learn how to run the form in debug mode so that you can single step through your code.

Now get rid of the %^&*%$# 'insert' statement which is in the trigger under the 'send' button. Put a 'commit_form' there instead. Oracle Forms will do all the inserting, updating and deleting that you need, you don't have to do it yourself.

In the When-Validate-Item item of :mesage.name you have:
select t.teacher_id into :mesage.receiver_id
from teacher t
where t.name = :mesage.name;

The validate trigger is there to validate not just get data. So validate the field. Test to see if the ':mesage.name' exists in the 'teacher' table. You do that by wrapping the 'select' statement with an exception handling routine.

By the way, do you really expect your user to type the 'to' name in mixed case and get the exact spelling that exists in the database? If you had an LOV on the ':mesage.name' field with 'Validate from List' turned 'on' would also be of assistance. Also, when you do the 'select' use 'upper' on the arguments on both sides of the 'where' clause. How are you going to handle the duplicate names?

You have specified the form's menu as "D:\interfac\menu\mes.mmx" and under the 'logout' button you have "new_form('D:\interfac\login');". Get rid of the absolute form name and just use "mes" and "login". The Forms system should be able to find them if you have defined your paths correctly. Also, save yourself a world of pain and have only ONE directory holding all your forms, menus, and libraries.

David
Re: error in inserting records from forms6i (strange) [message #269441 is a reply to message #268862] Sat, 22 September 2007 05:47 Go to previous message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
Not sure but pre-insert trigger of the form can be changed to

select max(nvl(msg_no,0)+1 into :mesage.msg_txt from mesage;

--------------------------------------------------------

yes i m using "pre-insert" triger and also doing selects.


i have written code to generate sequence number for "msg_no" in pre-insert trigger of form level.(this code is correctly generating sequence number)


select nvl(max(msg_no),0)+1 into :mesage.msg_txt
from mesage;
Previous Topic: user of enter button of key board to perform some action
Next Topic: how to import and export from forms 6i??
Goto Forum:
  


Current Time: Sun Dec 04 12:57:25 CST 2016

Total time taken to generate the page: 0.24284 seconds