ASP - add calcuation with NULL record

Author

add calcuation with NULL record

Tony WONG

2006-02-14, 3:55 am

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

Bob Barrows [MVP]

2006-02-14, 7:55 am

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

2006-02-15, 3:55 am

Bob

Thanks a lot for your detail analysis and advice.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> ¼¶¼g©ó¶l¥ó·s»D:O7RGC5VMGHA.916@TK2MSFTNGP10.phx.gbl...
> Tony WONG wrote:
>
> 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.
>
>
> 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]

2006-02-15, 7:55 am

Tony WONG wrote:
[color=darkred]

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

2006-02-19, 9:55 pm

never mind

i already fixed your formula into my case.

your formula have already helped me a lot.

thanks.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> ¼¶¼g©ó¶l¥ó·s»D:%23ekNmZiMGHA.964@tk2msftngp13.phx.gbl...
> Tony WONG wrote:
>
>
> 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"
>

Sponsored Links