Insert problems in Asp classico (replace)

Question

Guys, in my database, the column is set to numeric (18,2)

then I have a field in a form that the person type the value and it goes to my table.

the insert is like this:

entrada = Replace(Request.form("entrada"),",",".") 

only if the person type the value with semicolon, for example "100.328,74" he makes the following mistake:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Not possible convert a char value into money. The char value syntax is incorrect."

if she only type the dot: "100.328" the value turns 100,33 (display on the website with formatnumber"

if the person enters only the comma: "100,32" value goes straight to database "100.32" and appears "100,32" on the website, according to the formatnumber

any idea?

I have no way to indoctrinate everyone who will insert the value not to use the point that separates the thousand/million

I would like to program to already go the right value.

thank you!

Answer 1

This code will always return the value in the format accepted by the database. For example:

100.328,74 will be 100328.74

100,32 or 100.32 will be 100.32

100,3 will be 100.3

In short, if there are pennies the code separates the pennies from the principal value and joins the two again separated by a dot, which is the accepted format for monetary values. If there are no pennies, the value remains the same, just removing the points that separate the thousands:

100.328 will be 100328

Code:

entrada = Request.form("entrada")

Set regEx = New RegExp
regEx.Pattern = "[.,]\d{1,2}$"
Set RegExRes = regEx.Execute(entrada)
Set regEx = Nothing

If RegExRes.Count > 0 Then

   centavos = RegExRes(0)
   centslen = len(centavos)
   centavos = replace(replace(centavos, ",", ""), ".", "")
   valor = replace(left(entrada, len(entrada)-centslen), ".", "")

   entrada = valor&"."&centavos

Else

   entrada = replace(entrada, ".", "")

End If

Answer 2

In this case you just need to first remove the dots, and then replace the commas by dots. Ex:

entrada = Replace(Replace(Request.form("entrada"),".",""),",",".")