?
Solved

Convert string from textbox into type money SQL Server 2005

Posted on 2010-09-05
7
Medium Priority
?
616 Views
Last Modified: 2012-08-13
I usually use SQL Server 2008 and the following code works perfectly with it so I think its an SQL Server 2005 thing but am not 100% sure.

I am getting an amount of money from a textbox and inputting it into a database table with a field 'money'

am using a stored procedure - it works fine with every other field apart from the money field.

the code below doesn't work (i'm passing through a textbox (string))

I tried using CONVERT(MONEY, @Amount) instead of just @Amount but it made no difference.

anyone know what i'm missing?


p.s. - before someone asks I have checked that it is passing the correct textbox etc.
ALTER PROCEDURE [dbo].[procedurename]
	@Amount money = null
AS
BEGIN
from
	SET NOCOUNT ON;

	INSERT INTO Table1 (Amount)
	VALUES(@Amount);
	
	SELECT SCOPE_IDENTITY();

Open in new window

0
Comment
Question by:jd1991
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33607276
try this


insert into table1 (Amount)
values (CONVERT(VARCHAR,(@Amount,1)))


0
 

Author Comment

by:jd1991
ID: 33607304
nope doesn't work, says 'incorrect syntax near ',' (thats the commar with the 1 after it)

if i remove the ",1" bit it says "Disallowed implicit conversion from data type varchar to data type money"
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33607318
sorry, remove the first comma. should be:

values (CONVERT(VARCHAR,@Amount,1))

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:jd1991
ID: 33607342
that brings up an error saying cannot convert from data type varchar to data type money - which makes sense as what you have told me to do is convert a string to varchar and put varchar into a field which is of data type money... which doesn't really make sense.

anyway i tired it with CONVERT(MONEY,@Amount,1) instead bit it doesn't work
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 2000 total points
ID: 33607421
Can you check if this works:

      INSERT INTO Table1 (Amount)
      VALUES(CONVERT(money,'$1,000'));
      
      SELECT SCOPE_IDENTITY();

If it does,then I dont think its SQL 2005 issue, but data issue.

Thanks.
0
 

Author Comment

by:jd1991
ID: 33607451
thanks looks like it is a data issue, strange thing is though that it does work perfectly with sql server 2008. I'll have a look and see exactly what it is passing to it.
0
 

Author Comment

by:jd1991
ID: 33607463
thank you - got it working :)
was a silly mistake - sometimes all it takes is someone to point the obvious and it makes it so much easier to find it!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question