SQL & PHP question [Archive]

xypno

01-09-02, 10:35

Warning: MS SQL message: Disallowed implicit conversion from data type varchar to data type money, table 'test.dbo.test', column 'Yearly Income'. Use the CONVERT function to run this query. (severity 16) in c:\program files\apache group\apache\htdocs\3.php on line 14

In the php board the moderator already told me that CONVERT is a SQL Function that is used IN SQL. Well, question then, How do I use it correctly?

I want to be able to add a numeric value in an html form and have it added to the database in SQL. However I'd get the above message that i cannot go from varchar to money. So how would I go about using the CONVERT function, I've been looking around but all the examples I've seen are too confusing and use it in a different context. Please help.. Thanks.

MattR

01-09-02, 22:10

What is your SQL statement?

In MS SQL, you do not enclose a money value in quotes, nor can you have anything other than your money indicator ($ for example) and your separators (with use of CAST).

As an example:

-- The following three INSERT statements work.
INSERT INTO TestMoney VALUES ($123.45)
GO
INSERT INTO TestMoney VALUES ($123123.45)
GO
INSERT INTO TestMoney VALUES (CAST('$444,123.45' AS MONEY) )
GO

-- This INSERT statement gets an error because of the comma
-- separator in the money string.
INSERT INTO TestMoney VALUES ($555,123.45)
GO

Just remember this URL:
http://msdn.microsoft.com/sqlserver

xypno

01-10-02, 11:49

first i'd like to thank you, You're actually the only one who's been nice enough to help me get started anyways here is the statement

$sql = "INSERT INTO employees (first,last,address,position,[yearly income]) VALUES ('$first','$last','$address','$position','$yearly income')";

towards the bottom of the html page i have

<form method="post" action="<?php echo $PHP_SELF?>">

First name:<input type="Text" name="first"><br>

Last name:<input type="Text" name="last"><br>

Address:<input type="Text" name="address"><br>

Position:<input type="Text" name="position"><br>

Yearly Income: <input type ="text" name = "[yearly income]"><br>
<input type="Submit" name="submit" value="Enter information">

</form>

I found out how to use the convert function, so i did

Etc. (whatever was before this up there) VALUES ('$first','$last','$address','$position',CONVERT(m oney,'$yearly income'))";

However, even that still didnt work. A couple of people gave me some ideas but didnt work.

MattR

01-10-02, 11:54

Originally posted by xypno
first i'd like to thank you, You're actually the only one who's been nice enough to help me get started anyways here is the statement

$sql = "INSERT INTO employees (first,last,address,position,[yearly income]) VALUES ('$first','$last','$address','$position','$yearly income')";

That SQL will work if you remove the single quotes around $yearly_income, e.g.
'$position', $yearly_income )

Etc. (whatever was before this up there) VALUES ('$first','$last','$address','$position',CONVERT(m oney,'$yearly income'))";

PHP does not have variables with spaces in them. $yearly income is not a valid variable, it will send the following text to MS SQL:
'' income, or:
insert into .. 'income'.

So, change your form to be name = $yearly_income (notice the underscore), and your INSERT to be $yearly_income.

In MS SQL (and all RDBMS') do not enclose numeric (e.g. numbers, integers, money, etc.) values in 'quotes'. Only use that for character data (varchar, char, text, etc.).