Home » Developer & Programmer » Forms » INSERT INTO TWO TABLE WITH FOREIGN KEY
INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661004] Sat, 04 March 2017 09:59 Go to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
/forum/fa/13472/0/
Hi,
I have two table
1. BODY having 2 COLUMN COL1, COL2
2. HEAD having 1 COLUMN COL1
Foreign Key BODY references HEAD (COL1)
INSERT INTO HEAD (COL1)
VALUES (:COL1);
INSERT INTO BODY (COL1,COL2)
VALUES (:COL1,:COL2);
STANDARD.COMMIT;
WANT RESULT LIKE BELOW
select * from body;    select * from head;
                   TABLE
  BODY                             HEAD
COL1 COL2                          COL1
 A     B                             A
 A     C
 A     D
Thanks in Advanced...
  • Attachment: SAMPLE.JPG
    (Size: 15.75KB, Downloaded 433 times)
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661005 is a reply to message #661004] Sat, 04 March 2017 10:01 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
But my result is...
select * from body;    select * from head;
                   TABLE
  BODY                             HEAD
COL1 COL2                          COL1
 A     B                             A
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661006 is a reply to message #661005] Sat, 04 March 2017 10:18 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use the Data Block Wizard to create a master-detail form. Forms will take care about everything, you don't need to write a single line of code.
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661007 is a reply to message #661006] Sat, 04 March 2017 10:27 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
i want to use when-button-pressed / when-validate-item to insert record into database
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661016 is a reply to message #661007] Sun, 05 March 2017 04:23 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Reading your messages on OraFAQ Forum, I have the impression that you are doing everything you can to make your life (as a Forms developer) as miserable as possible. I can't remember whether I met someone (here, on the Forum) as stubborn as you. Every action you take, every task you have, you do it in a wrong manner.

Therefore, I have a suggestion for you: switch to Java. I've attended a 5-days course for Java beginners; the last day we asked the instructor to create a master-detail form on Scott's standard EMP and DEPT tables. In Forms, this is a 2-minutes job. A colleague of mine uses Delphi and he created the form in a matter of minutes too. However, our Java instructor needed several hours (honestly, I'm not exaggerating) and, eventually, didn't finish the task. Now, one could blame the person (who didn't know how to do it), or the tool (Java itself).

As everything you do, you do it as complicated and difficult as possible, I think that Java should be your choice. Give up on Forms, that's too simple for you.



To answer your last message: feel free to do it any way you want, but I'm afraid I won't take part in such a Forms massacre.
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661023 is a reply to message #661016] Sun, 05 March 2017 05:29 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
When-button-pressed trigger
INSERT INTO HEAD (COL1)
VALUES (:COL1);
INSERT INTO BODY (COL1,COL2)
VALUES (:COL1,:COL2); --When entering multiple row on COL2 like B,C,D... in database only last row D is showing
STANDARD.COMMIT;
Query
select * from body;    select * from head;
                   TABLE
  BODY                             HEAD
COL1 COL2                          COL1
 A     D                             A
can i use cursor / loop here to get all row or anything else...
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661024 is a reply to message #661016] Sun, 05 March 2017 05:44 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
i am not a Form Developer nor attended any class for oracle/pl sql either. i am network administrator in my office we are using Oracle based ERP, and i wanted to know how its made, and searching on you tube and i love the concept... that's it
Thanks...
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661025 is a reply to message #661024] Sun, 05 March 2017 19:33 Go to previous messageGo to next message
JohnGuru
Messages: 6
Registered: March 2017
Junior Member
It appears that you did not iterate the Block while inserting record into the Body table. So only the active rows gets inserted into DB. You can loop the block as given in the sample code for inserting all rows-

Go_Block('BL_BODY');
FIRST_RECORD;
LOOP
INSERT INTO ...
EXIT WHEN LAST_RECORD = TRUE;
NEXT_RECORD;
END LOOP;
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661044 is a reply to message #661025] Mon, 06 March 2017 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should really try just seeing what forms default behaviour does.
Create two blocks with a relationship using the wizards.
Write no code.
Run the form.
Insert data into both blocks.
Click save.
See what happens.
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661050 is a reply to message #661025] Mon, 06 March 2017 03:36 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
it works for me... Thanks
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661051 is a reply to message #661044] Mon, 06 March 2017 03:39 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
now clear the concept how data insert into db.. thank you
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661055 is a reply to message #661051] Mon, 06 March 2017 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've written lots of forms over the years. Hardly any of them contain insert/update/delete statements.
Vast majority of code I wrote was validation code.

You need to get your head around forms default behaviour and make use of it as much as possible.

Then every time you find yourself bypassing / suppressing that default behaviour you need to ask yourself if that's really necessary.
Re: INSERT INTO TWO TABLE WITH FOREIGN KEY [message #661096 is a reply to message #661055] Tue, 07 March 2017 07:51 Go to previous message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Thanks i'll get it as advice.
Previous Topic: Default Menubar Problem
Next Topic: Why is fmx not difference in different pc
Goto Forum:
  


Current Time: Sun Jan 26 13:15:08 CST 2020