nvarchar to money - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

nvarchar to money

Author

Topic

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 15:34:27

Implicit conversion from data type nvarchar to money is not allowed. Use the CONVERT function to run this query.

I am getting this error on my page upon refresh. The page wont even load. However, the data I am entering into this money field is, for example, something like this 12.25

Whats the problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-30 : 15:36:17

You need to use CONVERT like the message says.

SELECT CONVERT(money, nVarCharColumnName)
FROM...

Look up CONVERT in SQL Server Books Online to find out which ones require CONVERT and which ones can do an implicit conversion.

Tara

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 15:43:27

Ahh.. so no matter what I need to convert it?

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 15:44:45

The column is of data type money, though

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-30 : 15:49:17

Somewhere you've got it defined as nvarchar and somewhere else as money. Whether it be in a variable, in the table, in code, or somewhere else.

Perhaps you are using dynamic sql and trying to concatenate it with the string.

Tara

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 15:52:04

CREATE PROCEDURE InsertCommAdj

@TerritoryManager as nvarchar(50),
@AdjustmentType as nvarchar(30),
@DollarAmount as money,
@DaysCovered as int,
@Comments as nvarchar(1000)

AS

Insert into CommAdjustments
(EmployeeID, AdjustmentType, DollarAmount, DaysCovered, Comments)
Select Employees.EmployeeID, @AdjustmentType, CONVERT(money,@DollarAmount), @DaysCovered, @Comments
From Employees
Where Employees.FirstName + ' ' + Employees.LastName = @TerritoryManager
GO

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 15:52:51

[quotePerhaps you are using dynamic sql and trying to concatenate it with the string.[/quote]

Whats this mean? Because i don't believe I am defining it as an nvarchar anywhere.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-30 : 15:56:59

Your stored procedure isn't using dynamic sql so that's not it.

If you run InsertCommAdj in Query Analyzer, does it give the error? If it doesn't, then the problem is within the application and not with the stored procedure. Always get the stored procedure to work correctly in Query Analyzer first, then call it from your application.

Since @DollarAmount is defined as money, you won't need to use CONVERT in the SELECT statement.

Tara

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 16:01:17

hmm.. The stored procedure does execute fine, and the error my app is giving me is;

Line 53:
Line 54: conn.open()
Line 55: cmd1.ExecuteNonQuery()
Line 56:
Line 57: conn.Close()

This is the line that calls InsertCommAdj.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-30 : 16:02:58

I can't help you, but perhaps someone else here can.

Tara

Kristen
Test

22859 Posts

Posted - 2004-06-30 : 16:03:33

Are you passing the @DollarAmount parameter in your SProc a String value from your application?

(Dunno if SQL will do an implicit conversion in that circumstance, but I don't think it ever does [to money])

Kristen

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 16:12:38

yeah. i believe i am.

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 16:20:05

I mean no. I am not. HaHa

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-30 : 16:24:55

Betcha it's spaces

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-06-30 : 16:30:30

quote:
Originally posted by cusoxty

I mean no. I am not. HaHa

So the "type" of the variable you are passing from ASP is already a SQL MONEY type? That's pretty hard to acieve in VBScript!

Kristen

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-30 : 16:39:37

hmm. ok then.. what do you recommend?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-30 : 17:19:56

I recommend you run this in debug and send us the "exact" statement you are sending to SQL Server.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-30 : 17:28:34

quote:
Originally posted by cusoxty

hmm. ok then.. what do you recommend?

If I was not sure that I could provide the correct datatype I would change the parameter in the SProc to be VARCHAR(nn) and then CONVERT/CAST that to MONEY within the SProc.

MONEY is funny in this regard. Numeric types are generally happy with an implicit data concersion, but IME MONEY is much more fussy (not just IME either, BOL says it too!).

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources