• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

Convert string from textbox into type money SQL Server 2005

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
jd1991
Asked:
jd1991
  • 4
  • 2
1 Solution
 
sammySeltzerCommented:
try this


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


0
 
jd1991Author Commented:
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
 
sammySeltzerCommented:
sorry, remove the first comma. should be:

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

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
jd1991Author Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
jd1991Author Commented:
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
 
jd1991Author Commented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now