Coldfusion Error – value cannot be converted to a number

Here is a message that comes up often in CF error messages that is sometimes tough to figure out.

The value '' cannot be converted to a number.
This usually indicates that a Null value (ie record is missing a rowid, userid, phoneNbr, or dateadd calculation) is being converted to a number and CF doesn’t allow Null to Number. It is usually buried in a SQL statement like the following.

Select a.*, b.user_email, c.geo_region
from orders a, users b, states c
where a.user_id = b.user_id
and convert(numeric b.billing_zipcode) = convert(numeric, c.zipcode)
IF billing_zipcode in Table USERS is not numeric(ie Null, a space, or contains non-numeric data) the join fails and throws an error.

The key part of any error for conversion is to look at the type: “Converted to a Number” vs “Converted from Int to VAR CHAR”, Then find that conversion in your SQL or CF formats.