CODEWITHSUNDEEP
c#mysqlsqlnull
Lorence Hernandez
Lorence Hernandez

Reputation: 1189

Data is Null This method or property cannot be called on Null values error keeps appearing

I dont know why i keep getting getting the error Data is Null. This method or property cannot be called on Null values.
heres my query that will get the sum of all totalovertime between february 18 2016 and february 23 2016 for employeenumber 1:

 select sum(totalovertime) as totalovertime from timeinout where employeenumber = 1 and dateofin between 2016-02-18 and 2016-02-23

heres my table:

 employeenumber | totalovertime | dateofin 
 1              | 1             | 2016-02-19
 1              | 1             | 2016-02-22

the query should return 2 as totalovertime, but the error keeps appearing

but when i do:

select sum(totalovertime) from timeinout where employeenumber = 1

this query returns 2 and thats correct.

Upvotes: 0

Views: 712

Answers (2)

Jerrad
Jerrad

Reputation: 5290

When dealing with dates, you need to surround them with single quotes. Otherwise, SQL will interpret 2016-02-18 as 2016 minus 2 minus 18. Change your query to be

select sum(totalovertime) as totalovertime 
from timeinout 
where employeenumber = 1 
and dateofin between '2016-02-18' and '2016-02-23'

Upvotes: 1

jwilliams
jwilliams

Reputation: 11

Try using ISNULL() on the value inside the SUM.

SUM(ISNULL(totalovertime, 0))

This checks if totalovertime is null and replaces it with 0 if it is, which the SUM can then handle correctly.

Its also possible that your DateOfIn is NULL for some fields and the BETWEEN operator is throwing your error, not sure if that is possible though (check your table data!)

Upvotes: 1

Related Questions