cannot be converted to a number

[djkhalif]

Hello All,

I have run into another issue:

I have connected to DB2 (AS400) datasource and I am able to query data however, some of the data is unreadable. I have tried #ToString([encoding])# and this is where my data is crappy. Then, I tried replace and my results come back as "cannot be converted to a number." I am trying to get an ITEM number from AS400. Lastly, AS400 has a column with ITEM#; how can I write the string to overlook the # in the colmun name to that I can return results?

Thanks,

Khalif

[Guest]

You may be able to use the [] brackets around the ITEM# column in your SQL statement. I don't really know anything about the AS400 so I don't have any experience with scrambled data.

[djkhalif]

I tried it and that didn't resolve that issue. I'm going to keep trying at it.

[TLC-IT]

Well, a doubled hash-mark, "##", will be interpreted by CF as "the literal character '#' " amd this might be part-of what you are looking for.

You might need to use Regular Expressions to extract the "good" parts of a string so that you can then typecast it into a number.

You may also need to use a <cftry> block to catch runtime-errors produced by bogus data.

I'm a big believer in the rede which says, "your code should not only not be felled by bad data, but should pro-actively detect and report it."  Use a regular expression to validate what you know your incoming data should look like, and <cfthow> an exception with a meaningful message if it does not.  If you do this, your program will be able to tell you about data that it has encountred but did not know how to handle.  Ergo, if the program runs to completion without throwing up, you can assert that "all those millions of records" were not ill-formed.