Checking a DateTime field for NULL?

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

Select allOpen in new window

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?

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?

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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!

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

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?

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!

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!

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-

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.

I'm kinda acting as a DBA without formal DBA training so that helps a great deal!

Thanks!