add calcuation with NULL record

[Tony WONG]

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]]

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]

Bob

Thanks a lot for your detail analysis and advice.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> ¼¶¼g©ó¶l¥ó·s»D:O7*************@TK2MSFTNGP10.phx.gb l...

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]]

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]

never mind

i already fixed your formula into my case.

your formula have already helped me a lot.

thanks.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> ¼¶¼g©ó¶l¥ó·s»D:%2***************@tk2msftngp13.phx. gbl...

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"