Handling Null Dates

[kmcnet]

Hello Everyone and thanks for your help in advance. I am developing an application that utilizes a separate class library for data access to a SQL Server database. One of the tables has several fields defined as DateTime. The data access layer exposes properties for each field in the table. In the instance of datetime fields, the class returns a property of date type. The problem occurs when one of the datetime fields is NULL, which results in a value of 12:00:00 AM being returned. I then tried the following code

If Not IsDBNull(myclass.mydate) the
myTextbox.Text = myClass.myDat
Els
mytextbox.Text = "
End I

However, it still returned the 12:00:00 AM. Is there any way around this problem other than returning a string value, that converting it is datetime functions are required? Any help would be greatly appreciated. Thanks.

[Dan Brussee]

It sounds like your class is reading the value of the datetime field
and making it "12:00:00 AM". From then on, the value is not null, but
that time.

Option A: In your class, when reading the value of the datetime field
from the database, set the value to Null instead of midnight.

Option B: When reading the value of myClass.myDate, instead of
checking for DBNull, check for "12:00:00 AM". Of course, if a real
time of midnight were ever there, this would fail. I assume though
that your actual datetime would include dates, so this should work.
Just a bit kludgy.

On Sat, 28 Feb 2004 09:26:05 -0800, kmcnet
<an*******@discussions.microsoft.com> wrote:

Hello Everyone and thanks for your help in advance. I am developing an application that utilizes a separate class library for data access to a SQL Server database. One of the tables has several fields defined as DateTime. The data access layer exposes properties for each field in the table. In the instance of datetime fields, the class returns a property of date type. The problem occurs when one of the datetime fields is NULL, which results in a value of 12:00:00 AM being returned. I then tried the following code:

If Not IsDBNull(myclass.mydate) then
myTextbox.Text = myClass.myDate
Else
mytextbox.Text = ""
End If

However, it still returned the 12:00:00 AM. Is there any way around this problem other than returning a string value, that converting it is datetime functions are required? Any help would be greatly appreciated. Thanks.

[kmcnet]

Thanks Dan. That does work. However, I am not sure why it is being set to 12:00:00 am in the first place. The code I am using is as follows

To initialize the variabl

Private _HireDate As Dat

Then on the read from the database

If Not IsDBNull(myReader("HireDate")) The
_HireDate = Trim(myReader("HireDate")
End I

Finally, the property is set

'HireDat
ReadOnly Property HireDate() As Dat
Ge
Return _HireDat
End Ge
End Propert

So, I really can't figure out where the 12:00:00 am come from. Any insight?

[Paul Clement]

On Sat, 28 Feb 2004 10:21:05 -0800, kmcnet <an*******@discussions.microsoft.com> wrote:

¤ Thanks Dan. That does work. However, I am not sure why it is being set to 12:00:00 am in the first place. The code I am using is as follows:
¤
¤ To initialize the variable
¤
¤ Private _HireDate As Date
¤
¤ Then on the read from the database:
¤
¤ If Not IsDBNull(myReader("HireDate")) Then
¤ _HireDate = Trim(myReader("HireDate"))
¤ End If
¤
¤ Finally, the property is set:
¤
¤ 'HireDate
¤ ReadOnly Property HireDate() As Date
¤ Get
¤ Return _HireDate
¤ End Get
¤ End Property
¤
¤ So, I really can't figure out where the 12:00:00 am come from. Any insight?

Because uninitialized dates have a default value of 1/1/0001 12:00:00 AM.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)