Search code examples
sqlplsqloracle-apex-5

Unable to do update table from Apex 5 button Error mentions Cannot Insert NULL

Hi People of the Palace

I am not having luck here and tried a lot of things and seems as if I am not able to get the value from the field which needs to update.

There are 2 fields within Apex 5 which I want to update if something if the value is changed from default. :QUANTITY which is a text field inside of a Tabular form and :DISC which is also a text field in the same row. There can be single or multiple rows to that needs to be updated and this is usually where you set the option "updated rows and columns only"

The table SALES_TEMP does has columns ID, NAME, QUANTITY_TO_SELL, DISCOUNT.

The PL/SQL code that is assigned in the process to do this update is as follows.

BEGIN
update SALES_TEMP 
set QUANTITY_TO_SELL=:QUANTITY, DISCOUNT=:DISC;
end;

When I try and update the fields, it will return with

Cannot insert NULL into QUANTITY_TO_SELL

and similar with the DISCOUNT field.

Now I know there is nothing wrong with the query because if I do this:

BEGIN
update SALES_TEMP 
set QUANTITY_TO_SELL='2', DISCOUNT='5';
end;

It does in fact update the table, but it will then do this update to all rows in the table because I have no where clause.

I have had a look through the different options and cannot seem to find why it does not select the data from the fields. My main issue is, I have an exact same query running doing an insert which works.

Also from Apex's Sql Command line option if I run.

update SALES_TEMP 
set QUANTITY_TO_SELL=:QUANTITY, DISCOUNT=:DISC;

I get a popup requesting values for :QUANTITY and :DISC and it then updates the columns so something tells me that this is not getting the values from these text fields.

The SQL command to add to populate the fields are

select ID, NAME, QUANTITY_TO_SELL as QUANTITY, DISCOUNT as DISC from SALES_TEMP;

Obviously each gets assigned as :ID, :NAME :QUANTITY and :DISC in apex.

Solution

  • Seeing as you are using Tabular form I suggest you ensure that the following is set.

    1. On the procedure (in processing), Ensure you have the Tabular form selected.

    2. Ensure the Condition "When button is pressed" is set to use the button you want to assign this process to.