WPF Textbox as Double or Float

Question

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.

Answer 1

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.

MVVM pattern is (M)odel, (V)iew, (VM)ViewModel. The model is the data where all stuff comes from and goes to for the database. The view is all the presentation stuff to your end-users, hence the Textbox, and all other inputs, buttons, etc. The ViewModel is the glue that ties the pieces together. You can find plenty of reading out there.

Now, the view, you got. The model where the data resides also exists. Your view model is basically the object that allows the back-and-forth exposure. Since you are not showing your actual view (more context of the .xaml and .xaml.cs, harder to add more specifics. But, lets say that your .xaml.cs is directly paired with your .xaml. In the constructor, if you set the DataContext to your .xaml.cs (or other actual object), that is what is being "bound" to. So it might look like

namespace YourApp
{
   public partial class YourView
   {
      public YourView()
      {
         InitializeComponent();
         // HERE, you are basically telling the view that anything with "BINDING"
         // you want associated to this object.
         DataContext = this;
      }

      // Now, any publicly prepared properties such as
      // int, decimal, date, etc can be exposed as bindable in the view
      private int _youngAge = 7;
      public int YoungAge
      { get { return _youngAge; }
        set { _youngAge = value;
              DoSomethingOnceAssigned();
            }
      }

      private int _olderAge = 83;
      public int OlderAge 
      { get { return _olderAge; }
        set { _olderAge = value;
               DoSomethingWithOlderAge();
            }
      }

      // similar with any other properties
   }
}

Now, in this scenario, since I defaulted the young and old ages, if you run the view and have the bindings to these public properties (not the private), the form SHOULD show those ages respectively. Now, if you edit the details while running the form and change focus to the next field such as by click or tab, it should hit the respective setters which you can break-point on and debug with.

See if that helps you get going some and let me know if anything else to assist.

Answer 2

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.