what should I assign to my date variable, if the value is null?

[vinidel]

I have a variable of type DATE.
Oracle database does allow nulls for date field. How should I handle this in VB.NET? When I read data from database, what should I assign to my date variable, if the value is null?

 

[onpnt]

not sure I follow compeletely...

you can assign anything you want. If the Date returns Null then just condition for that instance and output what you think the user will be comfortable seeing in the particular task

If you're doing Date calculations and the Null blows it...then again just simply skip the calculation with the validation of the Null in the aprticular row you are at on read.

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]

 

[vinidel]

OK let me put it this way,

I have a DataContainerClass, that has an instance variable of type Date.
Now when I read data from Oracle database and I assign to my DataContainerClass instance variables, my DATE instance variable does not accept a NULL value.
Now what should I do, when the database has a NULL for date?

Thanks,

 

[nomi2000]

Hi VInidel
Dim MySqlDate As System.Data.SqlTypes.SqlDateTime
then you can use
MySqlDate = SqlTypes.SqlDateTime.Null

Regards

Nouman Zaheer
Software Engineer
MSR

 

[chiph]

If you're talking about the VB.NET datatype of DateTime, then there's no way to assign a NULL to it, as that is a database concept. What you can do is assign DateTime.MinValue to it, and then test for that value in your code.

No simple answer, I'm afraid. DateTimes are a challenge.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[DigitalGeek]

In you class, validate the Date and ignore... VB.net will store a null by default, as you pull the data back, validate again and treat it accordinbly.

 

[jfrost10]

Hey Vinidel,

Here's an option:

Public Class HandleDateNull

Private m_strDate

Public Property DateValue() as string

Get
DateValue = m_strDate
End Get

Set (value as string)
Try
value = CDate(value)
m_strDate = Value.ToString
Catch ex
Throw new exception("Invalid Date String")
End Try
End Set

End Property

Public Sub Save()
'code to create the data access object, connection,
'or whatever you use to connect to a database

'When you save the value via stored proc or however,
'you can now check to see if its an empty string
'(pass in a null value), or the valid date value.
End Sub

Just another option anyway.

D

 

[vinidel]

Well thanks for your comments & suggestions.

I found out what I was looking for.
May be I was not very clear with my question or may be I could not make you guys understand.

Anyway, just for those who may need it in future.

If you have a date field in your table which is NULL, and when you try to assign its value to an instance variable of a class, which is of type date, VB.NET code will fail.

So you can check for the dbnull and assign #01/01/0001# as shown below.
Use IIF function and when you assign #01/01/0001# it automatically converts it to #12:00:00 AM# and that is the MIN value of Date type in VB.NET

IIf(IsDBNull(myRows(i).Item("DUE_DATE")), #12:00:00 AM#, myRows(i).Item("DUE_DATE"))

Now on your form you can check if the date variable is equal to #01/01/0001# then you can assign a null "" to text property of the text box.

Thanks

 

[chiph]

01/01/0001 is DateTime.MinValue. I would recommend using it instead of the hard-coded constant, as it's part of the framework.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[jfrost10]

Hey Chip, what's the advantage of using MinValue over Nothing when assigning a variable?

(I like your idea...just trying to justify it with co-workers)

D

 

[chiph]

If you assign Nothing to it, whenever you go to inspect the value you'll get an exception (which you should only get when something is truly exceptional - like your network connection got cut or something). By using a (semi) reserved value your program code is more easily able to deal with it.

Example of using Nothing (syntax may be wrong -- I'm a C# guy not a VB.NET guy)

Dim SomeDate As DateTime
SomeDate = Nothing

' Using it later, if you remember to check for Nothing
If (SomeDate Is Nothing) Then
Else
   Console.WriteLine(SomeDate.ToString())
End If

' Using it later, if you don't remember to check for Nothing
Console.WriteLine(SomeDate.ToString())
' Exception is raised

' Using it later, wrapped in a try..catch block
Try
   Console.WriteLine(SomeDate.ToString())
Catch (NullReferenceException ex)
   ' Do something useful with exception
End Try

As you can see, wrapping it in a try..catch block can bulk up your code, plus there's the performance & conceptual problems of letting exceptions happen (exceptions are expensive). Putting these everywhere will slow things down.

OTOH, the downside to using MinValue is that a developer may forget to check for it, and you'll get subtle logic errors. For example, events may complete before they begin, Products are delivered before they are ordered, etc. One would hope that these would be caught by your NUnit tests.

There's no "right" answer to this -- just one that your experience tells you that most people are likely to have the fewest problems with.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[jfrost10]

Unit tests...what are these things you speak of?


Thanks for your reply, it does clear things up and actually you've presented the same pros/cons for both options that my co-workers had proposed.

We'll probably end up going with the Nothing method (we already have a method for checking 'Nothing' values for values coming back from the DB...and like you said: either you check for Nothing and risk exceptions, or you check for MinValue and risk wanky data showing up).

Thanks again,

D