How to Handle Nulls

  • Thread starter Thread starter Justin Emlay
  • Start date Start date
J

Justin Emlay

Nulls continue to kick my rear. It would have been so much easier to just
fill the DB with Zeros. But then people flame me for wasting drive space.
I just cant win! :)

Anyways, below is a snippet of code I'm using (minus the extras vacation and
holiday....). I need to add these fields up (along with other math) but a
lot of times the fields will be null. I cant seem to be able to do anything
with null fields regardless of how I format them. So I used try statements
and new variables to move the field data in to. Since the only result I can
get from adding these fields is an error the try statement is perfect
because then I can continue and fill with "0" since that would be the case.

BUT

Doing it this way takes a LONG time because you have to wait to each to
error out. I'm looping through these calcs many many times.

Try
RegHours = EmployeeRow.Item("Reg Hours")
Catch ex As Exception
RegHours = "0"
End Try
Try
OverHours = EmployeeRow.Item("Over Hours")
Catch ex As Exception
OverHours = "0"
End Try

If RegHours + OverHours < 80 Then....


So how can I format the following where if one field = 8 and the other field
= Null I still wind up with 8 after adding them instead of a system error
stating "anything" cant be converted to null.

EmployeeRow.Item("Reg Hours") + EmployeeRow.Item("Over Hours")

Thanks for any help,
Jusitn
 
Hi Justin,

Not your code however maybe this helps better..

Using VB it is

datarowfield Is DbValue.Null (means Null in database notation)
Objectreference Is Nothing (means there is no reference)
Value = Nothing (means the value is in the defaultstate what can be "", 0 or
01-01-1900 00:00:00)

I hope this helps?

Cor
 
Hi
Or you can convert them to 0 while retrieving values from the table. For example in SQL Server you can do something like

select columnA = coalesce(ColumnA,'0'), columnB = coalesce(ColumnB,'0') from TableA

So, if ColumnA and B are null they are replaced with Zeros. This will prevent you from checking for nulls in your api. Hope this helps.
 
Is that supposed to be my select statement? I didn't understand it :(

This is one of my statements that I'm currently using:

SELECT TimeSheets.Employee, SUM(TimeSheets.[Hol Hours]) AS [Hol Hours],
TimeSheets.[Job Number], SUM(TimeSheets.[Over Hours]) AS [Over Hours],
SUM(TimeSheets.[Reg Hours]) AS [Reg Hours], SUM(TimeSheets.[Vac Hours]) AS
[Vac Hours], Employee.Type, TimeSheets.Dept, TimeSheets.GL FROM (Employee
INNER JOIN TimeSheets ON Employee.Employee = TimeSheets.Employee) WHERE
[Period Start] = @PeriodStart GROUP BY TimeSheets.Employee, TimeSheets.[Job
Number], Employee.Type, TimeSheets.Dept, TimeSheets.GL ORDER BY
TimeSheets.Employee, TimeSheets.GL




Ibrahim Shameeque said:
Hi
Or you can convert them to 0 while retrieving values from the table. For
Click to expand...
example in SQL Server you can do something like
select columnA = coalesce(ColumnA,'0'), columnB = coalesce(ColumnB,'0') from TableA

So, if ColumnA and B are null they are replaced with Zeros. This will
Click to expand...
prevent you from checking for nulls in your api. Hope this helps.
 
I googled for "Coalesce" and got nothing but unrelated items. The one link
that did look like a match discussed T-SQL.
 
Back
Top