save money Textbox1.txt in MySQL Table

[dre-logics]

I use Visual Basic 2008 + MySQL Database
I have create a textbox1.text
MySQL Table Test with Two Columns:

Column Name DataType
RecordID INT(11)
Money Decimal(10,2)

TextBox1.Text = "€ 22,07"

I want the contents of field textbox1.txt save it into money in the column of the Test table!

I want to use the next MySQL Statement:

Insert INTO Test (money) VALUES ('" & textbox1.text & "')

Therefore I must translate content textbox1.txt into decimal format.
I hope that within the MySQL INSERT INTO statement can!!!

How can I do that the fastest way

Thanks for your help,

Regards,

Andre

[lolafuertes]

Using single quotes arround the content of the textbox1.text implies htat MySql should treat it as a text.

Without the single quotes will assume that it is a number so it will try to put the value into the destination field.

In your example, "€ 22,07" is not a number or decimal value acceptable because the currency symbol. Also the decimal point separator maybe unacceptable by MySql because the comma represents the field separator.

Also be sure to 'remove' any thousands separator before sending the number to MySql.

Hope this helps

[dre-logics]

This information I knew already (but thanks anyway)
But my main question how do I convert this data into the correct format.
And of course the fastest way

[lolafuertes]

The fastest way is to not allow the user to enter any unacceptable character in the text field.

You can do that with several methods:
a) Use a masked text field to enter the right value (IE: 99999,99) .
b) Prevent the user to enter invalid characters, capturing, evaluating and discarding those not acceptable.
To do so, you must enable the form to 'preview' the typed Keys by setting the KeyPreview to true in the forms properties.
Then capture the textbox KeyPress event and set the handled property of the System.Windows.Forms.KeyPressEventArgs to true, so the system will ignore this char and prevent it to be entrered in the text box. IE:

Select Case e.KeyChar
    Case "1"c, "2"c, ...., "9"c, System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator.Chars(0)  ' Instead of accepting the comma you can select to accept the decimal separator char defined on the System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator for multilanguage purposes
        IF textBox1.txt.IndexOf(System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator)<>-1 then  ' Only one comma will be accepted.
            e.Handled = True
        end if
    Case Else
        e.Handled = True
End select

Any way, once you have the right TextBox1.Text, you must revamp you SQL sentence like

"Insert INTO Test (money) VALUES (" & TextBox1.Text.Replace(System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator, "."c) & ")"

This way you can be confident that the decimal separator sent to MySQL is the ".".

Hope this helps

[dre-logics]

Thanks, this works fine!