[RESOLVED] What approach for passing null value to date field?

[adshocker]

hi all,

i'm not 100% sure about sql server but i think it doesn't accept null value for date type columns but in oracle, a date column can accept a null value.

and since i'm using oracle, this is causing some problems because in my form, a textbox item bound to a date column will not accept a null value.

reading thru some threads, it would seem that it is not possible to pass a null value to a date type in .Net.

but i came across a thread (i can't seem to put the URL here).

i did try to use the approach where

Dim _NullData As Nullable(Of DateTime) = Nothing

but when saving the value to the database, i see 01/01/01 on the column in it. still not saving null value to my database but it seemed to save a default value.

i'd appreciate any help.

thanks

[jmcilhinney]

Hehe. I just answered this at another forum about 30 seconds ago. I'm not stalking you, I swear. For others' sake, here's my answer:

Null in VB terms and null in database terms are different things. The fact that your column contains date/time values is completely irrelevant. If you want to assign null to ANY column of ANY data type in ANY database using ADO.NET then you do it in exactly the same way: you use an instance of the DBNull class, which you get from the Shared Value property, e.g.

'Set a field to null in a DataRow.
myDataRow("AnyColumnAtAll") = DBNull.Value

'Set a parameter to null in an OracleCommand.
myCommand.Parameters.AddWithValue("pAnyParameterAtAll", DBNull.Value)

The same goes for testing data that you retrieve from the database. You can either test whether a field value is equal to DBNull.Value or whether its type is DBNull:

If myDataRow("SomeColumn") Is DBNull.Value Then

If TypeOf myDataRow("SomeColumn") Is DBNull Then

The DataRow class and all DataReader classes also have methods to test a field for null. One's named IsNull and the other is named IsDBNull. I can never remember which is which but they do exactly the same thing so it doesn't really matter.

[adshocker]

lolz... i read your answer on the other thread first.

always a pleasure.