Solved

How do you properly check for NULL values?

Posted on 2014-04-30
11
359 Views
Last Modified: 2014-05-01
I've noticed that when I get into SSMS (SQL Server Management Studio) to view data via opening up a table directly or writing a Select Query, columns that have "NULL" in them arre obviously NULL whether the column is a VARCHAR or Numeric. However, when I try to retrieve data from the same table using VB.Net built query, the columns that contain NULL values appear as Strings or "".

What I'm trying to do is write a function that checks for NULL values regardless of what datatype the passed column may be. This is what I have so far and I don't think it covers everything.

    Public Function CheckForNULLs(ByVal fld As Object, Optional ByVal strType As String = "") As Object
        Try
            EH.ErrorMessage = ""

            If DBNull.Value.Equals(fld) Then
                If strType = "N" Then
                    Return 0
                Else
                    Return Nothing
                End If
            Else
                Return fld
            End If

        Catch ex As Exception
            EH.ErrorMessage = "modMain/CheckForNULLs() - " & ex.Message & "...Contact Engineering!" & "~E"
        End Try
    End Function

Open in new window

0
Comment
Question by:BlakeMcKenna
  • 6
  • 5
11 Comments
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Because fld is defined as a generic object, I cannot give you the right code, not knowing what it is there. But supposing that this is a type of object that has a Value property, the proper syntax would be:

DBNull.Value.Equals(fld.Value)

Try not to use Object variables and parameters when you know the type of data you will be working with. Specify the exact type of the object. This makes code a lot more readable, and it helps you in you work because you can use IntelliSense to get the list of properties and methods available in a variable.
0
 

Author Comment

by:BlakeMcKenna
Comment Utility
Well the data type will either string or numeric. I'm using object because when I had originally defined the signature using a "String"...whenever a call was made to the function and the value was NULL, it blew up without even executing the function giving me the error:

"Conversion from type 'DBNull' to type 'String' is not valid"
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
String and numeric variables cannot be DBNull.value. A numeric variable always have a numeric value (0 if none was given), and a String can be Nothing, but these are not the same thing as a DBNull.Value.

DBNull is a DataBaseNull. It applies only to fields that do not have a value. You should pass the field to your method.

If your working with a DataTable, the call might look something like the following:

CheckForNULLs(yourTable.Rows(2).Item("FieldName"),"")
0
 

Author Comment

by:BlakeMcKenna
Comment Utility
James,

Is the method written correctly?
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
Comment Utility
Depends on what you exactly mean by "correctly".

For many programmers, it is correct it if does what it needs to do.

But as a professional, I would not accept it.

Working with Object variables and return values when you could have specific types is not "correct" on a professional standpoint. This is more costly in resources and can lead to bugs that are hard to pinpoint. Object variables have their use, but it is always better to work with specific types when you can.

And passing the type through a variable can lead to errors. What if you pass a "N" along with a String value by mistake. BOOM! It would be better to detect the type.

Always thinking that you are working from a DataTable object and that you call your method with something like CheckForNULLs(yourTable.Rows(2).Item("Quantity")), here is a simplified version of what I would do (note that I do not have time to test it, and after testing, I might change a thing or two):
	Public Function CheckForNULLs(ByVal fld As Object) As Object

		If Not IsDBNull(fld) Then

			Select Case True
				Case IsNumeric(fld)
					Return 0
				Case TypeOf fld Is String
					Return String.Empty
				Case TypeOf fld Is Date
					Return New Date()
				Case TypeOf fld Is Boolean
					Return False
                                'Add any other type you might use in your database
				Case Else
					'Whatever you do with EH
					EH.ErrorMessage = "The type " & fld.GetType.Name & " is not handled by modMain/CheckForNULLs() - Contact Engineering!" & "~E"
					Application.Exit()
			End Select

		Else
			Return fld
		End If

	End Function

Open in new window

But with that, as well as with your original method, you might have brought a problem that will show up only after some time, causing a lot of headaches in between. The fields that were Null when you read the data have now changed. If you then put the data back in the database, you will overwrite the Nulls that where there by something else, which could have an impact somewhere in the system, where a zero will suddenly appear while the system was expecting a DBNull for empty fields. The zero might be interpreted as something else. If there was a DBNull value there in the start, there was a reason for it, and it thus should be left that way.

So there is something else I would do. Not using Null in the database, so I do not have to care. Nulls are overused. In most cases, defining a dummy default value in a field is a better way to handle fields that have no data.

For the date a photo was taken for instance, I would set 1800-01-01 as the default date for the field, and handle that as a field that has no value. Everywhere you have to check a date field for a DBNull, check for 1800-01-01 instead, and leave it as is if this is what you find. But in your code, see that as a date that has no meaning.

Some programmers do not like that way of working. They have been working with Null fields for too long and do not know how to do otherwise. But let me tell you that it solves a lot of problems, and prevents things like what you are doing, which is putting empty strings and zero values in fields that do not have these values. If doing that is OK for you, then why don't you set empty string and zero as default values for your fields from the start. That is what you will end up with anyway after going through your method.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Closing Comment

by:BlakeMcKenna
Comment Utility
James,

The only time I use this function is when I retrieve data from a database and wish to load the values into controls. It's been my experience that NULL values don't load well into controls. That's the only reason I use this function. Once the value is loaded into the field, then I can do with it as I need. In some cases those values won't even be touched and will just be stored back in the DB as they were when they were retrieved.

I like your code though. That definitely seems to cover the different data type scenarios.
0
 

Author Comment

by:BlakeMcKenna
Comment Utility
James,

I noticed that when the value being passed ISN'T NULL, then it checks for the TYPE. Wouldn't that be irrelevant if the value isn't NULL? On the flipside, if the value IS NULL, it passes back a NULL value!
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Yes, you are right, sorry. That is what happens when one does not run his code to check it.

For the logic, there should not be a "Not" in the If. But then, the method does not work. The code shows you how to work with different types when dealing with an Object variable, but not how to deal with DBNull.

This goes to say that what you want to do is something that every programmer thinks of doing at some time. If if was possible to write a generic method to handle DBNull in a control, there would be one in the framework. If there is none, it's because it's better to treat a DBNull on the spot, everywhere you think you could encounter one.

So if your original routine does the job for you, keep it. But personally, this is not something I would use.

I do not use Null very often in my databases, but when it happens, since I work mainly with a DataReader when working with databases, I do not find that having to do the following from time to time is too much work:
If drd.IsDBNull(3) Then
	txtQuantity.Text = "0"
Else
	txtQuantity.Text = CStr(drd.GetInt32(3))
End If

Open in new window

And I would rather do it that way, because the needs are not the same everywhere. In some fields, maybe -1 is a better value than 0. In another one, you will put an empty string. In another one, a prompt "<Please enter a quantity>" or a message "<Out of Stock>". Null is not the same thing as zero, so most of the time, displaying zero instead of Null is not a good idea. A generic routine is thus not a good idea. This is my experience as a programmer with 40 years of experience, 20 of these as a professional.

And if I really wanted to do it with a 0, I would simply do the following:
Public Class NumericTextBox
	Inherits TextBox

	Private _IsNullValue As Boolean

	Public ReadOnly Property IsNullValue As Boolean
		Get
			Return _IsNullValue
		End Get
	End Property

	Public Property Value As Object
		Get
			Return CDbl(Me.Text)
		End Get
		Set(value As Object)
			If IsDBNull(value) Then
				Me.Text = "0"
				_IsNullValue = True
			Else
				If IsNumeric(value) Then
					Me.Text = CStr(value)
					_IsNullValue = False
				Else
					Throw New ArgumentException("A non-numeric value was passed to a Control that accepts only numeric values.")
				End If
			End If
		End Set
	End Property

End Class

Open in new window

And this time, I have checked my code by running it.

This is the code for a TextBox to which I have added a Value property that takes care of DBNull and accepts only a numeric value. It also has a IsNullValue property that can be used to know if the value really if the real value of the field is 0 or Null when a "0" is displayed, enabling you to deal with DBNull if needed further on.

Using the same pattern, you can create one for String values, another one for Date values.

Whenever you need to display a value from field that accepts a Null, use these TextBoxes instead of the standard TextBox. And use the Value property instead of the Text property to display your field content:

NumericTextBox1.Value = yourTable.Rows(0).Item("Quantity")
0
 

Author Comment

by:BlakeMcKenna
Comment Utility
James,

Your code makes sense. Not to change the subject but I'm a self-taught programmer (even though I have over 25 years of professional experience myself) but using the NumericTextBox class you coded above, how would I implement that into my program.

I created a test app and added this class to the Project. When I added a new textbox control to my form it was just that...a "TextBox" and not a "NumericTextBox".

What do I need to do?

Thanks!
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Once the project is compiled, look at the top of the Toolbox. You should have a new tab with the name of your application, and the NumericTextBox control will show in it. Use that instead of the regular TextBox.
0
 

Author Comment

by:BlakeMcKenna
Comment Utility
Yep...that worked!

Thanks!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now