4 Replies Latest reply: Nov 12, 2010 11:50 AM by ZA RSS

    Alpha in a numeric field - Update Exception Handling

    554271
      Hello,
      here is my issue description.

      In my form, I have several fields that numeric (property set in the form and the db field is also numeric).

      In the form, I have a button called 'update'. Behind the button, WBP trigger updates the table
      with the fields in the form and at the end there is a simple message saying that the form values
      have been updated in the db. However, let us say if the user keys in '10abc' (alpha) in the field
      that is defined as a numeric and clicks the update button, then db throws the error message. I want
      the update to fail at this point and hard stop the user giving a message saying that alpha is not
      accepted and needs to be corrected before updating.

      I tried putting an exception around the update statement. But it is not catching the error.

      How can I catch this exception? Any ideas?

      PS: It is actually the form that throws the error message with FRM-40209: Field must be of the form 999999.99.

      Thanks,
      Chiru

      Edited by: Megastar_Chiru on Nov 12, 2010 10:37 AM

      Edited by: Megastar_Chiru on Nov 12, 2010 10:44 AM
        • 1. Re: Alpha in a numeric field - Update Exception Handling
          CraigB
          Chiru,
          What is your Forms version? I'm using Forms 10g (10.1.2.x.x) and I get an input error (FRM-50016: Legal characters are 0-9) on a numeric field if I enter Alpha characters. I also tested this in Forms 6i and got the same results.

          Based on this, I would guess that the Forms Data Type for your numeric field is not set to NUMBER.

          Hope this helps,
          Craig B-)

          If someone's response is helpful or correct, please mark it accordingly.
          • 2. Re: Alpha in a numeric field - Update Exception Handling
            554271
            Unfortunately, I am having to use older forms 6i.

            What was happening was, my on-error trigger, was set to null. Hence it wouldn't show any message. After I got rid of the on-error form level trigger, it is displaying the message. However, how do I hard stop the user for that form error?

            Thanks,
            Chiru
            • 3. Re: Alpha in a numeric field - Update Exception Handling
              CraigB
              Chiru,
              how do I hard stop the user for that form error?
              I'm not sure what you mean. Are you wanting to prevent the user from even entering an "Alpha" character? If so, Forms does not provide real-time Key Filter functionality. You can achieve this using a Java Bean, but your form would have to be web deployed in order to use a Java Bean. If your application is Client/Server - you'll have to live and work with the error. :-(

              You could put a Format Mask on the field. This will not prevent the user from entering "Alpha" characters, but at least the default error (FRM-40209) from Forms will display format mask instead of the standard error (FRM-50016: Legal characters are 0-9 - + E).

              Hope this helps,
              Craig B-)

              If someone's response is helpful or correct, please mark it accordingly.
              • 4. Re: Alpha in a numeric field - Update Exception Handling
                ZA
                If you really need real time check to see if user entered the right value, then you may consider this.

                Create a timer on WHEN-NEW-ITEM-IINSTANCE of the item you want to validate.
                -- WHEN-NEW-ITEM-INSTANCE
                
                DECLARE 
                  timer_id Timer; 
                  tm NUMBER(5) := 1000; 
                BEGIN 
                  timer_id := CREATE_TIMER('my_timer', tm, REPEAT); 
                END;
                And create a WHEN-TIMER-EXPIRED trigger like this:
                -- WHEN-TIMER-EXPIRED
                
                IF :YourBlock.YourItem IS NOT NULL THEN
                  :YourBlock.YourItem := TO_NUMBER(:YourBlock.YourItem);
                END IF;
                And finally delete the timer when you navigate out of the field.
                DECLARE 
                 tm_id Timer;
                 tm_name VARCHAR2(20) := 'my_timer';
                BEGIN 
                 tm_id := Find_Timer( tm_name ); 
                   IF NOT Id_Null(tm_id) THEN 
                     Delete_Timer(tm_id); 
                   END IF; 
                END; 
                Hope it helps!