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?
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.
- How to do a single line If statement in VBScript for Classic-ASP? (Mentioned by @TasosK in the comments)
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.
If Not IsNull(rs("ContractValue")) Then
<%=FormatNumber(rs("ContractValue"),0)%>
end if
Do not be in a hurry with Classic ASP.
I'm sure you want to insert content in between some HTML code which made you bunch up all that code. If that is the case, I suggest you separate VBscript code from HTML like below for example;
<%
Dim valueToOutput
If Not IsNull(rs("ContractValue")) Then
valueToOutput=FormatNumber(rs("ContractValue"),0)
end if
%>
<!-- HTML Code continues below with an inserted VBscript variable -->
There are a total of <%=valueToOutput%> oranges available!
