Disallowed implicit conversion from data type varchar to data type money, table 'OPICForms.dbo.Applications', column 'TotalCost'. Use the CONVERT function to run this query.

Hello Everyone, I get the following error from an UPDATE statement from a variable of the datatype money called "TotalCost"

MY CODE:


            strSQL = "UPDATE Applications SET ProjCtry='" & fnNullIfEmpty(request.form("ProjCtry")) & "', "    'added fnNullIfEmpty(strThis)
            strSQL = strSQL & "Industry='" & fnNullIfEmpty(request.form("Industry")) & "', "   'added fnNullIfEmpty(strThis)
            strSQL = strSQL & "TotalCost=" & fnMakeBlank(request.form("TotalCost")) & ", "
            strSQL = strSQL & "OPICLoan=" & fnMakeBlank(request.form("OPICLoan")) & ", "
            strSQL = strSQL & "OPICName='" & fnNullIfEmpty(request.form("OPICName")) & "', "            
            strSQL = strSQL & "Description='" & fnNullIfEmpty(request.form("Description")) & "', "  'added fnNullIfEmpty(strThis)
            strSQL = strSQL & "New = " & fnNullIfEmpty(request.form("New")) & " "  'added fnNullIfEmpty(strThis)
            strSQL = strSQL & "WHERE PID=" & Session("UserID")
            'response.write(strSQL)
            objConn.Execute(strSQL)


ERROR:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'OPICForms.dbo.Applications', column 'TotalCost'. Use the CONVERT function to run this query.

Gemini532Asked:
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.

jmundsackCommented:
Does fnMakeBlank(request.form("TotalCost")) result in a currency value that includes a dollar sign or other currency identifier, or separating commas, etc., e.g., 2,300.23 instead of 2300.23?

The currency identified and comma, I think would cause the specified error.
0
Gemini532Author Commented:
THat is correct!  This is because this field can have a huge number, and they want to see it separated by commas, however, it's giving me an error during the UPDATE, how can I fix it without changing the databtype to varchar...  Or is there no way to leave the datatype as money?
It's money right now, but it's giving me an error....  Can you help?
0
Gemini532Author Commented:
Does this mean I have to change the datatype to varchar?
is this what the problem is here? If it contains commas, than it must be varchar?
0
jmundsackCommented:
Try this line for the total cost field:

            strSQL = strSQL & "TotalCost= CONVERT(money,'" & fnMakeBlank(request.form("TotalCost")) & "'), "

(Note the single-quote just after the word money, and the single-quote and end paren just prior to the final comma.)
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
Gemini532Author Commented:
Amazing, how did you know that?
hehe
Thank you!
0
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
ASP

From novice to tech pro — start learning today.