Intermittant Data Type Conversion Error

Intermittant Data Type Conversion Error

Post by Kent Sulzberg » Wed, 22 Oct 2003 21:08:42



Hello all,

I've got an occasional error (Error converting data type varchar to
numeric) occurring in a stored procedure on what looks like to be
valid numeric data. What's even stranger is if I copy the contents of
a "suspect" record to a new record, the SP works fine - however,
merely typing new values in the "suspect" record does not fix the
issue. The data is "real"; examples include 30 or 30.5. There are no
hidden spaces in the field. It is as if the record is somehow corrupt.

A portion of the SP is below. This snippet creates an upper limit for
use in a 3 sigma comparison.

SELECT ISNULL(AVG(CAST(Delta AS NUMERIC(4, 2))) + SQRT(VARP(CAST(Delta
AS NUMERIC(4, 2)))) * 3, 0) AS DELTAUPPERLIMIT

The data is inserted from a VB application that converts all values to
strings before insertion (I didn't create the app, but I did write the
SP). The connection is via ODBC. All the table's fields are formatted
varchar (except for a timestamp field).

Has anyone experienced this before? Does it have to do with the way
the data is written to the table?

Many thanks in advance,
Kent

 
 
 

Intermittant Data Type Conversion Error

Post by Steve Kas » Wed, 22 Oct 2003 22:12:21


Kent,

  Are you sure there are no non-printing characters anywhere?  Try this:

select Delta, cast(Delta as varbinary(20)) as DeltaBinary from yourTable
where isnumeric(Delta) = 0

If that returns nothing, you might have to try a bit harder, with
something like this:

where (Delta like '%[^0-9.]%'  or Delta like '%.%.%' or Delta like
'[1-9][0-9][0-9]%' or len(Delta) = 0)

-- Steve Kass
-- Drew University
-- Ref: FB825541-E743-4527-B211-F4687928901E


>Hello all,

>I've got an occasional error (Error converting data type varchar to
>numeric) occurring in a stored procedure on what looks like to be
>valid numeric data. What's even stranger is if I copy the contents of
>a "suspect" record to a new record, the SP works fine - however,
>merely typing new values in the "suspect" record does not fix the
>issue. The data is "real"; examples include 30 or 30.5. There are no
>hidden spaces in the field. It is as if the record is somehow corrupt.

>A portion of the SP is below. This snippet creates an upper limit for
>use in a 3 sigma comparison.

>SELECT ISNULL(AVG(CAST(Delta AS NUMERIC(4, 2))) + SQRT(VARP(CAST(Delta
>AS NUMERIC(4, 2)))) * 3, 0) AS DELTAUPPERLIMIT

>The data is inserted from a VB application that converts all values to
>strings before insertion (I didn't create the app, but I did write the
>SP). The connection is via ODBC. All the table's fields are formatted
>varchar (except for a timestamp field).

>Has anyone experienced this before? Does it have to do with the way
>the data is written to the table?

>Many thanks in advance,
>Kent


 
 
 

Intermittant Data Type Conversion Error

Post by Kent Sulzberg » Thu, 23 Oct 2003 20:48:17


Thanks for your input, Steve. I'll have to wait a bit before the error
occurs again, then try your suggestions. I clean out suspect records
almost immediately, to maintain a production process.

One other interesting note: I tried to "null" out the field (ctrl+0),
then type in a new value & the error remains. The only fix is to
delete the record & create a new one. Could there be hidden data in a
field that is supposedly null?

Thanks,
Kent


> Kent,

>   Are you sure there are no non-printing characters anywhere?  Try this:

> select Delta, cast(Delta as varbinary(20)) as DeltaBinary from yourTable
> where isnumeric(Delta) = 0

> If that returns nothing, you might have to try a bit harder, with
> something like this:

> where (Delta like '%[^0-9.]%'  or Delta like '%.%.%' or Delta like
> '[1-9][0-9][0-9]%' or len(Delta) = 0)

> -- Steve Kass
> -- Drew University
> -- Ref: FB825541-E743-4527-B211-F4687928901E


> >Hello all,

> >I've got an occasional error (Error converting data type varchar to
> >numeric) occurring in a stored procedure on what looks like to be
> >valid numeric data. What's even stranger is if I copy the contents of
> >a "suspect" record to a new record, the SP works fine - however,
> >merely typing new values in the "suspect" record does not fix the
> >issue. The data is "real"; examples include 30 or 30.5. There are no
> >hidden spaces in the field. It is as if the record is somehow corrupt.

> >A portion of the SP is below. This snippet creates an upper limit for
> >use in a 3 sigma comparison.

> >SELECT ISNULL(AVG(CAST(Delta AS NUMERIC(4, 2))) + SQRT(VARP(CAST(Delta
> >AS NUMERIC(4, 2)))) * 3, 0) AS DELTAUPPERLIMIT

> >The data is inserted from a VB application that converts all values to
> >strings before insertion (I didn't create the app, but I did write the
> >SP). The connection is via ODBC. All the table's fields are formatted
> >varchar (except for a timestamp field).

> >Has anyone experienced this before? Does it have to do with the way
> >the data is written to the table?

> >Many thanks in advance,
> >Kent

 
 
 

1. Data type conversion error when saving numeric data types

I have a textbox which is bound to an Access database field (numeric data
type).
If I type a digit into the box, then the record can be saved ok.  If I
remove the digit again and try to save the record, then I get a data type
conversion error.
Does anyone know a way around this, so that if a user blanks the box, then
the record can be saved, and the box remains blank?

I know I can do this in an Access form, but can not seem to just blank the
box in a VB form.

Even 'text1 = val(text1)' just puts a zero in the box, but I do not want a
setting of zero if the user wants no setting to be printed.

Any help would be very much appreciated, as this has been bugging be for a
while now.

Thanks

Ed

2. DAO 3.5 and Win98

3. Disallowed implicit conversion from data type nvarchar to data type money

4. Sort Order error after Restore

5. conversion of ODBC SQL data types to ODBC C data types

6. What is better passing Cursor or quering database

7. data type conversion error

8. ANN: Database Workbench v2.3.1 released

9. Action was cancelled by the associated object - Data type conversion error

10. Data type conversion error

11. Data Type conversion error?

12. View to an Oracle Table data type conversion error