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.

Cup size   
Select size then click on coffee cup.
This entry was posted in Problems & Fixes, Snippets. Bookmark the permalink.