Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Checking a DateTime field for NULL?

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
BlakeMcKenna
Asked:
BlakeMcKenna
  • 7
  • 4
  • 3
  • +1
5 Solutions
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
it_saigeDeveloperCommented:
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
 
louisfrCommented:
You could also use the Field extension method:
clsHE.calibrationDate = row.Field(Of String)("calibrationDate")

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BlakeMcKennaAuthor Commented:
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
 
BlakeMcKennaAuthor Commented:
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
 
louisfrCommented:
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
 
BlakeMcKennaAuthor Commented:
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
 
louisfrCommented:
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
 
BlakeMcKennaAuthor Commented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
it_saigeDeveloperCommented:
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
 
louisfrCommented:
If the field in the database is a date, use
clsHE.calibrationDate = row.Field<Date>("calibrationDate")

Open in new window

0
 
BlakeMcKennaAuthor Commented:
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
 
BlakeMcKennaAuthor Commented:
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
 
it_saigeDeveloperCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
BlakeMcKennaAuthor Commented:
I'm kinda acting as a DBA without formal DBA training so that helps a great deal!

Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now