Question 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.

what approach can i do to go around this?

thanks.

 

[adshocker]

ok.. so i came across this thread http://www.vbdotnetforums.com/vb-net-general-discussion/6011-how-assign-null-value.html

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]

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.

 

[jmcilhinney]

Null in VB terms and null in database terms are different things.

Just getting back to this comment for a moment, a null in VB is Nothing, i.e. no object. In ADO.NET terms IS an object, i.e. an instance of the DBNull class, that represents a database null value.

 

[adshocker]

somehow i knew that a DateTime type cannot be null but i guess i was hoping.

thanks for the info. much appreciated.

 

[JohnH]

DateTime type doesn't include a null (Nothing) value, but the type can be Nullable(Of DateTime), a type which will allow for null (Nothing) value in addition to Date values. This is a client side type/value that have to be converted to DBNull to/from DB (a datetime column that allow null).

 

[cjard]

Oracle allows anything to be null. Code generated by the IDE in tableadapters (sorry, you don't get this because you didnt buy VS ) looks like:

        Public Overloads Overridable Function Update(ByRef IN_OUT_ADDRESS_REF As String, ByVal IN_ADDRESS_TYPE_ID As String, ByVal IN_CONTACT_NAME As String, ByVal IN_ADDRESS_1 As String, ByVal IN_ADDRESS_2 As String, ByVal IN_ADDRESS_3 As String, ByVal IN_CITY As String, ByVal IN_POSTCODE As String, ByVal IN_EFFECTIVE_DATE As Global.System.Nullable(Of Date)) As Integer
            If (IN_OUT_ADDRESS_REF Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(0).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(0).Value = CType(IN_OUT_ADDRESS_REF,String)
            End If
            If (IN_ADDRESS_TYPE_ID Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(1).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(1).Value = CType(IN_ADDRESS_TYPE_ID,String)
            End If
            If (IN_CONTACT_NAME Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(2).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(2).Value = CType(IN_CONTACT_NAME,String)
            End If
            If (IN_ADDRESS_1 Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(3).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(3).Value = CType(IN_ADDRESS_1,String)
            End If
            If (IN_ADDRESS_2 Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(4).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(4).Value = CType(IN_ADDRESS_2,String)
            End If
            If (IN_ADDRESS_3 Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(5).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(5).Value = CType(IN_ADDRESS_3,String)
            End If
            If (IN_CITY Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(6).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(6).Value = CType(IN_CITY,String)
            End If
            If (IN_POSTCODE Is Nothing) Then
                Me.Adapter.UpdateCommand.Parameters(7).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.UpdateCommand.Parameters(7).Value = CType(IN_POSTCODE,String)
            End If
            [B]If (IN_EFFECTIVE_DATE.HasValue = true) Then
                Me.Adapter.UpdateCommand.Parameters(8).Value = CType(IN_EFFECTIVE_DATE.Value,Date)
            Else
                Me.Adapter.UpdateCommand.Parameters(8).Value = Global.System.DBNull.Value
            End If[/B]            
Dim previousConnectionState As Global.System.Data.ConnectionState = Me.Adapter.UpdateCommand.Connection.State
            If ((Me.Adapter.UpdateCommand.Connection.State And Global.System.Data.ConnectionState.Open)  _
                        <> Global.System.Data.ConnectionState.Open) Then
                Me.Adapter.UpdateCommand.Connection.Open
            End If
            Try 
                Dim returnValue As Integer = Me.Adapter.UpdateCommand.ExecuteNonQuery
                If ((Me.Adapter.UpdateCommand.Parameters(0).Value Is Nothing)  _
                            OrElse (Me.Adapter.UpdateCommand.Parameters(0).Value.GetType Is GetType(Global.System.DBNull))) Then
                    IN_OUT_ADDRESS_REF = Nothing
                Else
                    IN_OUT_ADDRESS_REF = CType(Me.Adapter.UpdateCommand.Parameters(0).Value,String)
                End If
                Return returnValue
            Finally
                If (previousConnectionState = Global.System.Data.ConnectionState.Closed) Then
                    Me.Adapter.UpdateCommand.Connection.Close
                End If
            End Try
        End Function

From this we can learn:

If the value is Nothing, set the parameter to DBNull.Value


As I told you ages ago, give me the CREATE TABLE statement for your table and I will generate a whole dataset for you using my VS. I'll then give you the code and you can read it and it'll give you a lot of pointers on how to fake up the same thing yourself.

Actually, you might find yourself using it as a template, and writing an app where you paste your table def in and it will make the code for you.. a sort of mini version fo the dataset designer that we have in full VS

 

[cjard]

DateTime type doesn't include a null (Nothing) value, but the type can be Nullable(Of DateTime), a type which will allow for null (Nothing) value in addition to Date values. This is a client side type/value that have to be converted to DBNull to/from DB (a datetime column that allow null).

Note that this is exactly what the ebold part in the pasted sample shows:
Use of HasValue to determine if the Nullable has a value (or is null)

 

[JohnH]

I know, the reason for this is because DateTime is a structure, a value type, so it is not possible for a variable of this type to hold a null reference (Nothing). The default value for this is the date "01.01.0001 00:00:00".

 

[adshocker]

As I told you ages ago, give me the CREATE TABLE statement for your table and I will generate a whole dataset for you using my VS. I'll then give you the code and you can read it and it'll give you a lot of pointers on how to fake up the same thing yourself.

Actually, you might find yourself using it as a template, and writing an app where you paste your table def in and it will make the code for you.. a sort of mini version fo the dataset designer that we have in full VS

hi,

sorry about that.. completely forgot.

CREATE TABLE KNL_USERS
(
  USER_NUM            NUMBER,
  USERNAME            VARCHAR2(30 BYTE)         NOT NULL,
  ENCRYPTED_PASSWORD  VARCHAR2(100 BYTE)        NOT NULL,
  TITLE               VARCHAR2(10 BYTE),
  LAST_NAME           VARCHAR2(30 BYTE)         NOT NULL,
  FIRST_NAME          VARCHAR2(30 BYTE)         NOT NULL,
  MIDDLE_NAME         VARCHAR2(30 BYTE),
  FULL_NAME           VARCHAR2(90 BYTE)         NOT NULL,
  EMAIL_ADDRESS       VARCHAR2(100 BYTE)        NOT NULL,
  DESCRIPTION         VARCHAR2(250 BYTE),
  CREATED_ON          DATE                      NOT NULL,
  CREATED_BY          NUMBER                    NOT NULL,
  LAST_UPDATED_ON     DATE,
  LAST_UPDATED_BY     NUMBER,
  LAST_LOGIN_ON       DATE,
  LAST_PWD_CHG_ON     DATE,
  PWD_EXPIRES_ON      DATE,
  START_ON            DATE,
  END_ON              DATE
)

also, after reading your sample code above, i tried manually editing the dataset.Designer.vb and added some codes there. but when i saved the whole project, the codes where also removed. is there any way around this?

thanks.

 

[JohnH]

The dataset.Designer.vb is generated based on the configurations you make in Designer for the dataset.

 

[jmcilhinney]

All designer files are generated and regenerated by the designer. You should very, VERY rarely be making changes to a designer code file. Just like for forms, if you want to add your own code then you should add a new code file and add a partial class.

 

[cjard]

also, after reading your sample code above, i tried manually editing the dataset.Designer.vb and added some codes there. but when i saved the whole project, the codes where also removed.

Heed the warning at the top of the file:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50727.1433
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

Don't make code changes to .Designer.vb files

 

[cjard]

PS, i took the liberty of adding a primary key to your table def; without it, the designer wont generate UPDATE or DELETE statements

 

[adshocker]

hi,

just wanted to ask..

i use the Visual Basic 2005 Express Edition.. can i use the approach suggested?

thanks.