This code allows you to format a textbox string to the correct currency in SQL Server, independent of regional settings or the user input decimal symbol:
Public Function FormatMoney(ByVal strValue As String) As String '**This function fails if the user uses a digit grouping symbol and no decimal symbol. '** Get the decimal symbol from the system Dim DecS As String = Mid$(CStr(1.1), 2, 1) '** Find the location of the first dot Dim intDot As Integer = InStr(strValue, ".") '** Find the location of the first comma Dim intComma As Integer = InStr(strValue, ",") '** Select your regional decimal sign Select Case DecS Case "," '** Check to see if a dot or comma was used by the user If intDot > intComma Then '** make sure only the decimal sign remains strValue = Replace(strValue, ",", "") strValue = Replace(strValue, ".", ",") End If '** round the value to 2 decimals strValue = Decimal.Round(strValue, 2) '** prepare the value for SQL server strValue = Replace(strValue, ",", ".") Case "." If intComma > intDot Then '** make sure only the decimal sign remains strValue = Replace(strValue, ".", "") strValue = Replace(strValue, ",", ".") End If '** round the value to 2 decimals strValue = Decimal.Round(strValue, 2) End Select Return strValue End Function
