Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Too many DBNull errors - need your help!

My team is programming in and we are using datasets/datatables.
In the live system we are seeing too many dbnull errors.
People (who has been previousily programming in VB 6) are not used to think that value can be dbnull in VB and don't handle those cases as they should.
The programmers are also using SQL Server and there doesn't seem to be problem that they are inserting null into not null column.

Why is this a problem in VB.Net but not in SQL Server - it shouldn't be a problem at all.

Isn't there any way to spot where dbnull errors might occure so we can take a look at those places and correct them before the user see this error?

One way is to use default value in datatables - like use empty string as default for strings. But I have been told that that might cause other problems.

Have you any ideas for me how I can get rid of those dbnull errors?

All ideas welcome!

Wednesday, July 21, 2004

Hi, I'm a manager just like you who doesn't know much about this crazy new code the programmers insisted we use.

They said it was going to make things better, but all it has done is cause more errors. The higher-ups are all over me to get it fixed.

I see lots of "Object reference not set to an instance ..." errors in the bug tracker.

Does anybody know how we can fix all these object reference not set errors? Is there some keyword we could scan for? Maybe there is a HotFix for the problem?


Thursday, July 22, 2004

Okay guys, so assuming you're not just a pair of trolls, here is what I think is going on.

I suspect you are both suffering from programmer teams infected by CV-upgrade-itis. That is they thought that .NET would look good on their CVs and so started using it before they were ready.

I say that because both of these types of error are trivial and result from naïve use of the tools they have been given.

Their desire to upgrade and move on is understandable, as they want to do new stuff, not be stuck in the VB6 timewarp. However, it is critical that they appreciate that this ain't Kansas any more.

Although it is called VB.NET, it bears little relation to VB6, et al. A lot of things have changed, so you need to spend a little time understanding what is different.

For the DBNull problem, your programmers are just being lazy. They need to check for null (DBNull.Value) for every item of data that they get from the database. There are really three main options:

1) Get them to check every single bit of data for null and acts accordingly.

2) Assume that non-null columns in the database will not return null data, so relax rule 1) in these cases. This is fine if you never change your database schema, or communicate changes properly, so that mistakes are avoided.

3) Wrap database access in a library of functions that will do null-replacements for you. For example, empty strings for character data, 0 for numerics, etc. Dates are more tricky as it is often hard to decide what default to use.

For the "Object reference not set to an instance..." errors, this is due to the programmers not initializing their objects correctly. This is the same error repeated over and over again.

You can't do:

Person  myPerson;
myPerson.Name = "fred";

You have to do:

Person  myPerson = new Person();
myPerson.Name = "fred";

Or, you can do:

Person  myPerson;
myPerson = anotherPersonAlreadyInUse;

Apologies for the C# syntax, but it is years since I did VB. Essentially, you can only set up a variable by calling a constructor, via New, or by copying a reference to an instance that already exists.

I have used the word "programmer" above, rather than "developer". This is deliberate. Developers would be more pro-active in finding out what is going wrong here. Programmers just do code, so don't think about the big picture. Check out Eric Sink's article: for details of the differences.

I hope that this is of some use to you guys. Moving to .NET is not a no-brainer, but I assure you it is worth it in the end. And, no, I don't work for MS, I just love their products, especially .NET.

Thursday, July 22, 2004

Nemisis and useless - thank you for your comments.

I think my "developers" are ready for .NET - although this dbnull problem occure. 

You say trivial error - I don't agree - first I think that the VS.NET should help you spot those errors before they occur in the system - I am sure it is possible. Why should you need to program in .NET like you program in the database. Null has never been trivial in databases and has caused many strange errors in there. Yes I know that it has also solved some problems but some errors that it has caused are difficult to handle.

No my programmers are not lazy - and it is not just result from database that has those errors. Also when you are inserting into the datatable from the GUI. When you are programming you don't see it clearly that you need to check for dbnull - why shouldn't you see some tooltip if the
variable you are using need to be checked for dbnull.

Also it is rather ugly to see everywhere in the code some if else sentences just to cover dbnull errors.

"Wrap database access.." - I have been told that that might cause some other problems....

"For the "Object reference not set to an instance..." errors.."

I also think that the VS.NET should help you spot those places where this might happen - I am sure it is possible in many cases.

Why can't VS.NET see at compile time that this doesn't make sense?

"Person  myPerson;
myPerson.Name = "fred";

I would like to see more ideas to solve those problems.

Thursday, July 22, 2004

When I used the word "trivial", I meant that it is a very basic thing to consider when dealing with relational database. I did not mean that the solution was trivial.

How can VS tell whether some field might be null ? I think this would be difficult to implement in a sensible way, apart from trivial cases like "select foo from bar" and it knew that the foo column of the bar table allowed null.

Even then, what if a column didn't allow nulls when VS inspected it, so your code assumed all was well. The table definition might change to allow nulls after all, and your code would have this vunerability again.

Another approach would be to get VS to silently replace all nulls in your data with default values. But which defaults do you think VS should choose ? Also, what about the genuine cases when you do want to see nulls ?

I suppose you could decorate your data access code with attributes to say what you want to do with nulls, but this would be way more complicated than simply dealing with them properly.

VS cannot solve these problems for you. You and your team need to decide how you are going to address this issue and stick to it.

In an ideal world, you would have considered these issues in the design phase, so that whatever solution you came up with would have been implemented from day one, then none of these problems would have occurred.

This is nothing to do with .NET or VS, it is more to do with catering for nulls in databases.

Thursday, July 22, 2004

"How can VS tell whether some field might be null ?"

In the datatable you see if the variable can have null or not. VS don't need to check the database it just have to look up in the datatable in the dataset. I really think that is trivial.

If the code change later I would spot it if VS could perhaps add optionals warnings at the places that you need to check out.

"VS cannot solve these problems for you."
I actually still think that VS can be improved a lot and we don't need to see those errors. Why do you take it for granted that this problem need to be there and there is nothing to do about it?
If too many people are haveing dbNULL errors then something is wrong with the design of VS.NET - that is at least my opnion.

"In an ideal world, you would have considered these issues in the design phase, so that whatever solution you came up with would have been implemented from day one, then none of these problems would have occurred."

I dobt you can consider those cases in design mode - I have never seen design that consider such low level details.

Thursday, July 22, 2004

You must be kidding DotNetMan. Either you are a Troll or yyou are in serious trouble, IMHO.

Thursday, July 22, 2004


While these errors seem mystifying at first, they will come up, often, for every database-integrated project that ever gets written, period.

Your devs just need to learn that they need:

If Not (MyDataSet("MyColumn")(rowIndex).Value = DBNull.Value) Then
      'do stuff
End If

If they don't check, they *will* get bitten, constantly.

The NullReferenceExceptions are also a very common mistake, one that I'll still make from time to time, though I've learned to spot it quickly enough.  VB.Net variables (and variables in the other .net languages, for that matter) need to be initialized before use explicitly.

That said, if you need to bring somebody on to squash bugs, feel free to contact me.  :)

Greg Hurlman
Thursday, July 22, 2004

Nemesis - if this is your attitude in real life then I just feel sorry for you.

Greg Hurlman - thank you for your comments.

I can understand that in some cases you need to check for dbnull (as in database layer) but I think it strange to have the dbnull checks everywhere in the code in the gui and in the business layer. If you see cases which are difficult to handle you would like to take them to seperate place and take care of them there so you don't need to take care of them everywhere in the code. I think that should be possible in this case too but I don't know how.
I think you might misunderstand something so I will show one example of dbnull errror:

Private sub Test(counter as integer, dr as MyDataset.MyRow)
Dim Ids As New ArrayList

  if counter > 0 then
  end if

end sub

if dr.Myid is DBNull I will get the errror:
"Cannot get value because it is DBNull"

In the datatable MyId is declared like this:
<xs:element name="MydId" type="xs:int" minOccurs="0" />

In this case VS could see that this variable can be null and warn me so I will spot that I need to add " and if dr.IsMyIdNULL" to the if sentence.
Just such warnings would help a lot if they would be turned on.

I know that  NullReferenceExceptions is very common mistake but what I was asking is why VS.NET cannot spot it and add warning at compile time.

Thursday, July 22, 2004

DotNetMan - "Nemesis - if this is your attitude in real life then I just feel sorry for you.".

My attitude is that I tried to help you by offering several ways around the issue and even some guidance on how to improve your situation.

The fact is that VS does not do what you wish it to. Whether you accept that fact or not will not improve the quality of code your team are creating. You need standards and you need to stick to them.

Thanks to Greg for positive contribution.

Thursday, July 22, 2004

My guess is that the VS team decided that this feature wasn't enough in demand for the time they had, though it sounds like it would solve a lot of headaches for VB folks, especially those upgrading.

Head to, and create a suggestion "bug", so that it has a chance of possibly showing up in VS 2005.  Reply back here with the tracking number, so I can add a "me too"-type comment on it.

Greg Hurlman
Thursday, July 22, 2004

Nemesis: When I try to help people I don't tell them that they are Troll or in serious trouble - never - if they are thinking outside the box that is just great.

Greg Hurlman:
Which one (NullReferenceExceptions  or dbnull idea) should I post to the productfeedback website?

Thursday, July 22, 2004

Dear Mr Gates,

Please make Visual Studio write my code for me, as I keep making too many mistakes.

If you could also get it to correct any other errors I might ever make in my life, that'd be good too. Oh, and if you could get it to hold my hand and wipe my ass, as well.

I'm suprised nobody every told you about these problems with your software before.

Thanks anyway,


Thursday, July 22, 2004

Wow, I guess I'm a better developer than I thought!

Nemesis is right, he;'s kinda snarky, but he's right.

THe way I deal with this is to have a very simple object model for all my database-bound applications.  people use DAL and DAO patterns but you don't even need to go nuts with all that.

Basically I do it this way:
- base class models some thing, data, widget, process
- database class extends the base class, handles all the sprocs and data manipulations so as to set the class fields with native datatypes.
- UI classes get and set to the class

You can also just use database types for everything, but that would be bad.

This is not something VS.NET should do for you, this is something you need to understand and deal with.

Thursday, July 22, 2004

At first you may be thankful for the helpful message "This variable could be null" but after a short while it simply becomes a nuisance

Thursday, July 22, 2004

Yeah, sorry guys, didn't mean to be "snarky" :-(

Thursday, July 22, 2004

First, my contribution.  Object systems are built around a model based on partial functions.  As such, any reference to an object can be null.  So, unless you have constraints on your designs that prevent a certain reference from ever taking on the null value, you need to expect that the reference could assume the null value and code accordingly.  Your designs are the only thing that can be used to determine whether a given value will ever have null or not.  If you are seeing nulls in unexpected places, there is a problem with your code/design and there's no way the language can take responsibility for that.

Now a question:  Anyone have a theory why there needs to be a separate DBNull value?  Why can't these things just return the standard null/Nothing value in the case of a DBNull??

also snarky
Thursday, July 22, 2004

The difficulty is really due to the fact that value types, like int, datetime, long, etc, can't be null, as strings and classes can be.

One way to get around this is to re-write the value types as classes, rather than structs. Then you can have nulls for them.

I wouldn't really recommend this course of action (although it is trivial to do), due to the greater overhead of dealing with classes on the heap, rather than value types on the stack.

The O/P's problem (as I tried to explain) is that relational data can accommodate nulls, so the tools for dealing with them, such as VS, don't try to hide this fact, rather they expose it to you, so that you can use your judgement to cater for all eventualities.

There is no point in pretending that this is an issue with VS, it just isn't. VS is behaving exactly as it should.

Thursday, July 22, 2004


VisualStudio *cannot* detect whether or not a field could be null.  Consider this

public void doSomeStuff(MyClass a, MyClass2 b) {
  // can a or b be null?

Irrespective of what your database definitions say,  you can't stop a programmer doing

MyClass myA;
MyClass myB = new MyClass();

//... muck Around

doSomeStuff(myA, myB);

There is *no* way around this, lint style tools will warn you some of the time.  But if your developers are too incompetant to figure out what's going on, no tool will save them.

Proud to be Snarky
Thursday, July 22, 2004

There is an old and wise saying: Bad workmen blame their tools.

I didn't want to be so blatant (snarky?) when I tried to help the O/P above, but his attitude leaves a lot to be desired.

Does anyone else find the O/P's nickname ironic ?

Friday, July 23, 2004

Here's a simple suggestion if you don't want to implement a complicated object wrapper for the database connection:

Make a simple function like this:

Private Function unNull(ByVal objField As Object) As String
    If IsDBNull(objField) Then
        Return Nothing
        Return strField.ToString
    End If
End Function

Then whenever you need a value from a dataset or datareader, do something like:

strFirstName = unNull(myRow.Item("FirstName"))

If the FirstName field is really Null, then strFirstName will be nothing, which is equivalent to "" when evaluating string expressions.

Interestingly, this works fine for integer values, too. I don't quite know why :)

intAge = unNull(myRow.Item("Age"))

Brad Corbin
Monday, July 26, 2004


Return strField.ToString

should be

Return objField.ToString

Brad Corbin
Monday, July 26, 2004

There is a significant difference between null and DBNull.  If an object (or boxed valuetype) is null, it has not been initialised.  An object with a value of DBNull HAS been initialised an set accordingly.

The technique we use is to create a struct (c#) / Structure (VB) that wraps the datatype you are using.

We have overloaded the operators, constructors and defined implicit operators so that DBNull sets an internal state, and can be used in place of the corresponding type. It is correctly interpreted when executing DB stored procs

We only needed to create int(eger) and bool(ean) versions of these, but it can easily be extended to the other value types.

Robert slaney
Tuesday, July 27, 2004

Does VB.Net have an On Error Resume Next? If so, this would remove any errors having to do with Null.

Ok, seriously, in VB 6, you could do a On Error Resume next, and the after the first access of the record, check to see if you got an error.

Miles Archer
Friday, August 27, 2004

*  Recent Topics

*  Fog Creek Home