This content has been marked as final. Show 7 replies
Oradb wrote:If Insert Processing works well, then why are you here asking about it.
I created a table have 3 columns. one column is PK and other column set default as 0.
create table test
id NUMBER(12) not null,
salary NUMBER(10,2) default 0.00,
hour NUMBER(10,2) default 0.00
when user insert value at MS access form. salary and hours are blank. insert processing works well.
What does MS Access form have to do with Oracle DB?
does ID exist as NOT NULL value?
But salary and hour column is null. default value 0.00 does not be put into column during inserted data.How do I ask a question on the forums?
We use oracle 11.2 at window 64 bit 2008.
SQL and PL/SQL FAQ
A default value is used if and only if the column is omitted in the INSERT statement. If Access generates a statement
the default values will be ignored and both SALARY and HOUR will be NULL. If Access generates a statement
INSERT INTO test( id, salary, hour ) VALUES( 1, null, null )
the default values will be used for both SALARY and HOUR. If you are observing the both are NULL, that strongly implies that Access is generating a SQL statement that includes all three columns.
INSERT INTO test( id ) VALUES( 1 )
Someone that is more familiar with Access may be able to tell you whether it is possible for Access to generate a statement like the second one rather than the first one. I would tend to suspect not unless you insert into a view that omits both the SALARY and HOUR columns but doing that likely complicates whatever it is that you're trying to achieve in Access.
Oradb wrote:Are you saying Access doesn't display 0.00?
Thanks for your help!
ID exist at test table but salary and/ or hour column is null if front MS Access form field is blank and pass a null value.
What do you get if you cut Access out of the picture and select the data from sqlplus?
select id, nvl(salary,'NULL SALARY') nvl(hour,'NULL HOUR') from test;
Thanks for your help.
Yes. INSERT INTO test( id, salary, hour )
VALUES( 1, null, null ). the field is null too.
But developer told me, the user may not be like to enter value in Access form GUI. Developer want to me to set default value in database side.
Any suggestion? As just said, I can ask developer to put nvl funtion but this way will add additional working for GUL developer.
"A default value is used if and only if the column is omitted in the INSERT statement" is a good answer for developer
With a quick Google search I found that setting a default value for a form in access is almost trivial. ("ms access form default value"... 1st hit... office.microsoft.com)
Your business rule appears to be "no entry for salary or hour in the GUI should be stored as 0"
Best option... take advantage of the feature of the GUI environment to set the default value. A tiny amount of work for the developer handles the business rule.
Aggressive option... set the column to NOT NULL if it really must be "0" instead of null. This forces the GUI to pass a valid value. This ensures anyone who maintains the GUI later is aware of the business rule and handles it accordingly. You can still have the database default 0 for cases where those columns are not included in an INSERT.
Worst option... create a trigger to redefine NULL to 0 for those columns when during an insert or update. This hides the business rule "null should be saved as 0" from the developer, or anyone later modifying the Access GUI. It shifts the work from the original GUI developer to everyone who has to maintain the GUI or the database later.
Passive option... Ignore or change the business rule, continue to allow nulls, do not bother with defaults in the GUI. Anything that processes the data must add NVL() when handling salary or hour, shifting the work from the GUI developer to everyone else who uses the data.
It is cheaper in the long run to have the GUI developer spend the small amount of time to get the insert right up front, rather than having multiple people re-handle the business rule after the fact, or add extra complexity in the database via a trigger.