null fields using default value upon insertion

[herbally]

I'm writing a membership app in vb.net 2008 and I'm trying to setup my db access (access 2007 db) but when I insert a record I get an error on fields that don't allow non-null. Whats the best practice here? just don't include the fields where I want to use my default values in the insert statement? stored procedure? I need an experienced developer to give me a nudge in the right direction.

Thanks in advance!
Herbally

 

[cjard]

It depends on the reason why the field is non-null, and whether defaulting is sensible. If it's "Gender" then you really should be asking the user to fill in whether the member is male or female, and providing a value. If it's "Allergies" and you want to concretely say "None declared" then this can be a sensible default if the user doesnt enter nay value

 

[herbally]

well it's a membership app...one of these fields is date joined which I've defaulted to Now() which should be A-OK....another is date expired which I've set to a predetermined date like 100 years down the road which should be good to go...another is barred(banned) which will default to N...

This is strictly for new member insertion...obviously existing members would need to be handled differently, but those fields won't be null at that point since they will already have been "defaulted"

So am I to assume that excluding these fields from my insert statement would be an acceptable way to handle or is there a better way?

 

[cjard]

If you don't include the fields in the insert, then they will have their default value when the insert has finished. A field that has no specific default value specified has a default value of NULL. Any columns that are not allowed to be null, but actually hence had a null attempted to insert will cause the insert to fail

Make "not allowed to be null" only the fields that your app MUST have populated with a value.. For example if you MUST know the gender of your member, make Gender NOT NULL as a restriction. If it's OK that you don't know the Middle Name of your member, or if it's likely the member won't have one, then don't put a NOT NULL restriction on that field

ps; having re-read your original question:

I get an error on fields that don't allow non-null

DON'T allow NON-NULL = only allow nulls

?

 

[herbally]

I got not explanation for the don't allow non-null...lol...doesn't even make sense to me at this point...I think I was just mind boggled...your last post answered my question though. Thanks!