Is it possible to change the default decimal separator in float.ToString()?

Question

I need to send data in string format to mysql. By default vb.net interprets 0.5 as 0,5 which MySql won't accept. I know I could write floatval.tostring.replace(",", ".") to make it fit but I was wondering if it was possible to make it more comfortable so that an implicit conversion from float to string would produce a dot instead of a comma?

EDIT: per request, current code

Public Sub InsertInto(Values As IEnumerable(Of String))
    Dim ValStr As String = ""
    For Each V In Values
        ValStr &= "'" & V & "',"
    Next
    Dim Command = New MySqlCommand("INSERT INTO " & Table & " VALUES (" & ValStr.Substring(0, ValStr.Length - 1) & ");", Connection)
    Command.ExecuteNonQuery()
End Sub

this method is a part of a mysql connection wrapper and the properties "Connection" and "Table" are preassigned.

My test code calls the function as follows:

dimdum.InsertInto({"DEFAULT", (0.5).ToString.Replace(",", "."), "here is text"})

the test table columns are auto iterating int as primary key, a float and a varchar

You should not be using `floatval.ToString()` at all when generating SQL statements. If you use parameterized SQL statements, the handling of the formatting is done by the libraries and you don't need to worry about it. Please show your code and we can advise/improve on it. — Bernd Linde, Sep 04 '15 at 20:49
"By default vb.net interprets 0.5 as 0,5" I am afraid that this is a just-in-your-computer situation. VB.NET takes the decimal separator in the given culture (by default in the computer, unless you specify one for the given thread/calculations); in your case, it might be "," but in quite a few other computers it is ".". — varocarbas, Sep 04 '15 at 20:52
@BerndLinde hum I have no idea what you're talking about, I looked at the samples provided by the connector library and figured thats the way to do it — user81993, Sep 04 '15 at 20:58
I am afraid to say, but you are in very dire situation. You should never code sql commands like this. [Look at this funny explanation](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) of Sql Injection — Steve, Sep 04 '15 at 20:59
@Steve Yes I'm aware of the security concerns, I was planning to create a filter for any user input but are you suggesting there is a better way? — user81993, Sep 04 '15 at 21:01
Something for you to read up on: [Using Parameterized queries](http://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx) — Bernd Linde, Sep 04 '15 at 21:04
You're “aware of the security concerns” but you did it regardless? Worrisome. — Dour High Arch, Sep 04 '15 at 21:26
On the other hand, parameterised queries are the equivalent to strictly defining types. In VB.NET, Option Strict Off allows a not too strict assignation of types; it is certainly more dangerous (and not recommendable) but it is not the end of world if you know what you are doing. Parameterised queries (and SQL injection) are the same: if you do everything right (i.e., making sure that the strings to be included in the queries are OK), there will not be any problem. Although well... parameterised queries can also take care of these culture-related issues. — varocarbas, Sep 04 '15 at 21:54
@DourHighArch that method was to be used by all inserts, most of which originate from the system and are unaffected by user behavior, adding the filter there just seemed like unnecessary overhead if I were to filter user input before that method, whats worrisome about that? — user81993, Sep 04 '15 at 22:25

Steve · Accepted Answer · 2015-09-04T21:26:15.843

As I have saind in my comment above, I am afraid that you need to revise a lot of your code. As is you have a lot of problems, the worst is the Sql Injection that sooner or later you have to fix, but your try to convert everything in a string has also the drawback that the conversion of decimals, dates and other floating points values give more immediate troubles than the Sql Injection one.

There is only one way to get out and it is the use of parameterized queries. More code to write but after a while it is very straightforward.

So for example you should rewrite your code to something like this

Public Sub InsertInto(sqlText As String, Values As List(Of MySqlParameter))
    Using Connection = New MySqlConnection(... connectionstring here (or a global variable ....)
        Using Command = New MySqlCommand(sqlText, Connection)
            Connection.Open()
            If Values IsNot Nothing Then
                Command.Parameters.AddRange(values.ToArray)
            End If
            Command.ExecuteNonQuery()
        End Using
    End Using 
End Sub

and call it with this

Dim decValue As Decimal = 0.5
Dim strValue As String = "Test"
Dim dateValue As DateTime = DateTime.Today

Dim parameters = New List(Of MySqlParameter)()
parameters.Add(New MySqlParameter() With { .ParameterName = "@p1", 
                                           .DbType = MySqlDbType.Decimal,
                                           .Value = decValue})
parameters.Add(New MySqlParameter() With {.ParameterName = "@p2",
                                          .DbType = MySqlDbType.String,
                                          .Value = strValue})
parameters.Add(New MySqlParameter() With {.ParameterName = "@p3",
                                         .DbType = MySqlDbType.Date,
                                         .Value = dateValue})

InsertInto("INSERT INTO youTable VALUES(@p1, @p2, @p3)", parameters)

Note that now InserInto is just a simple routine that receives the command text and the parameters expected by the text, add them to the command, opens the connection, executes everything and exits closing the connection.

Note also that, with a parameterized queries, your sql command is totally void of the mess caused by single quotes for strings, formatting rules for dates and the handling of the decimal point is nowhere in sight

(A side note. This INSERT INTO text suppose that your table has exactly three fields and you supply the values for all of them, if you want to insert only a subset of fields then you need to pass them to the method as a third parameter )

score 1 · Answer 2 · answered Sep 04 '15 at 21:07

1

Specify CultureInfo:

Dim n As Single
Dim s As String
n = Math.PI
s = n.ToString("F2", New System.Globalization.CultureInfo("en-US"))

s will be "3.14", even if your computer is set for a different format.

answered Sep 04 '15 at 21:07

Ron

269
1
6

I think that this answer does not deserve -1. The question is: "Is it possible to change the default decimal separator in float.ToString()?". And here you have a valid (enough) answer for it. If, in this specific context, there is a better approach to deal with the underlying situation, it would be further up-voted (and ideally, Ron should have mentioned the parameterisation). In any case, this one adresses the original concern of the OP (as highlighted in the question) and, as such, is worthy. Useful for future readers wanting to know how to change the default decimal separator outside SQL. – varocarbas Sep 04 '15 at 21:55
1

@varocarbas Thanks. I frequently deal with data interchange between systems. It is usually over a serial port or a network connection. The punctuation has to parse at the far end, the customer is in control of the system settings, and parameterized queries are not an option. – Ron Sep 08 '15 at 19:34

Is it possible to change the default decimal separator in float.ToString()?

2 Answers2