Search code examples
vbscriptasp-classic

If Not IsNull in ASP Classic

I'm fairly new to asp and I've got a syntax error I would like help on if you can.

I've got an ASP page that shows a table that pulls data from sql. Most of the data hasn't been populated yet so returns a NULL. The data type in question is numeric. I need to FormatNumber the rs when it is not null and not populate if it is.

This is what I have

<%=If Not IsNull(rs("ContractValue")) Then FormatNumber(rs("ContractValue"),0) end if%>

But as mentioned, im getting a syntax error.

What am i doing wrong?

Solution

  • I would recommend not using IsNull() in this scenario, but to answer the question about the syntax error first.

    The reason is the <%= %> syntax which is shorthand for

    <% Response.Write %>

    in Classic ASP.

    So what you are actually doing if written without the shorthand approach is;

    <% Response.Write If Not IsNull(rs("ContractValue")) Then FormatNumber(rs("ContractValue"),0) End If %>

    which is incorrect syntax and will trigger a Syntax Error.

    To fix the code remove the = from the <% %> tags, like so;

    <% If Not IsNull(rs("ContractValue")) Then Response.Write FormatNumber(rs("ContractValue"),0) End If %>

    What about using IsNull?

    While this can work it can often give weird results because a DBNull (depending on the database being used) can be different and is often different to the VBScript vbNull variant.

    Because of this and the fact VBScript isn't strongly typed I find it useful to use a simple quick cast to string to avoid Nulls then check for valid data.

    Example numeric check

    Dim contractValue
contractValue = rs("ContractValue") & ""
If Len(contractValue) > 0 And IsNumeric(contractValue) Then contractValue = Clng(contractValue) Else contractValue = 0

    You can take this further by writing a reusable piece of code that IIf() function explained in this post.

    Something like this;

    Dim contractValue
contractValue = rs("ContractValue") & ""
contractValue = IIf(Len(contractValue) > 0 And IsNumeric(contractValue), contractValue, 0)

    @Paul made a good point about evaluation of parameters, in the original code would potentially break 

    contractValue = IIf(Len(contractValue) > 0 And IsNumeric(contractValue), Clng(contractValue), 0)

    because Clng(contractValue) would be evaluated regardless of whether the outcome was True or False. So any formatting would need to be afterwards or a more complex version of the IIf() function be built.