Decimal problems in the SQL string

Question

I have an error: every time the variable c.Value_do_title passes the SQL string, automatically the "." that separates the Decimal is converted to ",".

As you can see in this image, the variable is using the "." normally.

However, the SQL string takes the dot and places a comma.

What can it be?

The variable is 80.90, but when it is placed inside the sql string it becomes 80.90.

As the number is going with comma, Sql interprets as part of the error syntax:

Code:

public static void salvar(Mov_lancamento c, Int64 user)
{
    String sql;

    sql = "SELECT id_lancamento FROM mov_lancamento WHERE favorecido = '"+c.Favorecido+"' AND valor_do_titulo = "+ c.Valor_do_titulo + " AND data_vencimento = '"+c.Data_vencimento+"'";

    SqlDataReader dtr = Suporte.ConexaoBanco.selecionar(sql);

    if(dtr.Read())
        Id_lancamento = (Int64)dtr["id_lancamento"];
    else Id_lancamento = 0;

    dtr.Close();

    // Se tem 'id' igual a zero é porque ainda não foi inserido
    if (Id_lancamento == 0)
    {
        sql = "INSERT INTO mov_lancamento VALUES ('" + c.Favorecido + "', '" + c.Data_lancamento + "', '" + c.Data_vencimento + "', '" + c.Documento + "', " + Convert.ToDecimal(c.Valor_do_titulo.ToString().Replace(".", ",")) + ", " + c.Valor_pago + ", " + c.Acrecimo_valor + "," + c.Descontos_valor + "," + c.Saldo_a_pagar + ", " + c.Pago + ", '" + c.Data_pagamento + "', " + c.Excluido + ")";
        Suporte.ConexaoBanco.executar(sql);
    }
    else // Senão apenas atualiza
    {
        sql = "UPDATE mov_lancamento SET favorecido = '" + c.Favorecido + "', data_lancamento ='" + c.Data_lancamento + "', data_vencimento = '" + c.Data_vencimento + "', tipo_documento = " + c.Documento + ", " + Convert.ToDecimal(c.Valor_do_titulo.ToString().Replace(".", ","))  + ", pago = " + c.Pago + ", data_pagamento = '" + c.Data_pagamento + "' , excluido = 0 WHERE id_lancamento =" + c.Id_lancamento;
        Suporte.ConexaoBanco.executar(sql);
    }
}

Answer 1

What is happening is that the system has a globalization configuration, by default is being used the culture of your server that is probably en-BR (Portuguese-Brazil). What you can do is change this setting to the American standard, so it will stay in the desired format.

System.Globalization.CultureInfo.DefaultThreadCurrentCulture = new System.Globalization.CultureInfo("en-US");

If you do not want to modify these settings you can replace your variable that contains the value.

//Esse N2 configura a quantidade de casas decimais serão formatadas na sua variável.
//N2: duas casas decimais
//N3: três casas decimais
//N4: quatro casas decimais
//...
c.Valor_do_titulo.ToString("N2").Replace(",", ".");

Answer 2

If you do not want the dot to be converted to comma, you can make use of the Numberformatinfo, setting the property Numberdecimalseparator.

        var valor = 100.5M; //decimal
        var formatter = new NumberFormatInfo
        {
            NumberDecimalSeparator = ","
        };

        var comVirgula = valor.ToString(formatter); // resultado : 100,5        
        formatter.NumberDecimalSeparator = ".";
        var comPonto = valor.ToString(formatter); // resultado : 100.5

Answer 3

To avoid conversion issues, mainly in numerical or date variables, setting parameters from the SqlCommand is recommended.

Example taken from Microsoft’s Developer Network:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@ID", SqlDbType.Int);
    command.Parameters["@ID"].Value = customerID;

    // Use AddWithValue to assign Demographics.
    // SQL Server will implicitly convert strings into XML.
    command.Parameters.AddWithValue("@demographics", demoXml);

    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("RowsAffected: {0}", rowsAffected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}