calculations using 0 in place of null

Question

I work for a car manufacturer and I'm creating a Sales Comparison report that compares current year data to previous year data.

In SQL I have 3 tables all related by a dealer code. One table is the dealer information, the other two tables contain both years data.

There are dealers that existed one year but not the other. So when I create an Editable Grid Screen in lightswitch with a computed field that subtracts Previous YTD from Current YTD I get a null validation error. Those particular dealers will show up, but with an empty or null value as opposed to a 0. So naturally, I get the error. I need to write validation code that will change null values to 0 before doing the calculation.

Once thing to add is that the computed field is on the Dealers Table in Lightswitch.

Answer 1

Please could you post the code that you are using in the event handler for the calculated column. Thx.

---

Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

Answer 2

I use these functions to convert nulls to zeros where necessary.

    Public Function ZeroIfNull(ByVal iobjValue As Decimal?) As Decimal

        If Not iobjValue.HasValue Then
            Return 0D
        Else
            Return iobjValue
        End If

    End Function

    Public Function ZeroIfNull(ByVal iobjValue As Object) As Object

        If iobjValue Is Nothing OrElse iobjValue Is DBNull.Value Then
            Return 0D
        Else
            Return iobjValue
        End If

    End Function

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 3

Thank you simon, wo questions.

1. what does D represent when you return a value of 0D

2.I created a computed field on a table to do this and I'm am adding that calculated field to a details screen. So here is the exact code I am using to edit the method on the computed field. CuuYTDTotal and PrevYTDTotal represent the the tables the information is coming from and YTD represents the fields being calculated.

Private Sub Difference_Compute(ByRef result As Integer)
            If PrevYTDTotal.YTD Is Nothing OrElse PrevYTDTotal.YTD Is DBNull.Value Then
                Return 0
                result = CurrYTDTotal.YTD - PrevYTDTotal.YTD
            Else
                result = CurrYTDTotal.YTD - PrevYTDTotal.YTD
            End If


        End Sub

Answer 4

Simon,

**Update**

I tried this code instead. Although the logic works and doesn't error, I still get the same result. You should know that the YTD number from both tables comes into Lightswitch from SQL as an integer which is why I wne twith is Nothing.

        Private Sub Difference_Compute(ByRef result As Integer)


            If PrevYTDTotal.YTD Is Nothing Then
                PrevYTDTotal.YTD = 0
                result = PrevYTDTotal.YTD - CurrYTDTotal.YTD
            ElseIf CurrYTDTotal.YTD Is Nothing Then
                CurrYTDTotal.YTD = 0
                result = PrevYTDTotal.YTD - CurrYTDTotal.YTD
            Else
                result = PrevYTDTotal.YTD - CurrYTDTotal.YTD
            End If



        End Sub

Answer 5

1. 0D means zero as a decimal. It avoid having the zero taken to be an integer constant and then converted to a decimal. Using the type designator also avoids under or overflow errors where the constant won't fit into an Integer but would in a Decimal. You could also return 0.0 but that could be mistaken to be a double or single precision constant and so still have to be converted. See http://msdn.microsoft.com/en-us/library/dzy06xhf.aspx

2. LightSwitch always represents NULL values as Nothing, not as DBNull.Value.
Your code assumes the CurrYTDTotal.YTD will never be NULL. This may or may not be correct.
You have Return 0 on a SUB - the value will not be returned. You need to set the result parameter.
The line after Return 0 will never be executed because Return will jump straight out of the Subroutine.

This would probably be better recoded as follows

Private Sub Difference_Compute(ByRef result As Integer)
    result = ZeroIfNull(CurrYTDTotal.YTD) - ZeroIfNull(PrevYTDTotal.YTD)
End Sub

If your totals are always integers, add an overload of the ZeroIfNull function to handle Integers specifically, which will save converting your Integer? values to Decimal? and back again.

    Public Function ZeroIfNull(ByVal iintValue As Integer?) As Integer

        If Not iintValue.HasValue Then
            Return 0
        Else
            Return iintValue
        End If

    End Function

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 6

This can happen because you are trying to change a databound value and that can take some time. The value may not actually be changed until after this subroutine has finished executing.

It also isn't really good form to change NULL values to zero like this. NULL values mean "unknown" and are different to zero. If you don't want NULLs you should change your database design and/or provide defaults. Changing these on-the-fly like this will also mark those records as dirty and try to force them to be saved.

BTW, even without the timing issue, the code above only copes with three out of the four possibilities for whether the two values could be NULL. It would fail if both of them were NULL. (It would enter the first THEN arm, attempt to set one value to zero and then fail on the subtraction because the other value was NULL.)

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 7

Simon,

There will never be a situation where they are both NULL

Also, there is not a ZeroIfNull function just DivideByZeroException. Can this be used? Otherwise, it seems as if this may be the solution for me.....If I can get it to work

Answer 8

I think you mean there should never be a situation where both values are NULL. You should always code defensively to avoid having to deal with errors.

I've given you the definition of the ZeroIfNull function several times.
It takes a nullable value and returns that value or zero if it was null.

Copy this code into your application.

    Public Function ZeroIfNull(ByVal iintValue As Integer?) As Integer
        If Not iintValue.HasValue Then
            Return 0
        Else
            Return iintValue
        End If
    End Function

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 9

Oh right I get it. ZeroIfNull if the function you created. I was looking past that and thinking it was a built in function and intelisense was not picking it up. I get it now. So here's my code I used in the computed field on the table, but I am still having the issue. Is there something I need to change in my declaration lne in the Private Function.

BTW thank you for your help. I'm ok with vb6 and SQL but new to VB, and lightswitch for that matter.

Private Sub Difference_Compute(ByRef result As Integer)
            result = ZeroIfNull(CurrYTDTotal.YTD) - ZeroIfNull(PrevYTDTotal.YTD)
        End Sub

        Public Function ZeroIfNull(ByVal iintValue As Integer?) As Integer
            If Not iintValue.HasValue Then
                Return 0
            Else
                Return iintValue
            End If
        End Function

Answer 10

Please restate the exact error message you are getting and, if possible, which line it is occurring on.

Give screenshots if it will help.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 11



Answer 12

That probably means that either CurrYTDTotal is Nothing or PrevYTDTotal is Nothing, NOT that CurrYTDTotal.YTD is Nothing or PrevYTDTotal.YTD is Nothing.

Do you understand the difference?

Hover over the variables in the debugger to find out which it is.

Also check the definitions of these fields/variables.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 13

CurrYTDTotal is the table and CurrYTDTotal.YTD is a column in that table and the same with PreviYTDTotal. Those two tables are related to a dealers Table in SQL. That Dealer Table contains all dealers 2013 and 2014 Active and Non Active. So naturally, Most of the time a dealer is sold it's a buy/sell so the number of active dealers stays the same. If a Dealer was sold in march, the selling dealer will have data pertaining to last year and this year. The buying dealer will only have data for this year. So it's possible for a Dealers data last year to be null because the Dealer Exists in the Dealer table, but not in PrevYTDTotal so the value is translated as null. This is why one of the tables(Previous or Current) can have NULL value but never both. So with all that said, shouldn't the ZeroIfNull function translate any Null values to 0 therefore creating  an equation for a dealer that existed last year but not this year to show  0-100="-100", and for the dealers that are new this year and did not exist last year to show as this 100-0="100".

Answer 14

The problem is that the related record doesn't exist.

The ZeroIfNull function isn't getting called because the CurrYTYDTotal record is Nothing and the error happens on trying to get CurrYTDTotal.YTD because there is no CurrYTDTotal record from which to get the YTD value.

Try this

Private Sub Difference_Compute(ByRef result As Integer)

     If PrevYTDTotal Is Nothing Then
         result =  - ZeroIfNull(CurrYTDTotal.YTD)
     ElseIf CurrYTDTotal Is Nothing Then
         result = ZeroIfNull(PrevYTDTotal.YTD) 
     Else
         result = ZeroIfNull(PrevYTDTotal.YTD) - ZeroIfNull(CurrYTDTotal.YTD)
     End If

End Sub 

If the YTD columns of your CurrYTDTotal and PrevYTDTotal tables are not nullable, you can miss out the call to ZeroIfNull altogether.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 15

Simon,

I understand and I thank you graciously for your help as I continue to learn a new language. I have since refocused my logic on handling nulls on SQL and changed the relation on my tables so now it works. I now have a new issue regarding decimals and decimal places that I will post on the board now.

Answer 16

You are more than welcome. I'm glad you got it sorted in the end.

Getting to grips with a new development environment is always tricky and I'm glad to be able to help in any way I can.

Don't forget to mark the answer(s) to this question so other people can find answers more quickly in the future.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.