IsNull syntax error 1

[cfcProgrammer]

Hi,

Please be patient. I am still fairly new to this world. I come from an RPG AS400 background, so this is a little different.

I am trying to calculate a field from a record set that I loaded into an array(repArrRS).

Here is the calculation I had originally.

<%TotalSgFor = TotalSgFor + CDbl(repArrRS(6,repRow))%>

BUT I was getting an invalid use of Null. Cdbl.

So, I went to the net and searched on this error and this is what I came up with.

TotalSgFor = iif(IsNull(repArrRS(6,reprow)),0,repArrRS(6,reprow)) + TotalSgFor

I am now getting a syntax error. I can't seem to see what the problem is and I've had a few people here at the office take a look... no one is quite sure what needs to be done to fix it.

Could someone please help me try to figure this out. If you feel there is a better way to accomplish the calculation, taking Nulls into account, I am open to suggestions.

The only thing I ask is that you explain why or how the statment works.. that way... I'm learning the reasoning behind it and I can share with the office... and next time any of us run into the same problem we'll know what to do.


Thanks so much... as always... your help is greatly appreciated!! :->

cfcProgrammer

 

[gmmastros]

a record set that I loaded into an array

I assume this recordset is coming from a database. As such, I would handle this problem in the database itself.

The syntax for handling NULL in a database depends on the database itself. For SQL Server, I would change the query.

Ex:

[tt][blue]
Select Col1, Col_That_Can_Contain_Null
From Table
etc....[/blue][/tt]

To....

[tt][blue]
Select Col1, [!]Coalesce([/!]Col_That_Can_Contain_Null[!], 0) As Col_That_Can_Contain_Null[/!]
From Table
etc....[/blue][/tt]

Notice I used [!],0[/!]. I did this because you are handling the value as though it is a number. For strings, you could use... [!], ''[/!]

Basically, Coalesce will use the value of the first argument if it is NOT NULL. If it is, it will use the value of the second argument. Since the second argument is hardcoded as a Non Null value, you will never get a NULL in your ASP code.

The AS part is called an alias. Since you are performing an operation on the column in the select part of the query, you SHOULD provide an alias. Technically, it's not necessary, but it's a good habit to get in to.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cfcProgrammer]

Hi George!

It makes perfect sense... thank you so much.. you're always a great help.

You were helping me with another issue on here the other day... I got pulled away from it for a few days but will be getting back to you soon with a few more questions on it.

Thanks again... Have a great day!!

Colleen



cfcProgrammer