Solved

I need to insert into DB a value, witch end user enter into combobox.

Posted on 2004-03-24
44
1,439 Views
Last Modified: 2007-12-19
Hello,

My tables:
1. T_DAL
columns:
DAL_ID
DAL_PAV
AERO_ID

2. T_AERO
columns:
AERO_ID
AERO_PAV

In my forms block T_DAL I have a ListItem 'CMB_AERO' witch is a data base field (AERO_ID) and here I Populate data from other table T_AERO. I populate this list on when-new-form-instance trigger and populate it from record group
Record Group selekt = 'Select AERO_PAV, to_char(AERO_ID) from T_AERO';

When I inserting a new value into T_DAL, I also assign a value to CMB_AERO (select it from combobox)
But if user enter into this combobox a new data, I want to insert this to my T_AERO table, or open a new form (for enter data for new Aero).

In when-validate-item trigger I put such code

declare

LVal NUMBER;
LLab VARCHAR2(200);
listAero ITEM := find_item('CMB_AERO');

begin

Llab := :T_DAL.cmb_aero;      
select S_AERO.nextval into LVal from dual;
insert into Aviacija.T_AERO(aero_id, aero_pav) values (lval, llab);
add_list_element(listAero, 1, llab, LVal);

end;

I see a new value in a list, but not in DB.

How to insert these new values list, DB and that value user could assign at once to CMB_AERO?
Could you give some advices, comentars?

I would be very pleased

Solveiga


0
Comment
Question by:Solve
  • 24
  • 19
44 Comments
 
LVL 6

Expert Comment

by:musdu
ID: 10665703
Hi,

too see record inserted into database, you must commit your form; (issue a commit_form statement);

regards
0
 
LVL 1

Author Comment

by:Solve
ID: 10665732
But If I didn't entered other required fields yet? When I can't do a commit.

Also I got a messages: if I updating - cannot update record
if inserting - cannot insert record;

Solveiga

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10665760
"Also I got a messages: if I updating - cannot update record if inserting - cannot insert record" - you have to insert/update all NOT NULL columns.
0
 
LVL 1

Author Comment

by:Solve
ID: 10665848
I cheked..

And with treigger you recomend to use.. and how to issue commit_form?

I get a message that I can't commit_form in when-validate-item tregger.
What would be your comentars about my code placed above?

Solve
0
 
LVL 1

Author Comment

by:Solve
ID: 10665875
When I do commit it raise a when-validate-item trigger again and when
its trying to insert into DB only AERO_ID(nextval of my sequence)

I looked an have in my table such data:

160
161 AeroName1

....

Solveiga
0
 
LVL 1

Author Comment

by:Solve
ID: 10666709
It seems it is working....

But how to call commit_form programaticaly?
And mabe someone could write me a code, witch will check is there in data base a record, witch name is that new entered value to combobox?

Solveiga
 
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10666955
Commit_Form is a  restricted procedure so you can use it in a triggers where restricted procedures can be used. If you read the description of some trigger you can find there section Legal Commands.

For checking if there is a value in a table you can use this fast select-
it returns 0 if there is no such value(record) or 1 if there is:
SELECT count(*) INTO n_pom FROM dual WHERE EXISTS
   (select null from your_table where column=new_value);
0
 
LVL 1

Author Comment

by:Solve
ID: 10666989
Thank You, Helena.

And on witch event I should do that insert into table (when-validate-item, when-new-item-instanse or some other)
Because first of all i need to be sure that I want to insert, I need to chek a values I need to insert and so after that I need that value from combobox to be inserted?
Have you more patience to help me with this problem?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10667047
I think that it is not a good practice to insert to both tables in one block. You should create another block for T_AERO. That will solve the problem.
0
 
LVL 1

Author Comment

by:Solve
ID: 10667067
Ok, going to try creat anothet block

Solveiga
0
 
LVL 1

Author Comment

by:Solve
ID: 10667649
It seems I wonn't implement my plans....
I desided only to check a value if user enter something new in combobox...

When do I have to check? On witch tregger. I will use that code witch henka wrote for me.

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10667734
I desided only to check a value if user enter something new in combobox... - so you can use When-Validate-Item trigger for it.
0
 
LVL 1

Author Comment

by:Solve
ID: 10675335
In when-validate-item trigger I pust this code:

declare
      LVal NUMBER;
      LLab VARCHAR2(200);
      listAero ITEM := find_item('CMB_AERO');
yra number;                                      
statAero NUMBER;

begin

Llab := :T_DAL.cmb_aero;

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_PAV = Llab);

If yra = 0 then
n := show_alert('al_new_aero');
if n = alert_button1 then
      select Aviacija.s_aero.nextval into LVal from dual;
      insert into T_AERO(aero_id, aero_pav) values (Lval, Llab);
      :T_DAL.CMB_AERO := lval;
      statAero := Populate_Group('RG_AERO');
      Populate_List(listAero,'RG_AERO');
end if;

end if;
end;

But I always get that yra = 0, even if I select a value from combobox, what means that record exactly exists in DB.
What can it be wrong?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10675414
Did you try this in SQL*Plus ? (put select null from Aviacija.T_AERO ... instead of select * from Aviacija.T_AERO ...):

select count(*) from dual where exists (select null from Aviacija.T_AERO where AERO_PAV = Llab);
0
 
LVL 1

Author Comment

by:Solve
ID: 10675438
Sorry, Helena, it was my mistake
Now it works. When it is not a new record LLab = AERO_ID, when I need to convert it to number
but if it is a new (user's entered value) LLab = text...
So if there any possibilities to check if it is a new record?
Mabe here is is why my trigger works not corectly?

Solveiga
0
 
LVL 1

Author Comment

by:Solve
ID: 10675466
I need to solve this from here:

LLab := :CMB_AERO;

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_ID = to_number(Llab));

When I select a value from combobox LLab = (e.g.) 42 this is a record's ID
But if I enter something new and want to check is such record in DB I have when LLab = (text I entered) 'My new aerodrom'
It is VARCHAR and when It would be klever to use such code

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_NAME = LLab)...

How to catch if it is an old record selected or new value entered?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10675497
If it is for block - there is GET_RECORD_PROPERTY built-in there. The status of a new record (which is not in a table in a database) is NEW or INSERT.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10675518
But if user enter a new value in a combo box then there is no returning value there (record group 'RG_AERO') so you know it is a new value.
0
 
LVL 1

Author Comment

by:Solve
ID: 10675543
I tried this, but in both cases block status is 'CHANGED' because I change a combobox.


Would it be normal solution if I handle exception INVALID_NUMBER ?
Because whene do :

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_ID = to_number(Llab));
Trigger raise an unhandeled exception ORA-01722, because cannot convert LLab to number.

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10675577
I think that you can handle an exception.
0
 
LVL 1

Author Comment

by:Solve
ID: 10675598
How to do undo when I want a value from combobox to be deleted?
Clear_item doesn work in when-validate-item :(

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10675632
But there is another option you can use. If you want user to input only numbers then use Format Mask.
"Display item format mask symbols for CHAR values" - e.g. FM999
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:Helena Marková
ID: 10675650
You can set the item to NULL.
:your_item:=NULL;
0
 
LVL 1

Author Comment

by:Solve
ID: 10675856
nope...my plans to handle such excpetion was not successfuly...

What to do?
 Mabe I can when somehow to chek is LLab is from numbers or from characters??

Solveiga
0
 
LVL 1

Author Comment

by:Solve
ID: 10675909
I need to solve this priblem again

1. LLab := :CMB_AERO;

In case it was selected from combobox's values LLab = (e.g.) 42 and it's value is record's ID.

In case it was entered by user - Llab = TextEnteredByUser

In both cases LLab is characters, but in 1 case I can convert it to number and when have an ID.
...
 
1 case I need
select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_ID = to_number(Llab));
--and when to do something

2 caes I need
select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_NAME = LLab)...

How to catch if it is an old record selected or new value entered?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10676256
Why don't you use Format Mask ? e.g. FM999

or you can use this test:

SQL> select decode(translate('A123456','1234567890','Y'),'Y','Y','N') from dual;

DECODE(TRANSLATE('A123456','12
------------------------------
N

SQL> select decode(translate('123456','1234567890','Y'),'Y','Y','N') from dual;

DECODE(TRANSLATE('123456','123
------------------------------
Y
0
 
LVL 1

Author Comment

by:Solve
ID: 10676596
I have never know about this :(
Now I am going to train

Solveiga
0
 
LVL 1

Author Comment

by:Solve
ID: 10676627
Could you give an example to my case with format mask?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10676800
If the lenght of your item is 4 then the format mask is:
FM9999

All necessary informations you can find in the on-line help - "Format Mask property" topic.
0
 
LVL 1

Author Comment

by:Solve
ID: 10676894
I am afraid, that this is not what I need...
It has to be a way to designate is it value selectet from list or entered by user, isn't it?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10684550
If you want to know if the value exists in a list then you can find it in a record group which is used for populating the list.
0
 
LVL 1

Author Comment

by:Solve
ID: 10684756
Good morning :)

Actualy I populate list from table so would it be the same if I find it in table?
But I am not 100% sure, that I can lookup for a record when I don't know what is my combobox value.
One time it is numbers, second time it is characters:(

So when I do fast select and value is selected from list:

LLab := :T_DAL.CMB_AERO;
select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_PAV = Llab);
I get yra=0, because there is no such record where AERO+PAV = 42 (cause it is ID)

But it works correctly if I enter new value, save it to DB (do insert and I have LLab = TextIEntered)

Is there any possibilities to get_list_element_value if it is combobox?
I need to know a list_index for that

Solveiga






Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10684889
Of course, see GET_LIST_ELEMENT_COUNT example in the on-line help. You have to LOOP through the list.
0
 
LVL 1

Author Comment

by:Solve
ID: 10685082
According to that question I would like to ask one more... and my problem will be solved.

I succseeded to check if there is such value in DB, and if is not, I call my form for enter new aerodrom.
when I enter new record with all required fields. I save it and want this value to be in my other form's that Item
:T_DAL.cmb_aero.

Could you help?

Solve
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10685168
You can store the value in
1) a global variable
 or
2) in a package variable in a library attached to the both forms (this is better). You have to use CALL_FORM or OPEN_FORM with parameter SHARE_LIBRARY_DATA.
In such cases it is accessible to the both forms.
0
 
LVL 1

Author Comment

by:Solve
ID: 10685184
First I choice the first varian. I store value in a global

And how to assign it to my combobox when I close that form?

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10685246
Do you mean to store it to the block item or list ?

If to the the block item - then something like this:
IF :=:global.your_variable is not null THEN
    :T_DAL.cmb_aero:=:global.your_variable;
    :global.your_variable:=NULL;
END IF;

if to the list - populate the list once again.
0
 
LVL 1

Author Comment

by:Solve
ID: 10685292
Yes...I understood,
but when I call_form, it doesn't allow me to save record. It say :
FRM-40403 A calling form has unapplied changes. Save not allowed...
when I pree exit form and it say
do you want to apply? I prees ok, when get a message FRM-40404 record applyed..

But it doesn't insert into DB and I dont see it in cimbobox. My global = null:(

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10685327
Open your called form with OPEN_FORM built-in - with parameter session -
SESSION  Specifies that a new, separate database session should be created for the opened form.

0
 
LVL 1

Author Comment

by:Solve
ID: 10685333
You see, I ckeck that combobox value in when validate item trigger and I can't do open form
so I do call_form ...


FRM-40737:  Illegal restricted procedure  OPEN_FORM in when-validate-item trigger.

Cause:      Application design error.  A trigger tried to execute a restricted packaged procedure.

Action:      Remove the packaged procedure from the trigger text.

Level:      >25

Type:      Error
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 50 total points
ID: 10685394
It cannot be done such way because it is the same transaction.

You can display message to the user, cancel changes and use button for opening form for input a new value.
0
 
LVL 1

Author Comment

by:Solve
ID: 10685428
So now I am doing this... Have no choice :(

And closing this question...

Solveiga
0
 
LVL 1

Author Comment

by:Solve
ID: 10685614
Thank you Henka for your pacience very much!
Good luck :)

Solveiga
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10685655
Good luck with Oracle Solveiga :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now