Decimal problems in SQL string

Question

Decimal problems in SQL string

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (1 votes)

-1

I'm having an error: every time the variable c.Value_value passes the SQL string, automatically, the "." which separates the Decimal is converted to ",".

Asyoucanseeinthisimage,thevariableisusingthe"." usually.

However,theSQLstringtakesthepointandplacesacomma.

Whatcanitbe?

Thevarableis80.90,butwhenitisplacedinsidethesqlstringitbecomes80.90.

Becausethenumberiscomma-separated,SQlinterpretsaspartofthesyntaxgivingerror:

Code:

publicstaticvoidsalvar(Mov_lancamentoc,Int64user){Stringsql;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);
    }
}

sql sql-server c#

asked by anonymous 05.07.2018 / 16:34

3 answers

2

If you do not want the point to be converted to a comma, you can use NumberFormatInfo by setting the NumberDecimalSeparator property.

        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

05.07.2018 / 17:05

1

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

Example taken from the Developer Network of Microsoft :

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);
    }
}

05.07.2018 / 16:52

Configuration file for C # forms Winforms Refresh page automatically?

score 3 · Accepted Answer

What is happening is that the system has a globalization configuration, by default the culture of your server is probably being used (English-Portuguese). 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 perform a replace on 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(",", ".");