• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4791
  • Last Modified:

COMMIT causing "FRM-40405 No changes to apply." error

I've got a Save button that contains the code below.  It works fine the first time I click it, but on the second click I get the error message "FRM-40405 No changes to apply" even if there WERE changes made.  It saves them and seems to work like normal, but I still get the message.  This is a twofold question:

1) Is there something wrong, or is this normal behavior in Oracle when I commit again?

2) If this is normal, how can I suppress the message?

Here's the code.  Thanks again.

--------------------------
update SEI_Inv
   set SEI_Inv_Code          = :txtCode,
      SEI_Inv_ManufacturerID = :txtManufacturerID,
      SEI_Inv_TypeID         = :txtTypeID,
      SEI_Inv_Description    = :txtDescription,
      SEI_Inv_Serial         = :txtSerial,
      SEI_Inv_OrgNo          = :txtOrgNo,      
      SEI_Inv_CountyCode     = :txtCountyCode,
      SEI_Inv_Crew           = :txtCrew,
      SEI_Inv_Room           = :txtRoom,
      SEI_Inv_PurchaseDate   = :txtPurchaseDate,
      SEI_Inv_VendorID       = :txtVendorID,
      SEI_Inv_EncNo          = :txtEncNo,
      SEI_Inv_Voucher        = :txtVoucher,
      SEI_Inv_Cost           = :txtCost,      
      SEI_Inv_AppValue       = :txtAppValue,
      SEI_Inv_TransDate      = :txtTransDate,
      SEI_Inv_TransNo        = :txtTransNo,
      SEI_Inv_FedCode        = :chkFederalCode,
      SEI_Inv_StatusCode     = :txtStatusCode,
      SEI_Inv_LastUpdate     = sysdate
   where SEI_Inv_ItemNumber = :txtItemNumber;

commit;
0
bek
Asked:
bek
  • 5
  • 3
  • 3
  • +1
1 Solution
 
ser6398Commented:
When you run an explicit Update statement directly, Forms doesn't know that there are changes to commit.  It only knows that there are changes if you change the values in the fields in the form itself and then hit Save.  The method you are using will work, but it goes against the way Forms should be used.  You should have a database block for the SEL_Inv table, make your changes on the form, and then hit Save.  Forms will create the update statement itself, run it, and then commit.

If you wish to continue with the current method, you can create a When-New-Form-Instance with the following code to suppress that warning message:

:SYSTEM.MESSAGE_LEVEL := 5;

0
 
jtriftsCommented:
to follow up on ser's comment, you may want to set the message level back to its original value after the commit statement (otherwise all minor messages will remain supressed).

e.g. When-Button-Pressed on Save button:

update SEI_Inv
  set SEI_Inv_Code          = :txtCode,
     SEI_Inv_ManufacturerID = :txtManufacturerID,
     SEI_Inv_TypeID         = :txtTypeID,
     SEI_Inv_Description    = :txtDescription,
     SEI_Inv_Serial         = :txtSerial,
     SEI_Inv_OrgNo          = :txtOrgNo,      
     SEI_Inv_CountyCode     = :txtCountyCode,
     SEI_Inv_Crew           = :txtCrew,
     SEI_Inv_Room           = :txtRoom,
     SEI_Inv_PurchaseDate   = :txtPurchaseDate,
     SEI_Inv_VendorID       = :txtVendorID,
     SEI_Inv_EncNo          = :txtEncNo,
     SEI_Inv_Voucher        = :txtVoucher,
     SEI_Inv_Cost           = :txtCost,      
     SEI_Inv_AppValue       = :txtAppValue,
     SEI_Inv_TransDate      = :txtTransDate,
     SEI_Inv_TransNo        = :txtTransNo,
     SEI_Inv_FedCode        = :chkFederalCode,
     SEI_Inv_StatusCode     = :txtStatusCode,
     SEI_Inv_LastUpdate     = sysdate
  where SEI_Inv_ItemNumber = :txtItemNumber;
:SYSTEM.MESSAGE_LEVEL := 5;
commit;
:SYSTEM.MESSAGE_LEVEL := 25;


regards,

JT
0
 
ser6398Commented:
By the way, if you use the method I described, then all you put in the Save button is:  COMMIT_FORM;
 You no longer need to have the update statement coded anywhere.  

This is the way Forms was meant to work:  you create a Database Data Block based on the SEI_Inv table and add all the columns you need to it, you add the fields that users can see/modify to the layout, you create a button with COMMIT_FORM in it, run the form, select Enter_Query, enter query criteria, select Execute_Query, Forms returns a set of rows, you go to the rows and make changes (as soon as you make changes Forms flags that row as needs to be updated), you delete a row (as soon as you delete a row Forms flags that row as needs to be deleted), you add some rows (as sonn as you add a row Forms flags that row as needs to be inserted), then you hit Save button.  Forms goes through the rows and builds the Insert, Update, and Delete statements to make the database reflect what was done in the Form, and runs these statements itself, then commits.  You should almost never have to explicitly write Insert/Update/Delete statements in a Form.  If you build the form correctly, Oracle Forms will do all of this for you.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
ser6398Commented:
"SYSTEM.MESSAGE_LEVEL := 25;" is not the default.  If he wants to set the message level back to default, he should use:

:SYSTEM.MESSAGE_LEVEL := 0;

0
 
jtriftsCommented:
oops...you're absolutely right...
In fact though zero may not be what it was previous to yoou updating it, so in fact unless you specifically want it to go back to zero, you're probably better off setting a variable to the old value, changing it to your desired vbalue and then resetting it back to what it was before you changed it...
e.g.
oldmsg := :System.Message_Level;
:System.Message_Level := '10';
--do your stuff
:System.Message_Level := oldmsg;
0
 
bekAuthor Commented:
I set the message level both before and after my commit, as jtrifts recommended, yet I still get the error message.

Also, the *first* time I save, I don't get an error message--only the second time.  Can anyone offer any insight why this message doesn't show up the first time, and why I can't seem to suppress the message?

Thanks.
0
 
jtriftsCommented:
uncertain...but here is more detail on the message level thing...and you should consider what ser has noted re: normal forms behaviour.

Insert statements should not be required very often unless you have good reason for not basing blocks over base tables.

regards

JT

To control the messages that end users see when they use a Form Builder application, you can:

n     use the SYSTEM.MESSAGE_LEVEL system variable to suppress specific "severity levels" of messages

n     use On-Error and On-Message triggers to replace the standard processing of messages

n     use the SYSTEM.SUPPRESS_WORKING system variable to prevent the update of an end user's screen (by suppressing the "Working..." message)

Message Severity Levels

Forms Runtime messages are ranked by severity.  Use the SYSTEM.MESSAGE_LEVEL system variable to can control the minimum severity level that displays to end users.
There are six levels of message severity that you can affect, listed here in increasing order of severity.

Level     Message  Description
0     All types of messages from the other levels of severity.
5     Reaffirms an obvious condition.
10     Indicates that the end user has made a procedural mistake.
15     Declares that the end user is attempting to perform a function for which the form is not designed.
20     Indicates a condition where the end user cannot continue an intended action due to a problem with a trigger or another outstanding condition.
25     Indicates a condition that could result in the form performing incorrectly.
>25     Indicates a message severity level that you cannot suppress via the SYSTEM.MESSAGE_LEVEL system variable.
Severity levels of individual Forms Runtime messages are labelled with "Level" in the Form Builder online help system.

Message Types

To use On-Error and On-Message triggers to replace Forms Runtime messages, you need to be aware of the three types of Forms Runtime messages:

Informative      Informs end users of the present state of processing  (e.g.,  Last value retrieved.)  or provides end users with context-sensitive guidance  (e.g.,  Press [Accept] to enter answer.).  Use the On-Message trigger to suppress the appearance of these messages.
Error      Inform end users of error conditions that prevent the end user's actions  (e.g.,  Function key not allowed. Press [Show Function Keys] for list of valid keys.).  Use On-Error triggers to suppress the appearance of these messages.  However, you cannot suppress error messages that appear on the command line  (e.g.,  Too many arguments on command line.).

Working      Inform end users that Form Builder currently is processing  (e.g.,  Working...).  You cannot use On-Error or On-Message triggers, or the SYSTEM.MESSAGE_LEVEL system variable to suppress these messages.

Message types of individual Forms Runtime messages are labelled with "Type" in the Form Builder online help system.
0
 
ser6398Commented:
On the first time there is probably some information somewhere in a database block that is new or changed, and Forms tries to save those rows to the database (which may or may not be sucessful).  After this, there are no new changes to be made so further Saves cause the "no new data" message.

Try setting the message level to 5 in the When-New-Forms-Instance trigger.

0
 
ser6398Commented:
By the way, it is possible that Forms is updating the table in addition to your explicit Update statement.  When you use COMMIT, Forms uses it as COMMIT_FORM and tries to commit any changes in the Form.  So you may be doing the Update 2 times, with the same information each time.

For the SEI_inv block, is the Database Data Block property set to Yes?  If so, then just comment out the Update statement and replace COMMIT with COMMIT_FORM and see if it does the update for you.
0
 
bekAuthor Commented:
I don't use any database blocks in my application--right now, they're all control blocks (no field is bound to the database).  This should be the only application I ever have to write in forms, and it's due next week, so I'd rather not go back and tinker with how I save.  Everything seems to work right now, except that I get this extra message.

I will try some of your ideas above to suppress this message.  Let me know if anyone has any new ideas.

Thanks.
0
 
kretzschmarCommented:
maybe you could simple use

forms_ddl('COMMIT');
0
 
bekAuthor Commented:
Kretzschmar,

This didn't work by itself, but by raising the error level to 25, then issuing the forms_ddl('COMMIT'), I was able to suppress the error message.  When I raised the error level to 25 and tried only a COMMIT, then I still encountered the error.  Any idea why?
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now