data type conversion

Adam H-W · March 2nd, 2004, 06:12 AM

Hi there

I'm trying to update a table via the UPDATE function (via an ASP page) but keep getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'db_shhc.dbo.PPack', column 'FixPrice'. Use the CONVERT function to run this query.

I'm trying to change a value from 2 to 2.5 - I'm not sure why it's disallowing me to UPDATE from 2 to 2.5 - it works fine if I change the data type to varchar in the sql table but I don't want it to be varchar.

thanks

Adam

Jeff Mason · March 2nd, 2004, 08:03 AM

The error message pretty much says it all - you are attempting to SET the value of a column defined as a money datatype to a character string value. This requires an explicit conversion. Your UPDATE should look like:

Code:

UPDATE yourtable
    SET yourmoneycolumn=CAST(yourstringvalue AS money)
    ...

or alternatively,

Code:

UPDATE yourtable
    SET yourmoneycolumn=CONVERT(money, yourstringvalue)
    ...

Make sure that the conversion can in fact be done, since if your string data doesn't 'look' like a money type, you'll still get an error. You will have to edit the string before you use it in the UPDATE statement to make sure it is a proper numeric value.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Adam H-W · March 2nd, 2004, 08:38 AM

Great, thanks Jeff; will give it a bash.