8

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?

user692942
  • 16,398
  • 7
  • 76
  • 175
Ginga Dave
  • 103
  • 1
  • 1
  • 5
  • You are asking how to do an inline if statement, possible duplicate http://stackoverflow.com/questions/20353072/how-to-do-a-single-line-if-statement-in-vbscript-for-classic-asp – Tasos K. Oct 21 '15 at 08:34
  • 2
    @TasosK. That isn't what they are asking. – user692942 Oct 21 '15 at 08:36
  • @Lankymart IMHO, it is an attempt to write an if statement in one line, and the answer would be the use of inline if. – Tasos K. Oct 21 '15 at 08:38
  • 2
    @TasosK. `<% If .... Then .... Else ... End If %>` is valid syntax in Classic ASP the issue is the `<%=` causing the syntax error because you can't `Response.Write` an `If` statement. They are asking what they are doing wrong not how to write an `If` statement. – user692942 Oct 21 '15 at 08:46

3 Answers3

8

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.

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
  • Now im getting this ---- Microsoft VBScript compilation error '800a0414' Cannot use parentheses when calling a Sub /Fees.asp, line 89 If Not IsNull(rs("ContractValue")) Then FormatNumber(rs("ContractValue"),0) End If ----------------------------------------------------------------------------^ – Ginga Dave Oct 21 '15 at 09:40
  • `IIf` - always one of the first tools in my bag. – Paul Oct 21 '15 at 10:14
  • 1
    Just as a note on `IIf`, you have to be very careful with its usage. All arguments in `IIf` are evaluated whether you like it or not so Lankymart's version may well break if `contractValue` is not numeric (i.e. Null, Nothing or even a string). – Paul Oct 21 '15 at 10:16
  • @GingaDave Apologies I blindly copied the code, the error is correct because `FormatNumber()` has to assign a value to something in this case just output it with `Response.Write` but it could be assigned to variable as well. Have updated the example. – user692942 Oct 21 '15 at 10:27
  • Good point @Paul that will teach me for not testing code first. Updated answer. – user692942 Oct 21 '15 at 10:40
  • 1
    I worked more than 12 years on Classic ASP (VBScript) and one of the most useful series of functions I created to my personal library was toStr, toDbl, toInt ... all of them receives two parameters (value, defaultValue) and if _value_ cannot be converted to the proper type, it would return _defatulValue_; it is *very useful* to get parameters from Request and values from database. – Gerardo Lima Oct 21 '15 at 13:13
  • @GerardoLima Absolutely, I also have a plethora of *utility* functions for these types of things, dates is another one. – user692942 Oct 21 '15 at 13:23
  • 1
    @GerardoLima: My must have function is `FormData(dataItem, nullVal)` which reads data from either posted data *or* query string data and assigns a default value if it doesn't exist. Soooo useful, one call solves all. – Paul Oct 22 '15 at 07:53
  • I've posted another question that i think you will be able to guide / help me on - http://stackoverflow.com/questions/33280063/calculations-in-asp-classic-and-formatnumber-the-result – Ginga Dave Oct 22 '15 at 11:38
  • @Paul, the problem with 'FormData(val, defVal)' approach is that it doesn't convert to the corresponding Variant subtype (integers, dates, booleans, ...); working with proper types avoids lots of problems and bugs that are "unnexpected" and really hard to find. – Gerardo Lima Oct 26 '15 at 15:39
  • 1
    @GerardoLima True, but there is nothing stopping that approach being expanded to something like `Form(val, dataType, defVal)` using constants for the data types and handling them accordingly, admittedly it's more work and you can also get edge cases where it just won't work as expected. – user692942 Oct 26 '15 at 15:44
  • 1
    @GerardoLima: true, but then the data coming back is going to be a string anyway. A developer *should* know this and adjust accordingly. If they don't then it's a classic case of putting the computer back in the box and shipping back to the supplier. One of the tricks I use, for instance, is to set a `postBack` value using: `postBack = (FormData("postBack", "false") = "true")`. Once again, the old adage of: *"Never assume as it makes an ass of u and me"* should be kept in mind. – Paul Oct 26 '15 at 15:45
1
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!
Ezeilo Uchenna
  • 71
  • 4
  • Good advice, always cleaner to attempt to separate logic from presentation where possible. – user692942 Oct 21 '15 at 13:28
  • I've posted another question that i think you will be able to guide / help me on - http://stackoverflow.com/questions/33280063/calculations-in-asp-classic-and-formatnumber-the-result – Ginga Dave Oct 22 '15 at 11:38
0

If dealing with too many null fields, the code will be riddled with too many IF-THEN-ELSE statements and that would look really ugly.

Consider using the COALESCE function on the database side, so the field values don't come up as null on the recordset, or alternatively, consider using your own coalesce function in ASP that you can use over and over again.

Function Coalesce(inputValue, replaceWith)
      if isnull(X) then 
           Coalesce = replaceWith 
      else 
           Coalesce = inputValue 
      end if
End Function

Then you can use something like this:

<%=FormatNumber(Coalesce(rs("ContractValue"),0),0)%>
Kleber Alanis
  • 1
  • 1