Incorrect syntax near the keyword 'Convert'.

Posted on 2004-11-12
Last Modified: 2008-01-09
This is the initial error i get

Microsoft OLE DB Provider for SQL Server error ‘80040e07’

Disallowed implicit conversion from data type varchar to data type money, table ‘Corpweb.dbo.GoldsNum’, column ‘Sales’.  Use the CONVERT function to run this query.

/goldsweb/NumberGen.asp, line 29

and when i try to use the convert function i get the error that i put in the title of this question.

this is my string:

<% strSQLConvert = "Convert(numeric,'" & strSales & "')" %>

Please HELP!

Question by:Ahelbling
    LVL 3

    Expert Comment

    You could rewrite your sql like this:

    select cast(sales as money) from Corpweb.dbo.GoldsNum

    or, you could physically change/alter the data type of the sales column to money, permenately in the tables schema.

    Author Comment

    okay but the data type in the table is already a money data type.
    The problem is when i try to write a text box value of 125.25 for example, it can't because it is trying to write text to a money field. I need to convert it to money first and then write it to the field.

    Author Comment

    so this is what i have tried and it isn't working either....

    <% strSQL = "INSERT INTO GoldsNum(BegDate, EndDate, EmpName, Store, EmpPosition, Sales, Members, Sessions, Commission)" & _
       "VALUES('" & strBegDATE & "','" & strEndDate & "','" & strEmpName & "','" & strStore & "','" & strEmpPosition & "',CONVERT(money,'" & strSales & "'),'" & strMembers & "','" & strSessions & "','" & strCommissions & "');" %>

    and the error i get is:

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Incorrect syntax near the keyword 'Convert'.

    /goldsweb/NumberGen.asp, line 31.

    LVL 3

    Accepted Solution

    have you tried the FormatCurrency function?

    The FormatCurrency function returns an expression formatted as a currency value using the currency symbol defined in the computer's control panel.


    Parameter Description
    expression Required. The expression to be formatted
    NumDigAfterDec Optional. Indicates how many places to the right of the decimal are displayed. Default is -1 (the computer's regional settings are used)
    IncLeadingDig Optional. Indicates whether or not a leading zero is displayed for fractional values:
    -2 = TristateUseDefault - Use the computer's regional settings
    -1 = TristateTrue - True
    0 = TristateFalse - False
    UseParForNegNum Optional. Indicates whether or not to place negative values within parentheses:
    -2 = TristateUseDefault - Use the computer's regional settings
    -1 = TristateTrue - True
    0 = TristateFalse - False
    GroupDig Optional. Indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings:
    -2 = TristateUseDefault - Use the computer's regional settings
    -1 = TristateTrue - True
    0 = TristateFalse - False

    Example 1

    Example 2

    Example 3


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Problem to adjust sheet 1 79
    canBalance challenge 34 58
    method argument as final 1 54
    wordlen challenge 3 32
    Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
    In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now