Solved

Checking a DateTime field for NULL?

Posted on 2014-10-11
17
221 Views
Last Modified: 2014-10-13
I am retrieving a column from a SQL Server Database Table from my VB.Net Windows App. I have tried checking for "IsDBNull" and "Is Nothing" and it throws the exception:  "Conversion from type 'DBNull' to type 'String' is not valid."

I have tried fourdifferent statements and still getting the error. Here they are...

clsHE.calibrationDate = If((row("calibrationDate") Is Nothing), DBNull.Value, row("calibrationDate"))
clsHE.calibrationDate = If(IsNothing(row("calibrationDate")), DBNull.Value, row("calibrationDate"))
clsHE.calibrationDate = If(IsDBNull(row("calibrationDate")), DBNull.Value, row("calibrationDate"))
clsHE.calibrationDate = If(DBNull.Value.ToString.Equals(row("calibrationDate")), DBNull.Value, row("calibrationDate"))

Open in new window

0
Comment
Question by:BlakeMcKenna
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 40
ID: 40375282
You are not checking for DBNull, you are assigning it in your If.

Assuming that row is a DataRow object and clsHE.alibrationDate is a String:

If IsDBNull(row("calibrationDate")) Then
	clsHE.calibrationDate = String.Empty 'Or whatever you want to assign if the value is Null
Else
	clsHE.calibrationDate = row("calibrationDate")
End If

Open in new window

0
 
LVL 32

Assisted Solution

by:it_saige
it_saige earned 200 total points
ID: 40375304
You could also use a TryParse:
If DateTime.TryParse(DirectCast(row("calibrationDate"), String), clsHE.calibrationDate) Then
	''' Your value has successfully been parsed as a datetime value.
Else
	''' Your value was a null value or not a datetime value.
End If

Open in new window

More on DateTime.TryParse:

http://msdn.microsoft.com/en-us/library/ch92fbc1(v=vs.90).aspx

-saige-
0
 
LVL 11

Assisted Solution

by:louisfr
louisfr earned 200 total points
ID: 40376510
You could also use the Field extension method:
clsHE.calibrationDate = row.Field(Of String)("calibrationDate")

Open in new window

0
 

Author Comment

by:BlakeMcKenna
ID: 40376934
James,

Intially, I had the clsHE.calibrationDate defined as a "String" when I got that original exception. I then changed the datatype to a "Datetime". Then I got the exception:  "Conversion from type 'DBNull' to type 'Date' is not valid."

I thought that the statement construct I had was either assigning DBNull.Value or row("calibrationDate") to the clsHE.calibrationDate variable?
0
 

Author Comment

by:BlakeMcKenna
ID: 40376956
James,

My last post was just a comment, however, I tried your code suggestion and got the same error. But, I believe your code suggestion was based on "clsHE.calibrationDate" being of "String" datatype. I have since changed that variable to be a "DateTime" datatype.

Is it best to leave it as a "String" and then convert it before saving to the DB?
0
 
LVL 11

Expert Comment

by:louisfr
ID: 40376975
DBNull.Value is of type DBNull. You cannot assign a DBNull.Value to anything except a DBNull or Object variable.
You can assign a null value (Nothing in VB) to a String or Date variable.
Use one of the following:
If IsDBNull(row("calibrationDate")) Then
	clsHE.calibrationDate = Nothing
Else
	clsHE.calibrationDate = row("calibrationDate")
End If

Open in new window

If row.IsNull("calibrationDate")) Then
	clsHE.calibrationDate = Nothing
Else
	clsHE.calibrationDate = row("calibrationDate")
End If

Open in new window

clsHE.calibrationDate = row.Field(Of Date)("calibrationDate")

Open in new window

0
 

Author Comment

by:BlakeMcKenna
ID: 40377054
louisfr,

Thank you for that clarification. Now, just so I don't make this mistake again, how should values being retrieved from a Database be checked, i.e. "Integer", "Varchar", "Decimal", "Bit"?

I just want to incorporate the accepted method(s) for edit checking of NULL values. When I look a row of data in a SQL Server DB and see NULL for a column's value, I'm inclined to think that I should check for NULL in my VB app?

Thank you for your help!
0
 
LVL 11

Expert Comment

by:louisfr
ID: 40377157
To check if the database value is NULL, you can call:
- IsDbNull(row("xxx")) or row.IsNull("xxx") which returns true if xxx is null ; the syntax for those two doesn't depend on the type of data
- row.Field<T>("xxx") which returns the value of xxx or Nothing if xxx is null ; here T is the VB type of the data
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:BlakeMcKenna
ID: 40377172
What is the correct syntax for this statement?

"row.Field<T>("xxx") which returns the value of xxx or Nothing if xxx is null ; here T is the VB type of the data"

Can you show me an example?
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 100 total points
ID: 40377284
The syntax is always the same, but the value that you assign to your variable when the value in the database is Null should be of the correct type.

Because your original was a String, I went for String.Empty, which is the most usual way of handling nulls for text fields.

If the field is a numeric value, you might decide to set the value to 0 instead. Many programmers will use -1 for values that are never negative. When treating values, they consider -1 as if it was Null. Some other will use a value such as Integer.MaxValue or Integer.MinValue. For a Date field they might use a date far in the future or the past.
Then the code would become something like the following:

If IsDBNull(row("calibrationDate")) Then
      clsHE.calibrationDate = #1-1-1#
Else
      clsHE.calibrationDate = row("calibrationDate")
End If

They will then program their TextBoxes or grid cells so that they display nothing when the variable is set to that value.

If clsHE.CalibrationDate=#1-1-1# then
      TextBox1.Text=String.Empty
Else
      TextBox1.Text= CStr(row("calibrationDate"))
End If

Other programmers will use a Nullable datatype.

Personnally, since I am almost always in control of the design of the database, I try to control that at the database level, by not permitting Null value in a field as much as possible.

You have also to think of what you will do when you update your database. You must make sure, if the value is still you reference value #1-1-1# in my example, that you post back a Null to the database, not #1-1-1#.
0
 
LVL 32

Assisted Solution

by:it_saige
it_saige earned 200 total points
ID: 40377288
Quite simply:
Dim someValue As String = row.Field(Of String) ("calibrationDate")
If String.IsNullOrEmpty(someValue) Then
	' Value is null or empty, nothing to see here.
Else
	' Value has something, lets try to parse it to a date time value.
	If DateTime.TryParse(someValue, clsHE.calibrationDate) Then
		' someValue has successfully been parsed as a datetime value.
	Else
		' someValue was not a datetime value.
	End If
End If

Open in new window


You could also choose not to use a temporary variable:
If DateTime.TryParse(row.Field(Of String) ("calibrationDate"), clsHE.calibrationDate) Then
	' Field has successfully been parsed as a datetime value.
Else
	' Field was a null value or not a datetime value.
End If

Open in new window


-saige-
0
 
LVL 11

Assisted Solution

by:louisfr
louisfr earned 200 total points
ID: 40377361
If the field in the database is a date, use
clsHE.calibrationDate = row.Field<Date>("calibrationDate")

Open in new window

0
 

Author Closing Comment

by:BlakeMcKenna
ID: 40377382
Thanks guys...all those responses helped alot. I'm pretty much self-taught when it comes to OOP. Obviously, it's critical to get a good fundamental understanding of DataTypes and how to handle them.

Thanks again!
0
 

Author Comment

by:BlakeMcKenna
ID: 40377416
James,

You sparked an idea with me when you said you try to control values returned from your SQL scripts.

I use the ISNULL function sometimes. I usually use it in the context of this:

ISNULL(equipment_ID, 0) AS 'Equipment ID'

Where "equipment_ID" is defined as an "Integer" value.

Using the same construct, what should I return if the datatype was a VARCHAR

ISNULL(equipment_Name, ?) AS 'Equipment Name'

Thanks!
0
 
LVL 32

Expert Comment

by:it_saige
ID: 40377508
VarChar is usually represented as a string or a character array.

More on common data type mappings:

http://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

-saige-
0
 
LVL 40
ID: 40377556
ISNULL(equipment_Name, '') AS 'Equipment Name'

But by "control at the database level", I did not meant the SQL, although I also use ISNULL sometimes. I meant that I design the database so that it does not accepts Null (you can do that when creating a field), and would set the default value of the field to '' for a text field (or other value for another type of field). I then consider '' as my null value. Instead of checking for DBNull, I might check for String.Empty when needed. But in many cases, I simply do not have to care about it at all. Dealing with default values is easier than dealing with Null. You cannot always to that, because sometimes you need to make the difference between '' and Null, but in most cases, '' can be used in place of a Null.

Note that you will meet a lot of programmers who do not agree with that approach. I was of them when I first learned that many university teachers now go for that approach. But after working that way, I find that this is the easiest way to go.

And having "a good fundamental understanding of DataTypes and how to handle them" is not a OOP concept. It's a basic concept of database design and programming in general. More advanced languages such as C impose that on you. Languages that were designed to be simpler to use, such as VB, do not. It makes your life easier at first sight, but leads to a lot of problems and bugs. And it hides the fact that dealing with types is very important if you want solid code.

In VB, in order to force what is called strong typing, you need to set Option Strict to On in the project's properties. This forces you to make decisions about typing instead of letting the system do it for you. It's a little more work when you code, but most programmers would rather code than debug.
0
 

Author Comment

by:BlakeMcKenna
ID: 40377866
I'm kinda acting as a DBA without formal DBA training so that helps a great deal!

Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

18 Experts available now in Live!

Get 1:1 Help Now