DBNull Value on Calculated Column

[Roey]

Is there a better way of displaying a zero on a calculated column than by using the method below:

 

    Private Sub DateSort()

       Dim GetDate As Date = Me.GetDays()
       Dim row As DataRow

       For Each row In Me.dsInventory.Tables("TransActions").Rows
           row.Item("Past") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #01/01/1900# And TransDate < #" & GetDate.AddDays(-7) & "#")
           row.Item("Week2") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(-6) & "# And TransDate < #" & GetDate.AddDays(0) & "#")    'This Week
           row.Item("Week3") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(1) & "# And TransDate < #" & GetDate.AddDays(7) & "#")
           row.Item("Week4") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(8) & "# And TransDate < #" & GetDate.AddDays(14) & "#")
           row.Item("Week5") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(15) & "# And TransDate < #" & GetDate.AddDays(21) & "#")
           row.Item("Week6") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(22) & "# And TransDate < #" & GetDate.AddDays(28) & "#")
           row.Item("Week7") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(29) & "# And TransDate < #" & GetDate.AddDays(35) & "#")
           row.Item("Week8") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(36) & "# And TransDate < #" & GetDate.AddDays(42) & "#")
           row.Item("Future") = Me.dsInventory.Tables("TransActions").Compute("sum(Quantity)", "TransDate > #" & GetDate.AddDays(43) & "# And TransDate < #01/01/2300#")
           If row.Item("Past") Is DBNull.Value Then
               row.Item("Past") = 0
           End If
           If row.Item("Week2") Is DBNull.Value Then
               row.Item("Week2") = 0
           End If
           If row.Item("Week3") Is DBNull.Value Then
               row.Item("Week3") = 0
           End If
           If row.Item("Week4") Is DBNull.Value Then
               row.Item("Week4") = 0
           End If
           If row.Item("Week5") Is DBNull.Value Then
               row.Item("Week5") = 0
           End If
           If row.Item("Week6") Is DBNull.Value Then
               row.Item("Week6") = 0
           End If
           If row.Item("Week7") Is DBNull.Value Then
               row.Item("Week7") = 0
           End If
           If row.Item("Week8") Is DBNull.Value Then
               row.Item("Week8") = 0
           End If
           If row.Item("Future") Is DBNull.Value Then
               row.Item("Future") = 0
           End If
       Next

   End Sub 

[wessamzeidan]

You can do it using a function

 

Private Function CheckIfDBNull(ByVal ob as Object) as Integer

If ob Is DBNull.Value Then

return 0

Else

Return Ctype(ob,Integer)

End Function

 

 

then you can use this function

row.Item("Future")=CheckIfDBNull(row.Item("Future"))

[Nerseus]

Can you use IsNull on the Compute method? Something like:

...Compute("IsNull(sum(Quantity), 0)", ...

 

I can't remember. If there's no built-in way, then I'd suggest a function.

 

-Ner