Update table problem 2

[scomfort]

Hi there,

I'm having a problem updating a table in Oracle Forms 9i....
The user searches for a match in table A (consists of a sequential primary key and 4 other data fields) and if a match is found the results come up; they then choose one of the data fields by way of a radio button changing its value from y to n. However when it comes to committing it will not allow this and an error message saying cannot insert record pops up...looking at the error says unique constraint violated for the primary key. So its as if its trying to create a new set of data with the same primary key but the differing value for whatever the user chose. All I want is for it to update the data field in the already existing primary key.
Just to make sure the primary key was working ok I displayed it on-screen and it comes up fine. Just seems to want to insert rather then update! However I will need to insert too in case there is no match when the user performs the search. In this case a new primary key is generated with its respective data fields filled in. This inserts and commits fine, since there is no matching primary key. Its when it comes to updating an existing table that I encounter a problem.
Any ideas?
Thanks.

 

[BJCooperIT]

"if a match is found the results come up"

Have you coded your own SQL that selects and copies the existing table row into a row in a base table block? If so, then that result is correct. By copying a row already in the table forms will treat that row as an insert.

If this is the case, then the form should allow the user to select their match by using QUERY-BY-EXAMPLE. The block will automatically fetch the matching row(s) during EXECUTE_QUERY and the row will be marked for update when the change is made.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[scomfort]

Hi BJCooperIT - thanks for the helpful info!
Yes you were right I was using a select-into so of course was inserting like you say. Im unfamiliar with the use of queries but had a look at the online help; unfortunately I cant get it to work still
Can you give a few hints on how to go about this?
At the mo the user will select two criteria which when the second has been selected will search the database for its 3 respective fields (query perfrormed here I guess). All these fields are in the same table and are headed by a primary key which the user will not know. The select-into code which I used which inserted fine is as follows:

select tdid, slota, slotb, slotc
into :timeslots.tdid, :timeslots.slota, :timeslots.slotb, :timeslots.slotc
from timeslots
where bday = :timeslots.bday
and timeslots.vid = :vtype.vid;
...

The tdid is the primary key and slota,b,c, are the radio buttons which is what needs updating when the user selects one and the bday and vid are the criteria with which I am searching.
That worked fine for inserting - if you could help us with the equivalent query code then that would be great as at the moment I'm pretty stuck!?

 

[BJCooperIT]

You might benefit from reading:
faq259-3484 - What are the basic concepts of Oracle Forms?

As the last part of KEY-STARTUP, the basetable block should be put in ENTER_QUERY mode. The

:timeslots.bday

and

:vtype.vid

items should have QUERYABLE = TRUE in their property pallettes (and the other items false?). These two items most likely will have LOVs associated with them from which the user will make their value selections. Once they are valued, the user normally clicks on EXECUTE_QUERY from the menu, or clicks a button if one is provided, or presses the EXECUTE_QUERY function key. Forms then builds the SQL statement and populates the block with matching rows. At this point the user could make changes to the rows by means of the radio buttons.


[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[scomfort]

Thanks for the useful info again - you're very helpful you know
Ok now its updating partially ok. The only problem being is that the query is only searching for the :vtype.vid field but I also want it to search the :timeslots.bday at the same time. Thus when I do a query I'm getting multiple occurences of the same vid field but different bday's. It should only return one result or if nothing found insert a new record. The bday field is populated by the user pressing an ok button within a calendar and the vid field by way of an LOV - they both populate fine (or at least appear on-screen as should be!). Both the bday and vid items are set to query allowed in their properties. So Im not sure why the query doesnt look at both items?

 

[BJCooperIT]

It sounds as though

:timeslots.bday

does not have the property BASE TABLE set to true.

Hope this helps too!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[scomfort]

Where do you set this property because I cannot find it in the property palette of the :timeslots.bday item?
Is there anything else it could be as to why it isnt querying using both items, as to my knowledge I havent changed specific properties? More so its always nearly something "simple" with me!
Cheers for helping me again.

 

[BJCooperIT]

What type of item is

:timeslots.bday

?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[scomfort]

:timeslots.bday is of type date.
So far after populating this field with the calendar and then pressing execute_query it will give me more than one date for the same :vtype,vid (i.e. not searching :timeslots.bday). However if instead of clicking execute_query I click on enter_query it deletes the date already there? I then tried putting the exact same date manually and clicked on execute query and then it does what I want by searching both fields. No idea why?

 

[sem]

Is your block in ENTER_QUERY mode when you fill that field from calendar? I think that you populate it from calendar in NORMAL mode, thus it does not affect your query criteria.

Regards, Dima

http://seminihin.narod.ru

 

[scomfort]

Hey Sem,

I'm unsure as to how to make the :timeslots block go into enter_query mode programatically? (Just to let you know the date field is populated by use of a when-button-pressed trigger - this is in a separate block (date_control_block). The calendar is that used in demo Forms from the Oracle site and the only code in the when-button-pressed trigger is date_choosen. Dont know if this helps but any feedback would be great).

 

[sem]

As far as I remember Oracle calendar doesn't allow entering data in ENTER_QUERY mode, because inter-block navigation is not allowed in this mode and this element is implemented as a separate block. You may either refine that element or create something more robust. You have multiple choices: implement it as a separate form, use OLE (works only on Windows) or anything else.

Regards, Dima

http://seminihin.narod.ru

 

[BJCooperIT]

A block is cleared when it is placed in enter query mode.
KEY-STARTUP

..... your code .....
go_block('TIMESLOTS');
enter_query;

places the block in enter query mode. You should see a message on the status bar at the bottom left of the screen. At this point the user may enter their select criteria. When ready to query if they press/click Execute Query, forms will select matching query rows from the block's base table and populate the block.

You do not need to code a select statement for this query as forms does that for you. When placing a block in enter query mode, forms clears that block to ready it for the matching criteria. This is why, if you have any pending inserts/updates/deletions you are asked if you want to save the changes.

As to why the

:timeslots.bday

does not have a queryable property, compare the property pallette for

:timeslots.bday

to it. Certain properties are only applicable to certain types of fields.


[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[scomfort]

Sem. Thanks for the advice. I will try out creating a new form just for the calendar. I presume that you will return the date selected in this calendar as a parameter to the main form which will fill out the :timeslots.bday field? If so where would you put enter and execute query so when the user selected the date in the new calendar form it will query automatically the main form.
BJCooperIT, cheers again. I know a bit more about queries then I did. Yes it queries fine if I enter in the date manually in the :timeslots.bday field but really it should be carried out automatically using the calendar. Of course putting enter_query just after the code which populated :timeslots.bday cleared what was already selected by the calendar. Putting enter_query just before doesnt work either - perhaps because of what Sem said.
Thanks for your help again - eventually I'll get it working (I hope!).

 

[scomfort]

Hello again,

I was looking on technet site and they have an alternative solution as follows:

Create control block with the same sructure as main one.
For main block:
Key-Entqry
GO_BLOCK('CONTROL_BLOCK');
Pre-Query
if :control_block.item_1 is not null
then :main_block.item_1 := :control_block.item_1;
end if;
...
if :control_block.item_N is not null
then :main_block.item_N := :control_block.item_N;
end if;

Key-Exeqry for control block:
GO_BLOCK('MAIN_BLOCK');
EXECUTE_QUERY;

The person said it worked for them but would this work in my case (they're using same calendar as me)?
I would test this out although Im not sure what they are referring to as the main structure (also presume they mean copy everything)? Would ask on the other site but its not working.
Cheers.

 

[sem]

Yes, this is one more option. But the main drawback is that you have to create control block for each base table block you need to query. This is quite easy when you use some generating tool (like Designer), although harder when you create your forms manually :-(
Separate calendar form may return date via some global variable.

Regards, Dima

http://seminihin.narod.ru

 

[scomfort]

Hey Sem I decided to go along with your previous idea of creating a new form for the calendar. I created the global variable :global.gday and that works fine (as a test I displayed it on-screen on the main one and it appeared correctly, in date format). However after exiting the separate calendar form and back to the main one how would you automatically get it to perform the query using the global variable just created?

 

[sem]

I don't suppose that you should query your block immediately, just assign a value in ENTER QUERY mode and let user continue entering further criteria. You may use that calendar form as a LOV by adding key-listval trigger:

CALL(<calendar form>,NO_HIDE);
:<field> := :GLOBAL.<variable>;

Thus in any mode user may call that calendar window to simplify entering dates.
You may even create reusable component for displaying/editing/searching dates.


Regards, Dima

http://seminihin.narod.ru

 

[scomfort]

Hey Sem thanks for that I think I've got it to work ok (heres hoping!). Just two more things:

1. Is there anyway to stop the pop-up alert saying &quot;do you wish to save changes&quot; when you enter query mode? In all cases I dont.
2. If the query doesnt find a match then I'd like it to create one instead. At the moment it simply asks for another date if there is no match. So basically if no match abort the query but keep the date already there?

Cheers again!

 

[sem]

1. First of all you should know that this message means that some uncommited changes exist. This may be the result of hand editing or some bug in your code. In any case you may call CLEAR_BLOCK(NO_COMMIT|NO_VALIDATE) before ENTER_QUERY. If you use default menu/toolbar you should create KEY-ENTQRY trigger and place this code there.
2. You may create KEY-EXEQRY trigger on that block like:

declare
dt date;
begin
dt := :<date field>;
execute_query;
if get_block_property('<block name>',query_hits)= 0 then

exit_form;
:<date field> := dt;
end if;
end;

You may also get rid of annoying FRM-40301 by creating ON-MESSAGE trigger.

Regards, Dima

http://seminihin.narod.ru