How to Handle Nulls

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,
JusitnHi 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.
--
Ibrahim
"Justin Emlay" wrote:
> 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
>
>
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" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:AEAE83CC-FE57-4306-8D46-FC068A0AFFEC@.microsoft.com...
> 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.
> --
> Ibrahim
>
> "Justin Emlay" wrote:
> > 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
> >
> >
> >
> >
I googled for "Coalesce" and got nothing but unrelated items. The one link
that did look like a match discussed T-SQL.