How to save a null datetime to SqlServer DB? not working

Question

VS-Studio 2012 Web Express, ASP.NET, WebForms , VB , SqlServer , WebSite application having trouble saving a NULL value for DateTime to the strongly typed ROW:

      Dim oRowVEHICLES As Main_TblAdap.tVEHICLESRow = odtVEHICLES.Rows(0)  ' (0) is the first row.
      oRowVEHICLES.[WElectrical] = If(WElectrical.Year < 10, DBNull.Value, WElectrical)
...etc...

Currently the DetailsView template field textbox is < blank> or empty or "" and the BLL function shows it as a date like: #01/01/0001#. So I test the YEAR value of the passed in variable if less than 10 then save DBNull.Value to the oRowVehicles.[WElectrical] but fails since datatype=Date and cannot convert DBNull to Date.

The DB-field is type Date and allows nulls.

The TableAdapter.xsd view shows the default value is < DBNULL>.

So, why is the oRowVehicles not Date nullable?

How do I make the WElectrical column nullable DATE?

I must be overlooking something, because I cannot be the only one to save an optional DATE value to the Sql-DB.

Your comments and solutions are welcome. Thanks...John

EDIT ASPX code one DATE field in the DetailsView (others are similar):

              <asp:TemplateField HeaderText="Electrical End Date" SortExpression="WElectrical">
                 <EditItemTemplate>
                    <TGADate:GADate ID="ucdtWElectrical" runat="server" Enabled="True" MinDate="01/01/1980" MaxDate="12/31/2050"
                       Caption="Electrical End Date" HideCaption="True" Width="100"
                       IsRequired="false"
                       UpdateMode="Conditional"
                       Text='<%# Bind("WElectrical")%>' />
                 </EditItemTemplate>
                 <InsertItemTemplate>
                    <TGADate:GADate ID="ucdtWElectrical2" runat="server" Enabled="True" MinDate="01/01/1980" MaxDate="12/31/2050"
                       Caption="Electrical End Date" HideCaption="True" Width="100"
                       IsRequired="false"
                       UpdateMode="Conditional"
                       Text='<%# Bind("WElectrical")%>' />
                 </InsertItemTemplate>
                 <ItemTemplate>
                    <asp:Label ID="lblWElectrical" runat="server" Text='<%# clsGA_Lib1.fnGetDateTextFromObject(Eval("WElectrical"))%>' Style="font-weight: bold;"></asp:Label>
                 </ItemTemplate>
                 <ItemStyle Font-Bold="true" />
              </asp:TemplateField>

Object DataSource parameter definition in the ASPX.

 <asp:Parameter Name="WElectrical" Type="DateTime" />

BLL Code:

   <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, False)> _
Public Function UpdateFromDetailsView(ByVal original_UID_VEHICLE As Int32, _
                                     ByVal VehicleNbr As String, _
...other function parameter variables...      
                                     ByVal WElectrical As Date, _
...other function parameter variables...      
                                   ) As Boolean

  ' Get the new VEHICLE-row instance to be updated.
  Dim odtVEHICLES As Main_TblAdap.tVEHICLESDataTable = Adapter.GetVhclByVhclID(original_UID_VEHICLE)

  If odtVEHICLES.Count <> 1 Then
     ' no matching record found, return false
     Return False
  End If

  ' Populate the values of the ROW.
  Dim oRowVEHICLES As Main_TblAdap.tVEHICLESRow = odtVEHICLES.Rows(0)  ' (0) is the first row.
  With oRowVEHICLES
     ...setting row-field values...
     .[WElectrical] = If(WElectrical.Year < 10, Nothing, WElectrical)
     ...etc...
  End With

  ' Update the oRowVEHICLES.
  Dim rowsAffected As Integer = Adapter.Update(odtVEHICLES)

  ' Return TRUE if precisely one row was INSERTED, otherwise false.
  Return CBool(rowsAffected = 1)
End Function

Edit comment for above code

The WElectrical parameter coming into the BLL-function is a DATE with a value of #01/01/0001#.
The code to place the value into the ROW-object

.[WElectrical] = If(WElectrical.Year < 10, Nothing, WElectrical)

places Nothing as the row-object-field-value.

The Adapter.Update(odtVEHICLES) updates the Sql-DB.

So what is causing the #01/01/0001# value to be placed into the Sql-DB?

Sql-DB column definition

//////// end of Edit ///////////

If you are dealing with .net objects, use `null` or `Nothing`. Use `DBNull` only when you are directly dealing with the database, like sending a parameter to a query or SP. Is `WElectrical` a field of type `DateTime?`? I'm not into VB, so you may need to do what's asked here: http://stackoverflow.com/questions/12595775/how-do-i-set-a-nullable-datetime-to-null-in-vb-net — Andrew, Jul 25 '16 at 18:40
Thank you for this comment. The link is very informative and very important. I will be checking out these changes and let you know. Thx. — John D, Jul 25 '16 at 22:14
It looks like you are using a table adaptor. YOu need to set the parameter to AllowNulls: https://msdn.microsoft.com/en-us/library/ms233762.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1 — Jon P, Jul 26 '16 at 01:27
@JonP -- thanks, but dataset designer is/was configured as you suggest. — John D, Jul 26 '16 at 03:02

score 0 · Answer 1 · answered Jul 25 '16 at 20:01

0

do one thing:

change DBNull.Value to Nothing

Alternatively you can change the datatype in the dataset to System.Object, and go to the properties of that data colm then you can select '(Nothing)' in the dropdown.

set Null value to >> Nothing

answered Jul 25 '16 at 20:01

abcool

95
1
10

Thank you for your answer. I did change DBNull.Value to Nothing and that got me past the error of not being able to convert to Date-type. However the other suggestion did not remove the problem of getting a BIND value of #01/01/0001#, that actually may be in my user control for Date-entry on the screen. The real problem now is why is #01/01/0001# being saved to the Sql-DB and not NULL. I will get back to you on this. Thx. – John D Jul 25 '16 at 22:17
Post your code so we can find where the issue is. You may have to check that the value is greater than `DateTime.MinDate` before binding. – Andrew Jul 25 '16 at 23:53
Added code snippets -- your comments are welcome...thanks. – John D Jul 26 '16 at 00:57
sounds like your DB column does not accept nullable Date, therefore it is initialized as min value of Date – kurakura88 Jul 26 '16 at 01:01
@kurakura88 - please see edited code above showing an image of the Sql-column definition -- NULLs are allowed. Thanks for your comment. – John D Jul 26 '16 at 01:10

John D · Accepted Answer · 2016-07-26T02:54:58.863

Thanks to all commentators to the above question. The solution is to change the Row-column-variable to this sentence which casts the Nothing to Date? (nullable) as follows...

 .[WElectrical] = If(WElectrical.Year < 10, CType(Nothing, Date?), WElectrical)

AND -- Changed the dataset-column-definition (in the .xsd) as follows:

DataType ==> System.Object (not Date)

NullValue ==> (Nothing) (not Throw Exception)

My sincere thanks to all contributors -- since elements of each of their suggestions have contributed to this solution.
This is the solution for sending a nullable value into the DataRow-column.

Thank you for all your help.

Thanks @Andrew -- how do I do that? I marked it as green check-mark. Is that ok? — John D, Aug 19 '16 at 15:08

How to save a null datetime to SqlServer DB? not working

2 Answers2