Home » Developer & Programmer » Forms » how to insert records manually
how to insert records manually [message #594560] Fri, 30 August 2013 02:22 Go to next message
newbieinapps
Messages: 13
Registered: August 2013
Junior Member
I have a form with a datablock displaying 3 text item fields.
But the actual oracle table have 8 columns.
When ever I insert new data and save it, only the three records are being stored in backend whereas remaining column stores with null value.
That is the basic function of the form.

I placed an another list item field in a control block which is non-database item.
Now I want to store the list item value into a column along with the 3 columns.

In a simple way, how to insert record manually along with the other records?
Re: how to insert records manually [message #594567 is a reply to message #594560] Fri, 30 August 2013 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you want to insert an additional record, or do you want to include the value from the list item in the records the datablock inserts anyway?
It's not clear from your post what you want to happen.
Re: how to insert records manually [message #594570 is a reply to message #594560] Fri, 30 August 2013 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although your data block contains 3 items, I'd suggest you to add the fourth one (which can be hidden if you don't want to see it, but it must be a database item). It would contain value of the non-database list item.

Two options I can think of: one is to use "copy value from item" property for the fourth data block item (you'd copy non-database list item's value into it). Or, in a PRE-INSERT trigger, put non-database list item's value into the fourth item of the database block.

Or, you could try with POST-INSERT trigger and manually (in a loop through all records of the database block) perform UPDATE of the base table.
Re: how to insert records manually [message #594574 is a reply to message #594570] Fri, 30 August 2013 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot wrote on Fri, 30 August 2013 09:11

Or, you could try with POST-INSERT trigger and manually (in a loop through all records of the database block) perform UPDATE of the base table.


I haven't forms handy to check but I'm pretty sure go_record is illegal in post-insert.
Re: how to insert records manually [message #594576 is a reply to message #594574] Fri, 30 August 2013 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe you are right; didn't think of that, my apologies.
Re: how to insert records manually [message #594579 is a reply to message #594567] Fri, 30 August 2013 03:53 Go to previous messageGo to next message
newbieinapps
Messages: 13
Registered: August 2013
Junior Member
Thanks for the reply.


I want to insert the record along with the records that datablock inserts.

I will do what Little Foot suggests and I have one more question.

Can we override the insert operation of records that was done by the data block and insert manually by separate insert statement?
Re: how to insert records manually [message #594580 is a reply to message #594579] Fri, 30 August 2013 04:05 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, but in that case you'd rather use non-database block because Forms would - by default - insert these records once, and you'd them insert again with manually written INSERT statement. Therefore, it would either fail (because of violated uniqueness), or it would succeed (so you'd get duplicates).
Re: how to insert records manually [message #594710 is a reply to message #594580] Mon, 02 September 2013 02:21 Go to previous messageGo to next message
newbieinapps
Messages: 13
Registered: August 2013
Junior Member
It is working out.
Here I used 3 stacked canvas inside a content canvas.
When I query,
The canvas should be displayed based on the selected drop down list.
Insertion is working fine.
I wrote a select statement in POST-Query trigger.
But when querying the data the trigger fetches one time and the data block itself fetches another time.
So here I see 2 multiple records at same time.
Re: how to insert records manually [message #594713 is a reply to message #594710] Mon, 02 September 2013 02:51 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you misused POST-QUERY trigger. Let Forms fetch data, don't do that manually. Use POST-QUERY to populate non-database items.

For example, if it was a form created on Scott's EMP table. It contains the DEPTNO column which represents department number. But, you want to display department name as well, but it is stored in the DEPT table. Therefore, you let Forms fetch all data from EMP table and write POST-QUERY trigger to select DNAME:
select d.dname
  into :emp.dname
  from dept d
  where d.deptno = :emp.deptno;      --> :emp.deptno is populated by Forms when you execute query
Re: how to insert records manually [message #594715 is a reply to message #594713] Mon, 02 September 2013 03:21 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not convinced you need manual inserts.
When the child datablock contains 3 records, how many records should be inserted into the db?
Re: how to insert records manually [message #594764 is a reply to message #594715] Mon, 02 September 2013 10:30 Go to previous messageGo to next message
newbieinapps
Messages: 13
Registered: August 2013
Junior Member
Insertion is working fine.
There is a single table with 5 columns
There are 3 data blocks with 3 records display with 3 stacked canvases in a content canvas
I had 3 fixed values in a non-database list item in the content canvas.
For particular selected value in the list item, the canvas should be displayed.
In the backend the fields displayed in the current canvas and the value in the list item should be stored. I assigned the list item value for the hidden column
But when query the form, all the values that belong to the other block is being displayed.
Query should be performed based on the selected list item value.
So for this, is there any manipulation can be done to query the form as per the above said logic?
Re: how to insert records manually [message #594765 is a reply to message #594764] Mon, 02 September 2013 10:43 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't answer my question.
Re: how to insert records manually [message #594778 is a reply to message #594715] Mon, 02 September 2013 18:52 Go to previous messageGo to next message
newbieinapps
Messages: 13
Registered: August 2013
Junior Member
Then 3 records need to be inserted in the database
Re: how to insert records manually [message #594785 is a reply to message #594778] Tue, 03 September 2013 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you insist on doing it manually? Why don't you let Forms handle that stuff? As you can see, the major difference is that your way doesn't work correctly. Even if you fix it (I hope you will), solution will be more complex than it should be.
Re: how to insert records manually [message #594790 is a reply to message #594785] Tue, 03 September 2013 03:53 Go to previous message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should do what LF suggested in the first place:
Quote:

Although your data block contains 3 items, I'd suggest you to add the fourth one (which can be hidden if you don't want to see it, but it must be a database item). It would contain value of the non-database list item.

Two options I can think of: one is to use "copy value from item" property for the fourth data block item (you'd copy non-database list item's value into it). Or, in a PRE-INSERT trigger, put non-database list item's value into the fourth item of the database block.


That will make the automatic insert work properly. No manual insert needed. No manual query needed.
Previous Topic: How to add in icon to a button in 10g
Next Topic: blinking the text item in oracle forms 6i
Goto Forum:
  


Current Time: Wed Oct 01 14:23:14 CDT 2014

Total time taken to generate the page: 0.19690 seconds