Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2004, 06:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 347
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Adam H-W
Default data type conversion

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
 
Old March 2nd, 2004, 08:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 2nd, 2004, 08:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 347
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Adam H-W
Default

Great, thanks Jeff; will give it a bash.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion from type 'DBNull' to type 'String' is GailCG ASP.NET 2.0 Basics 5 February 22nd, 2007 03:12 PM
Help with data type conversion please androoo Pro VB 6 2 December 24th, 2004 05:45 PM
Help with data type conversion please androoo Pro VB.NET 2002/2003 0 November 29th, 2004 09:06 AM
Data Type Conversion using WHERE IN () Colonel Angus SQL Server 2000 14 August 18th, 2004 08:08 PM
Data Type Conversion owain SQL Language 5 October 31st, 2003 12:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.