Convert varchar to money?

[ooiooipig]

Hi all, can anyone help me to solve this problem?? How to convert a data type varchar to data type money in an asp file?? By using a convert function, where should I insert the code?? How is the code being written??

Any help will kindly be appreciated!!
Thank You!!

[raf]

Here you go.

Why don't you folow our advice in your other thread (insert) ?


info from helpfile
---------------------------------
Description
Returns an expression that has been converted to a Variant of subtype Currency.
Syntax
CCur(expression)
The expression argument is any valid expression.

Remarks
In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CCur to force currency arithmetic in cases where integer arithmetic normally would occur.
You should use the CCur function to provide internationally aware conversions from any other data type to a Currency subtype. For example, different decimal separators and thousands separators are properly recognized depending on the locale setting of your system.

The following example uses the CCur function to convert an expression to a Currency:

Dim MyDouble, MyCurr
MyDouble = 543.214588 ' MyDouble is a Double.
MyCurr = CCur(MyDouble * 2) ' Convert result of MyDouble * 2 (1086.429176)
' to a Currency (1086.4292).

[david7777]

Have a look at my reply to your other thread as an altenative...

http://www.codingforums.com/showthread.php?s=&postid=83537#post83537

[ooiooipig]

Hi guys, all the previous problems have been solved... I've another problem, now in the table there's a column called "ReqNo". This is the reference number of the request. How can I generate the number out?? It is being set as the primary key already, however, I cant click on to the "identity", "identity seed" and "identity increment".

Please advice!

[twocool]

bro u have to ask chew mee.. that woman only set permissions one the table

[whammy]

Looks like you guys know each other. ;-)

[ooiooipig]

Hi all, I still cant resolve the conversion of data type varchar to money... here's my coding for your references:

<%
vreqType = Request("vreqType")
vReqtrEmail = Request("vReqtrEmail")
'vReqNo = Request("ReqNo")
vAppnId = Request("vAppnId")
vreqTitle = replace(trim(Request("vreqTitle")),"'","''")
vDesiredDt = month(Request("vDesiredDt")) & "/" & day(Request("vDesiredDt")) & "/" & year(Request("vDesiredDt"))
vReqtr = replace(trim(Request("vReqtr")),"'","''")
'date = Request("date()")
vContactNo = Request("vContactNo")
vDept = replace(trim(Request("vDept")),"'","''")
vRC = Request("vRC")
vPriority = Request("vPriority")
vfuncReq = server.URLencode(trim(Request("vfuncReq")))
vFreqVol = server.URLencode(trim(Request("vFreqVol")))
vSavings = Request("vSavings")
vRevenue = Request("vRevenue")
vBenefit = server.URLencode(trim(Request("vBenefit")))
vTester = replace(trim(Request("vTester")),"'","''")
vTesterContact = Request("vTesterContact")
vApprovOfficer = replace(trim(Request("vApprovOfficer")),"'","''")
vApprovEmail = Request("vApprovEmail")
vStatus = Request("vStatus")
%>

<%
sql="INSERT INTO Requests (ReqNo,ReqType,AppnId,reqTitle,ReqDt,DesiredDt,Reqtr,ReqtrEmail,ContactNo,Dept,RC,Priority,funcReq,F reqVol,Savings,Revenue,Benefit,Tester,TesterContact,ApprovOfficer,ApprovEmail,ReqStatus)"
sql=sql & " VALUES "
sql=sql & "('" & ReqNo & "',"
sql=sql & "'" & vreqType & "',"
sql=sql & "'" & vAppnId & "',"
sql=sql & "'" & vreqTitle & "',"
sql=sql & "'" & (ReqDt) & "',"
sql=sql & "'" & cdate(vDesiredDt) & "',"
sql=sql & "'" & vReqtr & "',"
sql=sql & "'" & vReqtrEmail & "',"
sql=sql & "'" & vContactNo & "',"
sql=sql & "'" & vDept & "',"
sql=sql & "'" & vRC & "',"
sql=sql & "'" & vPriority & "',"
sql=sql & "'" & vfuncReq & "',"
sql=sql & "'" & vFreqVol & "',"
sql=sql & "'" & ccur(vSavings) & "',"
sql=sql & "'" & ccur(vRevenue) & "',"
sql=sql & "'" & vBenefit & "',"
sql=sql & "'" & vTester & "',"
sql=sql & "'" & vTesterContact & "',"
sql=sql & "'" & vApprovOfficer & "',"
sql=sql & "'" & vApprovEmail & "',"
sql=sql & "'" & vStatus & "')"

MSCS.execute(sql)
%>

can anyone kindly advise?? thanks!!

[whammy]

I'm not sure what the problem is... you seem to be doing an INSERT - why would you need to convert datatypes doing an insert :confused:

[ooiooipig]

yes whammy, I'm doing an insert... but when I submit the form and before it can be inserted into the database, it gives me an error asking me to convert the data type from varchar to money using the convert function. I'm not sure what the error is about and can anyone tell me how to use a convert function for converting data type varchar to money?

thanks!! :confused:

[whammy]

What's the error you're getting? The best way to figure that out after you've noted the error message is to do this:


<%
sql="INSERT INTO Requests (ReqNo,ReqType,AppnId,reqTitle,ReqDt,DesiredDt,Req
tr,ReqtrEmail,ContactNo,Dept,RC,Priority,funcReq,F
reqVol,Savings,Revenue,Benefit,Tester,TesterContac
t,ApprovOfficer,ApprovEmail,ReqStatus)"
sql=sql & " VALUES "
sql=sql & "('" & ReqNo & "',"
sql=sql & "'" & vreqType & "',"
sql=sql & "'" & vAppnId & "',"
sql=sql & "'" & vreqTitle & "',"
sql=sql & "'" & (ReqDt) & "',"
sql=sql & "'" & cdate(vDesiredDt) & "',"
sql=sql & "'" & vReqtr & "',"
sql=sql & "'" & vReqtrEmail & "',"
sql=sql & "'" & vContactNo & "',"
sql=sql & "'" & vDept & "',"
sql=sql & "'" & vRC & "',"
sql=sql & "'" & vPriority & "',"
sql=sql & "'" & vfuncReq & "',"
sql=sql & "'" & vFreqVol & "',"
sql=sql & "'" & ccur(vSavings) & "',"
sql=sql & "'" & ccur(vRevenue) & "',"
sql=sql & "'" & vBenefit & "',"
sql=sql & "'" & vTester & "',"
sql=sql & "'" & vTesterContact & "',"
sql=sql & "'" & vApprovOfficer & "',"
sql=sql & "'" & vApprovEmail & "',"
sql=sql & "'" & vStatus & "')"
Response.Write(sql) : Response.End
' MSCS.execute(sql) ; this is commented out although the .end above kills everything ;o)
%>


If you are using Enterprise Manager, then you can paste the query you wrote to the page directly into it and parse/analyze it. ')

[whammy]

P.S. What is the datatype in your table?

[ooiooipig]

the datatype in the database is "money"

[whammy]

"money"? What kind of server are you using?!? I would think it would be a float...

[ooiooipig]

i'm using sql server... and i cant change the data type of the field as it's being set already...

[whammy]

You haven't fixed it yet? Your datatype in ASP/VBScript shouldn't matter _AT ALL_ to what the database's datatype is for that field - except for the fact that it has to fit into it (in other words, you don't want to input 15 characters into a 14 character field, or put letters in a float (or money?!?) datatype).

In other words, doing a type conversion on an ASP variable means nothing to SQL, as far as I know (and I haven't seen anything to disprove this at all - only the contrary). ALL of the things I input into a SQL statement as variables are usually considered as strings to ASP/VBScript, since that's the way that ASP treats form submissions. The difference is whether or not you put a single quote around it, really.

I might input an integer, a float, or whatnot - but I make sure I validate it using regular expressions before I allow the update/insert statement to run (to make sure that the input is of a type that the database expects, and to try and prevent SQL injection attacks!), and I make sure that I use single quotes around strings etc, and no quotes around integer types.

So I'm really confused as to what the error is you're getting, and whether you have followed my suggestions above to see what the problem is?

[whammy]

P.S. I notice a few statements in this thread like "Why didn't you follow my advice in this other thread?" etc.

Please exhaust the suggestions given (in other words, try them out before you post again!), or if you don't understand them, ask for clarification.

If this doesn't solve your problem I'm going to close this thread and ask that you start a new thread for each SPECIFIC problem you are having.

:D

[whammy]

P.P.S. From triply re-reading your posts, it sounds like you may have an actual ASP/VBScript problem instead... can you post the code? That's usually helpful.

[ooiooipig]

hi whammy, here's my code:

<%
vreqType = Request("vreqType")
vReqtrEmail = Request("vReqtrEmail")
vReqNo = Request("ReqNo")
vAppnId = Request("vAppnId")
vreqTitle = replace(trim(Request("vreqTitle")),"'","''")
vDesiredDt = month(Request("vDesiredDt")) & "/" & day(Request("vDesiredDt")) & "/" & year(Request("vDesiredDt"))
vReqtr = replace(trim(Request("vReqtr")),"'","''")
'date = Request("date()")
vContactNo = Request("vContactNo")
vDept = replace(trim(Request("vDept")),"'","''")
vRC = Request("vRC")
vPriority = Request("vPriority")
vfuncReq = server.URLencode(trim(Request("vfuncReq")))
vFreqVol = server.URLencode(trim(Request("vFreqVol")))
vSavings = Request("vSavings")
vRevenue = Request("vRevenue")
vBenefit = server.URLencode(trim(Request("vBenefit")))
vTester = replace(trim(Request("vTester")),"'","''")
vTesterContact = Request("vTesterContact")
vApprovOfficer = replace(trim(Request("vApprovOfficer")),"'","''")
vApprovEmail = Request("vApprovEmail")
vStatus = Request("vStatus")
%>

<%

'Format day
strDay = right("0" & cstr(day(date())),2)
'Format Month
strMonth = right("0" & cstr(month(date())),2)
'Format Request Date
ReqDt = strMonth & "/" & strDay & "/" & cstr(year(date()))
'Update Request
sql="EXEC UpdateRequest '" & vAppnId & "','" &_
vreqTitle & "'," & cdate(ReqDt) & "," & cdate(vDesiredDt) & ",'" &_
vReqtr & "','" &_
vReqtrEmail & "','" &_
vContactNo & "','" &_
vDept & "','" &_
vRC & "','" &_
vPriority & "','" &_
vfuncReq & "','" &_
vFreqVol & "'," & ccur(vSavings) & "," & ccur(vRevenue) & ",'" &_
vBenefit & "','" &_
vTester & "','" &_
vTesterContact & "','" &_
vApprovOfficer & "','" &_
vApprovEmail & "','" &_
vStatus & "'"
' response.write(sql)
' response.end

MSCS.execute(sql)

****************************************************

I've request the values to the action page where the update of the fields in the database is to be done. however, it prompts me an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '/'.

****************************************************

[whammy]

There you go, if you'd do the response.write(sql) : response.end I suggested long ago, you'd see the error with that big of a clue!!!

It's in your date variable... the only place where you use a "/". Probably you aren't receiving a value for one of the variables (one of the things I always test for before writing to a database).

:)

[ooiooipig]

hi all!! i've solved this problem... thanks alot for your help!! ;)

[david7777]

So share the sollution!! :D

[ooiooipig]

this part,

sql="EXEC UpdateRequest '" & vAppnId & "','" &_
vreqTitle & "'," & cdate(ReqDt) & "," & cdate(vDesiredDt) & ",'" &_
vReqtr & "','" &_
vReqtrEmail & "','" &_
vContactNo & "','" &_
vDept & "','" &_
vRC & "','" &_
vPriority & "','" &_
vfuncReq & "','" &_
vFreqVol & "'," & ccur(vSavings) & "," & ccur(vRevenue) & ",'" &_
vBenefit & "','" &_
vTester & "','" &_
vTesterContact & "','" &_
vApprovOfficer & "','" &_
vApprovEmail & "','" &_
vStatus & "'"

i pass in short of one value, that's why the values to be updated in the database is in the wrong field so it prompt me an error. instead of passing the DesiredDt to a datetime field (due to short of one value passed in) , it is being pass into a varchar field... that's why there's an error!!

;)