add calcuation with NULL record

Discussion in 'ASP General' started by Tony WONG, Feb 14, 2006.

  1. Tony WONG

    Tony WONG Guest

    i use the below formula to add up records which is extracted from SQL by
    ASP.
    sumQS = cint(objRS1("Q1S")) + cint(objRS1("Q2S")) + ....

    but if cint(objRS1("Q1S")) is null, it gets error.

    i read solution from book that add ZERO value to those NULL variables by
    using IsNULL.

    is it the only way to deal with this cos i have lots of these variables?
    then lots of code.

    Thanks a lot.

    tony
    Tony WONG, Feb 14, 2006
    #1
    1. Advertising

  2. Tony WONG wrote:
    > i use the below formula to add up records which is extracted from SQL
    > by ASP.
    > sumQS = cint(objRS1("Q1S")) + cint(objRS1("Q2S")) + ....


    This seems like bad database design to me. If all of your QS values were
    stored in a single column in a database table, a simple grouping query would
    vastly simplify this task you have set yourself.

    >
    > but if cint(objRS1("Q1S")) is null, it gets error.
    >
    > i read solution from book that add ZERO value to those NULL variables
    > by using IsNULL.
    >
    > is it the only way to deal with this cos i have lots of these
    > variables? then lots of code.


    One thing you can do is use functions in your sql statement that generates
    this recordset to guarantee that no nulls are returned. I can't get more
    specific due to your failure to tell us what database you are using, but I'm
    sure if you looked at the online help for the SQL variant used by your
    database, you can find those functions (COALESCE for SQL Server, Iif for
    Access)

    That said, it is always costly to directly read values from your recordset
    object. If you are using the same values more than once, especially inside a
    loop, you can greatly improve performance by assigning the values to
    variables. Assigning the values to variables is the perfect time to make
    sure that nulls are handled. You can put this function in your script
    library:

    Function HandleNull(data)
    if data is null then HandleNull = 0
    End Function

    Then, when processing your recordset:

    dim q1s, q2s, ...
    q1s=HandleNull(objRS1("q1s").value)
    etc.

    You should consider that other datatypes (string, date, etc) can also
    contain nulls, so you will want to handle those differently.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Feb 14, 2006
    #2
    1. Advertising

  3. Tony WONG

    Tony WONG Guest

    Bob

    Thanks a lot for your detail analysis and advice.


    "Bob Barrows [MVP]" <> ¼¶¼g©ó¶l¥ó·s»D:...
    > Tony WONG wrote:
    >> i use the below formula to add up records which is extracted from SQL
    >> by ASP.
    >> sumQS = cint(objRS1("Q1S")) + cint(objRS1("Q2S")) + ....

    >
    > This seems like bad database design to me. If all of your QS values were
    > stored in a single column in a database table, a simple grouping query
    > would vastly simplify this task you have set yourself.
    >
    >>
    >> but if cint(objRS1("Q1S")) is null, it gets error.
    >>
    >> i read solution from book that add ZERO value to those NULL variables
    >> by using IsNULL.
    >>
    >> is it the only way to deal with this cos i have lots of these
    >> variables? then lots of code.

    >
    > One thing you can do is use functions in your sql statement that generates
    > this recordset to guarantee that no nulls are returned. I can't get more
    > specific due to your failure to tell us what database you are using, but
    > I'm sure if you looked at the online help for the SQL variant used by your
    > database, you can find those functions (COALESCE for SQL Server, Iif for
    > Access)
    >
    > That said, it is always costly to directly read values from your recordset
    > object. If you are using the same values more than once, especially inside
    > a loop, you can greatly improve performance by assigning the values to
    > variables. Assigning the values to variables is the perfect time to make
    > sure that nulls are handled. You can put this function in your script
    > library:
    >
    > Function HandleNull(data)
    > if data is null then HandleNull = 0
    > End Function
    >
    > Then, when processing your recordset:
    >
    > dim q1s, q2s, ...
    > q1s=HandleNull(objRS1("q1s").value)
    > etc.
    >
    > You should consider that other datatypes (string, date, etc) can also
    > contain nulls, so you will want to handle those differently.
    >
    > Bob Barrows
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Tony WONG, Feb 15, 2006
    #3
  4. Tony WONG wrote:

    >> Function HandleNull(data)
    >> if data is null then HandleNull = 0
    >> End Function
    >>


    Wow! What a stupid mistake! I can't believe nobody called me on this. The
    function, of course, should read:

    Function HandleNull(data)
    if data is null then
    HandleNull = 0
    else
    HandleNull = data
    end if
    End Function


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Feb 15, 2006
    #4
  5. Tony WONG

    Tony WONG Guest

    never mind

    i already fixed your formula into my case.

    your formula have already helped me a lot.

    thanks.

    "Bob Barrows [MVP]" <> ¼¶¼g©ó¶l¥ó·s»D:%...
    > Tony WONG wrote:
    >
    >>> Function HandleNull(data)
    >>> if data is null then HandleNull = 0
    >>> End Function
    >>>

    >
    > Wow! What a stupid mistake! I can't believe nobody called me on this. The
    > function, of course, should read:
    >
    > Function HandleNull(data)
    > if data is null then
    > HandleNull = 0
    > else
    > HandleNull = data
    > end if
    > End Function
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Tony WONG, Feb 20, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    5
    Views:
    26,630
    Mike Schilling
    Mar 29, 2006
  2. André
    Replies:
    0
    Views:
    3,745
    André
    Jun 25, 2006
  3. André
    Replies:
    0
    Views:
    438
    André
    Jun 25, 2006
  4. Replies:
    0
    Views:
    171
  5. ice
    Replies:
    0
    Views:
    70
Loading...

Share This Page