VB .net and Microsoft Access - NULL ISSUE

Hi Everyone! I'm stumped and need some assistance please.

I created an inventory system in VB.net that talks to Microsoft Access. The Access setup is fairly simple - 4 tables. The VB application can query the database, display data in textboxes, update fields in the database, or delete entire records.

Everything worked fine until I had to add a field in one of the tables to accommodate item ownership. I added the field, and then things started acting strange in the VB app. Only half of the text boxes would populate when the database was queried.

Long story short, the problem ended up being null values in the database. Adding the new database field screwed up the database somehow. I'm not sure I am wrapping my head around this correctly, but when I was initially populating the database through the app and saving data records, empty fields in the VB app were saved in the database in a particular format.... yet adding a new field in the database contains data of a different format (or lack of a format... i.e. null). When my program hits a null in the database, it sort of breaks, and cannot continue past the field that contains a null.

My question is, HOW DO I HANDLE NULLS IN THE DATABASE?????? Can I replace nulls in the database with some other empty value through a SQL query?? I have tried to go in and manually add a " " (a space) and save the database, but it doesn't save the empty space in the fields. Same problem exists.

I have done lots of google searches, and have identified that this is indeed an issue, but there seems to be no viable resolutions from the database end.

It is not practical to re-inventory 1000 parts, now that the new field is added. Further, I can see that down the road we will have to add another field or two. It cannot be the case that I will have to re-inventory everything over and over every time I add a new database field to remedy the null issue.

Can anyone help me please???

Just an FYI - I am NOT an expert programmer, so if you provide a suggestion do you think you could contribute the code or SQL statement to try out on my end? I'd appreciate it!!!!

-Dave

Unfortunately, there are two distinctly different ways to handle this depending on how you are accessing the database. If you are using TableAdapters, then you will have a special method for nulls. I find this non-intuitive, but it is what it is. Alternatively, if you are accessing the database more directly with raw SQL and things like Dataadapters, Datareaders, and such, then you have a few methods you can use, most notably IsDBNull. You'd just be checking for Null in that case.

So, the right answer depends to a large extent on what you are doing. If you show us the line that breaks, or the function that includes the line that breaks, we can probably give you a much more specific answer.

Try the NZ function...

like this:
select Field1, Field2, NZ(Field3, '') as Field3 from sometable ....


that's two tick (') marks... NZ is Access's version of the ISNull funciton... if the first parameter (in this case Field3) is null, then it returns the second value (an empty string)....

that should be enough to prevent hte code from choking on the null.

-tg

if you have problems with null's
just do not allow them in the database
set the 'requered' property of all your fields to true
set the 'allow zero lenght' property of text fields to false
give all your fields a fitting 'default' property

if you really want to go all the way:
give them a validationrule of 'Is Not Null' (without the quotes)

note: i have a dutch version of acces installed
so i am not sure of the property names

and for good measure replace all the null's now present in your fields with a valid value

I don't agree with that. That's a way... but not one I agree with.

-tg

I'm not sure that's even possible in this case. If you have a table with a bunch of records, and add a new field to it, the value of that field will be either Null or the default value, if one is supplied. So, the only way to avoid nulls in that scenario is to have a default value. There are plenty of fields for which a default value, in this scenario, doesn't make sense. For example, since I work with fish, we might add a field like Gender. From that point forwards, we'd be able to determine the sex of the fish. For all the fish prior to that, they had a gender, we just don't know what it was. So, we could come up with some kind of dummy default value like "Unknown", but Null works just as well, and is actually a bit more accurate. The data was there, and it wasn't unknown, it just wasn't recorded.

That's what NULL is ... the unknown.
Your example is the perfect reason and use for it. There's a difference between a blank value (it's a known value, but not recorded - think of the "Prefer to not answer" that oft found on surveys) ... and a NULL, which is more of a "no response" or "unknown".

Even still, adding fields to tables is risky as it is. If something is going to have nulls, I prefer to deal with it at the extraction level, rather than in the table itself.


-tg

null does not mean not recorded
null does not mean unknown
null does not mean forgotten
null does not mean refused to enter
null does not mean...anything you want

and that is exactly the problem with null's
they have no meaning
there is nothing that is equal to null
null is not even equal to null
null is not even equal to itself

sexing some fish may be really difficult indeed
what is the sex of a clown fish ? male, female, hermaphrodite, or what ?

please do not misinterpret me
i am completely comfortable with using nulls
but topic starter seems not to be

but then again...i am a complete newbie to .net
so maybe null's do have meaning in .net ?

just think of it, if you give a meaning to null , such as "no responce" or "unknown" or whatever
then why not use just that ?

No, you're right about what a null is. Null is null and that's about all. It really only has meaning in a database context. There's now something in .NET called a nullable type, which is kind of taking null into the language, though I still have some doubts about those types, and have yet to find a need to use one.

In general, we offer up an explicit value for unknown for those cases that really are unknown, and the sex of a fish certainly can fall into that category. Some fish you can only tell by dissection, but you happened to pick a good example of one that is difficult....and then there are the wrasse, which can change gender. Fortunately, I don't have to deal with them. I suppose that for such a species, you'd have to say not just the sex but the time of observation, because the sex is time dependent. Pretty strange fish. The fish I work with are much simpler. If you can get your hands on them, you can often tell by looking at them.

There are people who have policies of not allowing nulls in databases. I don't believe in that policy. I think that null is a perfectly valid value...or a lack of a value, or whatever.

Quote:

---

Originally Posted by RDevos

just think of it, if you give a meaning to null , such as "no responce" or "unknown" or whatever
then why not use just that ?

---

Because "Unknown" doesn't fit into a DateTime field very well, nor does it fit into an Integer field... I get an invalid cast errors when I try that. But I can set it to NULL.

I didn't get the impression that hte OP wasn't comfortable with NULLs, just inexperienced in how to handle them. True NULLs by themselves don't have meaning... but that doesn't negate the value (or lack of) of a NULL... It's nothing, non-existent... it's the absence of data or value. A data vacuum. How you use it depends on the data and its use.

Think about this site... in the settings you have the ability to set your birthday... it's optional ... there's a number of us that choose to not enter it... so what should it store? you can't convert a blank into a date... but you can put a null in there... that's appropriate.

Quote:

---

null does not mean not recorded
null does not mean unknown
null does not mean forgotten
null does not mean refused to enter
null does not mean...anything you want

---

So now we know what you think NULL isn't... now tell me what it IS... clearly it serves a purpose... so what is that purpose?

-tg

Meanwhile we haven't heard a peep from the OP since post 1...

-tg

It took me a while to realize the situation, now I get it. I write big paragraphs so I'm going to make the important part stand out, Shaggy Hiker made me understand it with his last post.

Quote:

---

When you add a new column and rows exist, each row has to get a value for that column. If you provide a default value, that's what everything gets. Otherwise, the only reasonable value is NULL.

---

Sometimes there's a default value that can be applied. Other times, you really can't pick one. When that happens, you have to allow NULL, and your UI has to be prepared to handle it. It's a pain. That's why, in general, it's recommended you fully design your tables BEFORE entering data. Adding columns when there's thousands of rows is tricky business.

REDevos: I agree with you, that NULL is problematic because its meaning is always ambiguous. But programming decisions are context-sensitive, and in this context we (probably) don't have the luxury of choosing "not null" values for the column. Maybe. "Never use null" is a policy that has very good arguments in favor, but "use null judiciously" is also a policy with very good arguments in favor. The only policy I can't support is "don't think about null, use it like salt".

ddeprospero, if I were you, I'd learn to safeguard my UI from null values. This can vary a little depending on how you're getting data from the db into the UI and vice versa. Maybe you haven't appeared in a few days because you don't like the answer. I don't think it's going to change.

A reasonable option might be to take a "no NULL allowed" approach like RDevos advocates. I don't know what the column actually represents, but this would involve adding a default value that effectively means "This hasn't been set yet." This is janky for Boolean, DateTime, and several other column types depending on your program's constraints. For example, your "unset" rule for DateTime might be "any date before 1980 represents unset", but only if your program shouldn't ever reasonably store a date before 1980. Generally it involves a little bit of thought and a little bit of updating your logic to handle this case.

Unfortunately, "you can update your program's logic to handle that safely" is functionally equivalent to how you handle leaving NULL inside. The only benefit to having a not-null value mean "unset" is you can tell the difference between, "Something in my program is screwing up and setting NULL" and, "I haven't filled in this column yet".

So you're going to have to update your UI/logic no matter what. No way around it.

If you're just straight executing SQL queries and using things like DataReader to deal with the values, you need to be checking that each value is not System.DBNull before you use it. If your code encounters DBNull, then you need to convert it to some reasonable value the UI can display. This is notoriously difficult for DateTime fields, as DateTimePicker doesn't have facilities for handling this.

Shaggy Hiker mentioned a different approach: some APIs have special methods or mechanisms you can use to adjust values from DBNull to a more UI-friendly type. There's probably half a dozen other ways to approach it.

But you can't escape it: now that your database might have values that aren't 100% valid in them, your UI and logic are going to have to adjust to that.

Quote:

---

So now we know what you think NULL isn't... now tell me what it IS... clearly it serves a purpose... so what is that purpose?

---

let me put it this way:
null is part of the collection of values (or non values) in any/every domain
and as such it allows an easy way out of a difficult spot
a 'fact' may very well be precisely known by a person
but that person may have no knowledge of a common frame of reference to communicate about that fact
an example, a mother is very well aware of the day her baby was born
but if she has no notion of a calendar, she can not say to you on what day of what month of what year her baby was born
but supposing she can count and use the decimal systen, she can say 'my baby was born 332 times the sun went up after my husband died'
and if you ask her: 'when did your husband die ?, she may answer you '332 times the sun went up before my baby was born'
the frame of reference of a DateOfBird field in a database is a calendar
and the frame of reference for the mother is the day her husband died and the day her baby was born
so now we have a problem, how to note the DateOfBird ?
easy as pie: null is part of the collection of days in the calender
as wel as the collection of days past the dead of the mothers busband
as well as the collection of days before the birth of her child
simply because null is in every domain

and now i even have an easy answer to "who is the baby's father", it is NULL

That's really poetic and cryptic, but I think this is a religious war.

Sometimes, there's just not a reasonable default for a column. And even when you add extra columns to indicate "the value in this other column shouldn't be used", all you've really done is make a more complicated NULL. In terms of relational database tables, we just don't have a lot of tools to represent columns that may not always have a value.

The thing I realized is it's almost always true if any data might not be present, that's a domain fact that will need to be known at every layer, from DB to UI. No matter what tricks you perform to avoid null in the DB (maybe an extra boolean column), your Model layer has to understand to look for that data and interpret it. And your UI layer needs to be aware that it might need to disable/hide some controls. It doesn't matter if the value's NULL, or if there's a Boolean telling you not to use it, or if you use a fancy library with things like Optional types.

There's no real difference between these two requirements:

• If the apartment lease has not been terminated, "terminationDate" is NULL.
• If the apartment lease has not been terminated, "isTerminated" is false and "terminationDate" is the same as "endDate".

No matter what I do, my UI and model need to be aware that "terminationDate" is a special column that sometimes should not be trusted. Whether I use NULL or a trick to represent that isn't really relevant, it's my job to make sure each layer of my code expresses this.

Hello everyone!! Thank you for your contributions - I have found much enjoyment reading the tangents that this thread has taken!! Unfortunately I have been away for work on some back-to-back assignments and this project took a back seat for a while.

Just to clarify, I know what null is, and I know why it is used.

My question was..... is there a way to REPLACE nulls in an Access database, in bulk, with some other character such as a " " (space)?

To reiterate, I had to add a few fields to my table which breaks how the program works. I will have to add some more fields down the road. No way around it. I HAVE code to manage dbnull values, but it doesn't seem to handle the issue. I have replaced dbnull with vbnull just to see if there was a difference, and the programs remains broken. When the program parses the database fields and hits a null, it stops parsing and none of the remaining data is read.

While there was indeed a lot of philosophical exploration that took place in this thread, all I really needed to know is if there is a way to get rid of the nulls? The only nulls in the database occur within the field that I added after copious data was collected. Clearly, writing data to the database (even blank data from an empty Visual Basic text box) is enough to replace the null with some other value (perhaps this is a VBnull as opposed to the default DBnull.... I don't know). But one type of "empty" works with the programming, and the other type of "empty" makes my program fail to work, despite code being in place to seemingly mitigate the null issue.

then in the future, when adding collumns, make them "not null" and with a default value that works... a space, an empty string, 0, "Zero"... what ever... and call it a day.

-tg

Techgnome, how do you accomplish technique? In Access, a new column is added simply by adding a column header name... there aren't many options. Are you suggesting a method of adding a field/column through a SQL statement or some other technique? That sounds like the simplest and best suggestion moving forward. Thanks for the clarification!

It should be as simple as putting the table in design mode, adding the col, then in the proprties section at the bottom of the screen, there should be a property "Nullable" or "Allow Null" ... set it to False, so that the field doesn't allow nulls... then there should be another "Default value" ... set it to a space... Save... done.

If that doesn't work, then you'll need to likely use an "Alter table" and "add column" to accomplish it.

Either way, it shouldn't take a whole lot of effort.

-tg

[QUOTE=ddeprospero;5157289]In Access, a new column is added simply by adding a column header name... there aren't many options/QUOTE]
are you really saying you have never opened a table in design view ?
and you have succeeded in making an inventory program ?
my sincere congratulations

Techgnome, thank you very much for your input - it is very helpful and your method will be adopted from here on out!

RDevos, have you never been confused on a topic you are less than familiar with? Yes - I am quite adept at VB.net, though I have NEVER constructed a database-driven program before. I can count the number of times on one hand that I have ever used Access. This possibility is shocking to you? Perhaps you should rephrase your comments so as not to discourage people from asking questions in order to gain knowledge and experience. After all, is that not the purpose of such boards? To learn? I have learned a lot through this process - but your comments have added ZERO to the process. Perhaps you should remember the old mantra - if you have nothing nice to say, it is best to say nothing.

Quote:

---

if you have nothing nice to say, it is best to say nothing.

---

i would like to advice you, to stand before a mirror and speak out exactly those words

also i would like to advice you, to read what is written and not what you think is written

also my sincere appologies for assuming you were so exceptionally intelligent
that you could write an inventory program that used the tables you created
without knowing anything about the fields in the tables you created

i have to admid, i was wrong in assuming so, my bad

but as the saying goes:

Quote:

---

It's an ill wind that blows nobody any good

---

you have made your way of thinking perfectly clear

RDevos, thank you for your apology, as well as the praise for creating the system I created. The overall system design was rather trivial, with the exception of the database component which I was unfamiliar with. Armed with the new knowledge I gained from the great people of this board, I will be able to create a more streamlined system in the future. I will take the high road and disregard the remainder of your comments since they add no value to this process.