WPF Textbox as Double or Float-CodePudding

In my application i have two textboxes that are used to set the price of an entries for children and adults. Everytime the textboxes are changed, the event "textbox_ValueChanged" fires which executes "priceChanged()" and saves the content of my textboxes to a MySQL Database. The columns that the prices are saved to are type double. This whole thing works fine for typing integers into "adultPriceTextbox" (for example), but when trying to write a double or float into the textbox there are the following cases:

1. -> User types "5" then "," and then "5"

In this case the program crashes as soon as the second "5" is typed. The line that crashes is cmd.ExecuteNonQuery(); which saves the value to the database (view code further below). The error message looks like this

Data truncated for column 'adults' at row 1"

2. -> User types "5" then "." and then "5"

In this case nothing crashes, but the value being saved to the database does not contain the dot. So "5.5" would turn into "55".

This is the code that saves the values to the database. It is inside of a class called "DatabaseInterface":

    public static void updatePrice(double _adults, double _children)
    {
        MySqlConnection cnn = OpenCnn();
        MySqlCommand cmd = new MySqlCommand("UPDATE price SET adults = '"   _adults   "', children = '"   _children   "';", cnn);
        cmd.ExecuteNonQuery();
    }

And this is the code that executes "UpdatePrice":

    private void priceChanged()
    {
        double adultPrice;
        double childPrice;

        try
        {
            adultPrice = Convert.ToDouble(adultPriceTextbox.Text);
        }
        catch
        {
            adultPrice = 0.0;
        }
        try
        {
            childPrice = Convert.ToDouble(childPriceTextbox.Text);
        }
        catch
        {
            childPrice = 0.0;
        }
        DatabaseInterface.updatePrice(adultPrice, childPrice);

    }

Note that in this special case, there are two input windows. One that sets the price for children and the other one for adults. Also my region is Germany, where decimals are written with "," instead of ".". What would be the most elegant solution to achieve a Textbox where the user can type in integers and floats / doubles?

Addition: Ideas for blocking any alphabetical input into said textboxes are welcome as well, only numbers and "." / "," should be allowed.

CodePudding user response：

Instead of binding to a text/string, just have a property on your binding object (MVVM pattern) that is the expected type of decimal, float or int for the respective type. Then, when you bind your text to that public get/set property, it will only store the value IF it qualifies for that data type, thus NOT applying the value to the property with bad values that cant be converted.

Additionally, instead of sending the data on every key stroke, you could change the binding to only submit after focus changes such as

<TextBox Text="{Binding YourIntegerField, UpdateSourceTrigger=LostFocus}" 
    Width="40" MaxLength="3" />


<TextBox Text="{Binding YourDecimalField, UpdateSourceTrigger=LostFocus}" 
    Width="70" MaxLength="6" />

The MaxLength is how many characters you actually allow. For a person's age, you probably cap at 3, but for pricing, your call.

Finally, by building out your strings with quoted values, you are open to SQL injection and should PARAMETERIZE your queries. Learn to do that early on, especially if dealing with actual text-based content and not numbers.

CodePudding user response：

To both cases, in the priceChanged ignores any not numeric characters, you can do as this, or using a ASCII table instead of regex, exclude the '.' or/and '.' from ignored characters. Doing the first suggestion, i think that the crashes from the first case will not happen again. For the second case, you the culture info as suggested Jaime and don't forget to change the ',' to '.' or the inverse before the conversion.