We help IT Professionals succeed at work.
Troubleshooting Question

Pass Null Value For DateTime through DataAdapter to SQL Table

30 Views
Last Modified: 2020-11-04
Good morning,

I am struggling to pass a Null value into a DataAdapter for a DateTime field. The values come the other way (from the database into the application), but I cannot send them back. I get a "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM." error back (SqlTypes Exception).

I need to be able to do this as this is used to signify a machine "INACTIVE" state on the dashboards around the factory. The machine reports its own state along with a DateTime value of the last report, so when the software detects that the machine is offline it needs to be able to null the field.

I have tried everything I can think of to be able to pass the null in as a parameter to the query in the adapter, but nothing appears to work. The only thing I have managed to do is an inefficient two-hit system of updating everything else in the record and setting the DateTime to NULL directly in the SQL of the query, and then setting the DateTime to a value if there is one. Two queries, two updates. Nasty, ugly, inefficient.

There must be a way I can do this in one go? What am I missing?

Steve
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
w/o code, it will be difficult to see what might work.

I used to use DBNull.Value

https://docs.microsoft.com/en-us/dotnet/api/system.dbnull
Steve MarshallIT Manager

Author

Commented:
Hi,

There is no code per se. I have a DateTime variable that is passed in a call to a DataAdapter created in Visual Studio. The DataAdapter generates an exception ... and that's it.

The call to the Data Adapter is wrapped in a Try...Catch which is how I know I am getting a SqlTypes Exception, but beyond that there is not much to share code-wise.

I have tried passing Nothing (get the error in OP), I have tried using DBNull but I get an Invalid Cast Exception (which to be fair is what the documentation says it will do).

Steve

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It has been a few years since I messed with .Net and DataAdapters so I'm going from memory.  Also never messed with MVC if that is what you are doing.

That is why I asked for the code.  I wanted to see how you were doing things.

Maybe try "Nothing"?

https://www.vbforums.com/showthread.php?655269-RESOLVED-Instert-Null-Value-in-DataAdapter
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
Hi,

Does your table in the database allow null values? It needs to allow nulls if you want to be able to insert/update datetime to a Null value.
You should use sqldatenull  as shown in the example of the following link (assuming you are using VB.Net as topic suggests)
https://www.c-sharpcorner.com/article/enter-null-values-for-datetime-column-of-sql-server/

Regards,
    Tomas Helgi
Steve MarshallIT Manager

Author

Commented:
No worries. I have tried "Nothing" and that generates the "SqlDateTime overflow" as Nothing equates to "1/1/0001 00:00:00" which falls outside of the allowed date range in SQL Server.

I am starting to think that this may not be possible via a DataAdapter as every "solution" I am finding out there seems to be a work around rather than a true "solution". One is the double-tap update that I have currently used (set the value to NULL in the SQL and then set the single field to a value if you have one) - I just find this unacceptable. Another is to attach a Boolean flag to a Nullable DateTime field and then the DateTime field always holds the last written value and it is the Boolean that is swapped around True = DateTime is valid and False = DateTime is invalid and should be ignored. Again, a solution that will work, but shouldn't be necessary as I should be able to send a null in through the DataAdapter if one can come out through the DataAdapter.

Either I am missing some crucial feature, or this is some crazy restriction that makes no sense (to me at least).

Steve
Steve MarshallIT Manager

Author

Commented:
Hi Tomas,

Yes - the field in the table is Nullable, and I can set it to Null directly in the SQL - just not passing in as a parameter. I will read the article you gave a link to and come back.

Steve

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
You are pushing my .Net memory.

I see some references to AllowDBNull?

https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.allowdbnull?view=netcore-3.1

It would be great if you could somehow give us something more to play with?

Maybe:  https://dotnetfiddle.net/
Steve MarshallIT Manager

Author

Commented:
Hi Tomas,

Well, to get that to work with a DataAdapter I had to pass the parameter as CType(SqlDateTime.Null, Date?) to get the type to match. That no longer throws an exception, which is a step in the right direction, but sets the value to "1900-01-01 00:00:00:000" which is not the same as when I execute  "UPDATE MachineState SET LastReportedIn = NULL WHERE MachineId = 4" which sets the value to NULL. The two are clearly doing something different somehow.

Steve

Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
Hi Steve,

Usually in databases then NULL in DateTime is stored physically as 1900-01-01 00:00:00:000.

Regards,
     Tomas Helgi
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I wouldn't suggest you to store a 'default' value for a null in the database.
Steve MarshallIT Manager

Author

Commented:
Hi slightvw. Sorry for being evasive, but I honestly cannot thing what to show you. The routine that yields the error is a mere 5 or 6 lines long, and most of that is the error handling. As for code that actually does something it is one line - a call to the DataAdapter that takes three parameters, the MachineStateId which is an integer value from an ENum (0 = Inactive, 1 = Setup, 2 = Running, 3 = Down), the ReportedIn which is a DateTime, and the MachineId which is an Integer depending on the Machine that is reporting. Just three parameters. The SQL is a simple update statement:

UPDATE 
    MachineState
SET 
    ReportedIn = @ReportedIn,
    MachineStateId = @MachineStateId
WHERE
    MachineId = @MachineId

The two integer parameters (@MachineStateId and @MachineId) are declared as System.Int32, and the DateTime (@ReportedIn) is declared as Date?

The routine is passed an Object that holds the three values. If the report comes from the machine itself the ReportedIn property will hold a valid Date/Time. If the report comes from the software itself detecting that the machine is not on the network, the ReportedIn will hold Nothing.

So, as you can see - nothing really to show. The issue is with the call to the DataAdapter, not with anything around it which is why I tried not to clutter with code that is working.

Steve

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Steve MarshallIT Manager

Author

Commented:
Hi Tomas,

I have done a bit more testing and herein lies the issue. If we write NULL back to the field then when the software queries it gets a NULL value back which it can detect and display an appropriate machine inactive state. However, when I write back using CType(SqlDateTime.Null, Date?)  then the value in the database is not NULL (even if the database interprets it as such), and does not come back into the software as NULL either - so even though the database may interpret that as its NULL value it does not translate it to NULL when returning it. Our software is currently displaying my test machine as having reported an INACTIVE state on 01/01/1900 because it is getting what it sees as a valid date back.

We are using the NULL in the DateTime to indicate an extraordinary state - machine not on network, so there is a reason for what we do.

Steve
Steve MarshallIT Manager

Author

Commented:
Hi SlightVW,

Regards the SqlDateTime.Null - that is precisely what Tomas and I are discussing back & forth at the moment. It solves the Exception issue, but does not achieve the true NULL value that we interpret as an exceptional state as it writes back "1900-01-01 00:00:00:000" and not NULL.

I suppose we could change the software to interpret "1900-01-01 00:00:00:000"  as NULL, but that means several changes to several systems rather than one change to the write...

Steve
Database Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Steve MarshallIT Manager

Author

Commented:
Hi Tomas,

I will try the above suggestions and come back to you.

Thanks,
Steve
Steve MarshallIT Manager

Author

Commented:
Hi Tomas,

You have done it!! It took a combination of two things that you suggested:

First, use of SqlDateTime.Null when the parameter is passed to the DataAdapter:
If((.ReportedIn = Nothing), CType(SqlDateTime.Null, Date?), .ReportedIn)
Then, a variation of NULLIF() within the SQL of the Query within the DataAdapter:
ReportedIn = NULLIF(@ReportedIn, '1900-01-01')
So, took a bit of experimentation and tinkering, but We got it!!

Thank you so much.
Steve
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
You're welcome.

Regards,
    Tomas Helgi

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions