locked
Dot and comma problem

    Question

  • Hi,

    Currently I developed a C sharp windows form application which is installed in both German and English windows operating system. The problem is the calculation part and storing it in MySql.

    The German windows operating system uses "," as a separator (for e.g 2,53) where as English operating system uses dot as separator (for e.g 2.53). The MySQL accepts only dot separated numbers.


    I used culture function to convert comma to dot. But some people who are used to English are using dot as separator in German windows operating system which is not working (for e.g. if he gives 2.53 the result is displaying 253) the same with the other way if a German guy working on English operating system and enter number with comma it is doing the same as in the example above.


    IS there a way where I can make it a common thing without any clashes which ultimately stores in my sql even though if he uses dot or comma in any of the windows operating system.
    Friday, December 03, 2010 1:48 PM

Answers

  • you can use the Contains string method. Convert the value into a string and do a comparation of string contains dot or comma. If you have eng. windows if the comma is found use Replace string method to change it. And contrary for the ger. windows system.

    Or you can use the string format overload that tkaes a CultureInfo object. The list of using the correct cultureInfo you get it here.

    Friday, December 03, 2010 2:12 PM
  • If you ignore cultural on the input side you should be able to get just what they enter and deal with it from there. You should not be constrained by that unless you have specified to use it.
    Devlin Liles If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, December 04, 2010 4:04 PM
  • Not sure how MySQL works but this is not how it works with other DBMS I worked with.

    Precisely the idea is that converting a data to a string usable in a SQL Statement is NOT something you should be concerned with. Usually your provide the data using its ACTUAL datatype (that is a decimal, string, date, boolean or whatever it is).

    This is the provider that will convert this typed value to whatever is understood by the server...

    So the overall scheme is :
    - user entered value in a textbox (USER)
    - the code converts this to a typed value (date, floating point value or whatever) according to the user culture (YOUR CODE)
    - this typed value is passed to the provider using a parametized query (YOUR CODE)
    - and the value is transmitted to the server without any culture concern (DONE FOR YOU BY THE PROVIDER)

    Or is this something particular to how the MySQL provider works ? Unfortunately I don't have mySQL handy to check this.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, December 08, 2010 2:07 PM

All replies

  • you can use the Contains string method. Convert the value into a string and do a comparation of string contains dot or comma. If you have eng. windows if the comma is found use Replace string method to change it. And contrary for the ger. windows system.

    Or you can use the string format overload that tkaes a CultureInfo object. The list of using the correct cultureInfo you get it here.

    Friday, December 03, 2010 2:12 PM
  • Hi,

     

    Yes I used both of the methods but they are limited to any one either eng or german windows. The main thing is that the program should identify which language settings it installed, I mean to identify either it is a german or eng window.

     

     

    Note : But some clinet computers don't have permission to acess some parts of the control panel in order to identify which window it has been installed.

    Friday, December 03, 2010 6:02 PM
  • This is an example how to get the OS language:

     

    private void DetectLanguage()
      {
       //example of string:
       decimal decValue = 2.53M;
       string value = decValue.ToString();
       string strCurCulture = System.Globalization.CultureInfo.CurrentCulture.ToString();
       if (strCurCulture == "de-DE")
       {
        if (value.Contains("."))
         value = value.Replace(".", ",");
       }
       else if (strCurCulture == "en-GB")
       {
        if (value.Contains(","))
         value = value.Replace(",", ".");
       }
       else if (strCurCulture == "sl-SI")
       {
        if (value.Contains(","))
         value = value.Replace(",", ".");
       }
       decValue = Convert.ToDecimal(value);
      }
    

     

    but the problem remains, even in my country if I want to convert from string 2.53 to decimal  I get 253 - dot is not recognized. But you can try using it anyway. This is a tough one.

    Will try some more...

    back later

    Mitja

    Friday, December 03, 2010 6:39 PM
  • Hi,

    If supported by the MySQL provider it is much better to use placeholders (@Name for SQL Server, ? for Access for example). For example http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html

    This way you work with the native datatype and this is done for you. You have a similar problem for strings (doubling quotes), dates (the culture doesn't write date as SQL wants them). Using parameters suppress those issues among other benefits...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Friday, December 03, 2010 7:13 PM
  • This is one of those things that you have to force users to work with the culture they are in.

    I wouldn't open a calculator and type 2,53 + 4,47 and expect to get 7,00.  And in Germany they wouldn't enter 2.53 + 4.47 and expect to get 7.00.


    public enum Answers { Everything = 0x2A }
    Friday, December 03, 2010 9:06 PM
  • Can you handle this by simply replacing the comma with a period without using cultural info? Sanitize the input to what the database expects right before you use it in the database? When you read it back out and put it to the screen use the cultural info to convert it but on the input side you shouldn't care right?

    I mean like this example?

     

     public void TestMethod1()
            {
                List<string> strings = new List<string>()
                                           {
                                               "2.57",
                                               "2,57"
                                           };
                List<string> databaseStrings = new List<string>();
                foreach (var item in strings)
                {
                    if(item.Contains(","))
                    {
                        var newItem = item;
                        newItem.Replace(",", ".");
                        databaseStrings.Add(newItem);
                    }
                    else
                    {
                        databaseStrings.Add(item);
                    }
                }
                
            }
    Devlin Liles If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, December 04, 2010 4:18 AM
  • Thanks a lot.

     

    @Mitja : thanks a lot I will try that.

     

    @ Capscdev : I alreay tried that but some people who are used to English are using dot as separator in German windows operating system which is not working (for e.g. if he gives 2.53 the result is displaying 253) the same with the other way if a German guy working on English operating system and enter number with comma it is doing the same as in the example above.

     

    Saturday, December 04, 2010 1:40 PM
  • If you ignore cultural on the input side you should be able to get just what they enter and deal with it from there. You should not be constrained by that unless you have specified to use it.
    Devlin Liles If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, December 04, 2010 4:04 PM
  • According to http://weblogs.asp.net/cumpsd/archive/2004/04/05/107456.aspx it uses the ? placeholder. Then you just provide a typed value (that is converted from text input fields using the current user culture) and this typed value is automatically used by the query without having to deal with a SQL suitable conversion...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Monday, December 06, 2010 9:17 AM
  •  

    Hi  Yams23,

     

    Welcome to MSDN Forum!

     

    I think we don't need to care about the culture, we just need to replace the ',' to '.' if there's a ',' in the string, otherwise just return the original value directly.

    string value = this.textBox1.Text;
    string result = value.Contains(',') ? value.Replace(',', '.') : value;
    MessageBox.Show(result);
    
    

    Place show us a response to let us make sure if your problem had been solved.

    And if there's anything I had misunderstood, please feel free to point out to let us also on the right direction to help you clear about this problem.

     

    Have a nice day!

    Mike

    ----------------

    If(helpful) -> Mark


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!
    Wednesday, December 08, 2010 6:35 AM
    Moderator
  • IMHO this is a bad idea. AFAIK in English , is the thousand separator so one could type 2,000 for 2000 rather than for 2.000.

    IMO it would be better to forget about having to convert value to a string representation suitable for use in a SQL statement. Using the ? placeholder and providing a typed value (converted from the user input using his culture) will provide that.

    Keeping doing so would raise similar issues for dates or even strings (not sure about MySQL but the typical case is O'Brian where you have to double the '). Rather than doing this by hand, it would be better to use ? placeholders (in addition it avoids SQL injection attacks).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, December 08, 2010 9:15 AM
  • The problem with that is that once the cultural info is evaluated and forwarded to the database the DBMS has to also understand that cultural info MySQL does not support the , decimal separator. If you were to do this in SQL Server it would work, unfortunately that is not the case here. The best bet would be to ignore culture on the way in removing all commas but the last one and replacing it with a decimal. The on the output side allow cultural info to evaluate to make the number more comfortable for the users.

    Devlin Liles http://twitter.com/devlinliles http://www.devlinliles.com/ If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, December 08, 2010 1:21 PM
  • Not sure how MySQL works but this is not how it works with other DBMS I worked with.

    Precisely the idea is that converting a data to a string usable in a SQL Statement is NOT something you should be concerned with. Usually your provide the data using its ACTUAL datatype (that is a decimal, string, date, boolean or whatever it is).

    This is the provider that will convert this typed value to whatever is understood by the server...

    So the overall scheme is :
    - user entered value in a textbox (USER)
    - the code converts this to a typed value (date, floating point value or whatever) according to the user culture (YOUR CODE)
    - this typed value is passed to the provider using a parametized query (YOUR CODE)
    - and the value is transmitted to the server without any culture concern (DONE FOR YOU BY THE PROVIDER)

    Or is this something particular to how the MySQL provider works ? Unfortunately I don't have mySQL handy to check this.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, December 08, 2010 2:07 PM
  • Hi  Patrice Scribe,

     

    I think you idea is ok. And thanks for your response.

    Since I assume that the application not accept the user use a ',' as a thousand separator, so I gave out the above method.

    As far as I know, one application would define some input rules to limit the users' input information, otherwise we need do lots of conversion and judgment or switch to let a user's input information convert to a meaningful information, even the user just input a meaningless information but meaningful in another situation, the application also would try to convert that information, which maybe is not our expected.

     

    Have a nice day!

    Mike

    ----------------

    If(helpful) -> Mark


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!
    Thursday, December 09, 2010 2:07 AM
    Moderator