Web Development

Ask something
Discussion Locked

Handling Nulls

0 helpful
10 replies

by afisher 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am developing an application in VB using ADO and Access as a back-end. It has a data entry form with required and unrequired fields. I will be migrating to SQL 2000 at a later date. Should I allow null values to be saved in my tables when a user doesn't fill out an unrequired field or should I create defaults of some kind for each data type to be saved in those cases to act as space fillers, so to speak? Thanks!

10 replies

by john_wills

Allow null values to be saved: they will always be nulls until explicitly changed, whereas any value you choose for "undefined" or "unanswered" may turn out to have some other meaning later. Of course, some variables may have a legitimate default value, where the user should get something quite definite when he fails to answer - in those cases the default value should appear on focus being lost. Distinguish the two classes of non-answer by asking whether there is anything the user *must* mean by not putting something in. In doubt, use null.

by steved

In some circles, this question can start a "religious war" (if you'll pardon the expression). In my view, it really depends on your application. Null values exist in order to allow you to distinguish between no entry in an unrequired field and the explicit entry of a default or blank value. If there is some value to your application in making this distinction, if you're ever going to do anything based on the distinction, then you should allow nulls and take on the extra headaches associated with working with nulls. If your application will never care about that distinction, then don't allow nulls and give each field a default.

My two cents. I hope it helps.

Steve Diamond

by sl-campbell

Hey, this is fun! Everyone has there own opinions and only a 100% well thought out project with the future completely in mind, and alot of experience from the past, can answer this. So who has been able to do this?

I would say that it doesn't matter as long as the fields are not required. Nulls can come in handy later, and, as answer #1 says "When in doubt, use Nulls". But again, it doesn't really matter. Then again, with date fields you might want to check if a billing for a booking has been made, or paid, and checking for Nulls on that field is an easy way. Besides, then "PaidOnDate" field wouldn't have a date if it hadn't been paid so it couldn't have a default either! So it will also have to be checked for Nulls when getting the data! And why stop there? Check them all!
The real "Problem" is when reading the data from a recordset that contains Nulls. But it's not really a problem if you get into the habit of always handling them. I usually use a function that checks for Nulls, except for text fields, (rsData.Fields("Name") & vbNullString takes care of that!). And if the Entry box should show a default value then that is done in code.
Checking for and handling Nulls, even if you think that you have prevented data from being entered as a Null, is not just good programming practice. I believe one should always do it. If you always should check for Nulls, then what does it matter if Nulls are saved? (Unless some other reporting program cannot automatically handle orconvert them).

To Be Continued......

by sl-campbell

What I do is create a recordset using my own data class, (I do not like bound controls), get the field values, check for Nulls, assign them to variables, format the values if needed, if they contain no data but need to show a default then I assign adefault and put them into the text boxes. When the user changes values, the values get formated and assigned to the variables. When the user saves, all of the fields used get assigned to the variables and the recordset it updated. You then do not need to check which values have changed and worry about which fields will get updated - they all will - whether with an empty string or a zero, or a date variable with no date.

I never have needed to check a text field for a Null for any other reason but preventing an error. There may be a case like, (this is a silly one), a user claims to have entered a name and , because it is not there anymore, says it must have gotton erased by someone else - but you can prove that it was never entered, (or at least never got saved), by checking for the presence of a Null.

Conclusion:
The good side is IF you allow Nulls and later decide that you do not want them anymore, it is pretty simple to loop through all fields of a database, (or, if you prefer, table), check the field type and update the text fields with an empty string.
The downside is you cannot do this the other way around.

by sl-campbell

Hi again!

vbNullString is the same as using an empty string.it is nothing more than a constant that equates to "" .
Constants make code easier to read, they do not change through-out the program like a variable, make the code easier to maintain. They are resolved one the code is compiled with the appropiate value written into the code, make the code run faster, and unused constants are discarded when you make the EXE!

by afisher

Your answers have been a great help. I have two more things: Have you had any success with the Alias Nulls technique? Should I use vbNullString to account for blank fields when saving data to my tables in order to avoid compatibility problems when I transition to SQL, or should I just use an empty string? Thanks again!

This discussion is closed to new replies.