Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    7

    Unanswered: SQL & PHP question

    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.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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:
    Code:
    -- 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
    Thanks,

    Matt

  3. #3
    Join Date
    Jan 2002
    Posts
    7

    here ya go

    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.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Re: here ya go

    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.).
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •