Incorrect syntax near the keyword 'Convert'.

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!
Thanks,
Andrew

AhelblingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

djwillmsCommented:
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.
0
AhelblingAuthor Commented:
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.
0
AhelblingAuthor Commented:
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.

Thanks!
0
CAVccCommented:
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.

Syntax
FormatCurrency(Expression[,NumDigAfterDec[,
IncLeadingDig[,UseParForNegNum[,GroupDig]]]])

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
document.write(FormatCurrency(20000))Output:$20,000.00

Example 2
document.write(FormatCurrency(20000.578,2))Output:$20,000.58

Example 3
document.write(FormatCurrency(20000.578,2,,,0))Output:$20000.58


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.