Problem to send values of type 11.50 to SQL Server database

-2

I have a formatting problem of values of type R $ when I send to the database 11.50 in the database it is of type 1150.

This is the push button of the submit button for the database has a database management class cl_GestorBD

private void button1_Click(object sender, EventArgs e)
        {
            cl_GestorBD gestor = new cl_GestorBD(true);
            int id_produto = gestor.ID_DISPONIVEL("compras", "id_compra");
            List<cl_GestorBD.SQLParametro> parametro = new List<cl_GestorBD.SQLParametro>();
            parametro.Add(new cl_GestorBD.SQLParametro("@atualizacao", DateTime.Now));

        string PRODUTO;
        int QTY;
        decimal VALOR_UNID;
        decimal VALOR_TOTAL;

        try
        {
            foreach (ListViewItem caixa in listCaixa.Items)
            {

                PRODUTO = caixa.SubItems[0].Text;
                QTY = Convert.ToInt32(caixa.SubItems[1].Text);
                VALOR_UNID = Convert.ToDecimal(caixa.SubItems[2].Text);
                VALOR_TOTAL = Convert.ToDecimal(caixa.SubItems[3].Text);

                query = "INSERT INTO compras VALUES ('" + id_produto + "','" + lblID.Text + "', '" + PRODUTO + "', '" + QTY + "', '" + VALOR_UNID + "', '" + VALOR_TOTAL + "',@atualizacao)";
                gestor.EXE_NON_QUERY(query, parametro);
            }

            MessageBox.Show("Registrado com sucesso!");
        }
        catch (Exception erro)
        {
            MessageBox.Show("ERRO " + erro.Message);
        }
    }
    
asked by anonymous 06.06.2018 / 15:50

2 answers

1

Not to mention variable names that do not follow recommended style , the code has some problems:

Do not convert data that you are not sure is correct. Convert will generate an error in the application when this occurs. Test whether the data is correct with TryParse() .

  

query="INSERT INTO purchases VALUES ('" + product_id + "', '" + lblID.Text + "', '" + PRODUCT + "', '" + QTY + "", "" + VALUE_TOTAL + "', @ update)";

This code is extremely insecure. It's more or less the way it's done .

The way you are writing can violate the atomicity and consistency of the database, I just can not guarantee it because I do not know the requirements.

Whenever you capture Exception , except when you are very experienced, you are looking for a magic solution to your problems . Choose which exception to treat and make an appropriate treatment, what was done is not helpful.

The correct type is Money itself. And you need to indicate the type in the code, something like this:

new SqlParameter("@Valor", SqlDbType.Money)

There are some things that do not seem to make sense, but you will know ... For example SubItems seems weird to me.

There are other problems that do not make the code go wrong, but that is not the way to do it.

There may be some other problem that is not clear in the code, even in the text entry.

    
06.06.2018 / 16:44
-1

Leave the field in the database as numeric (18,2) and try:

Note: I do not have C # here to test, maybe you need to change some thing there in formatting

private void button1_Click(object sender, EventArgs e)
        {
            cl_GestorBD gestor = new cl_GestorBD(true);
            int id_produto = gestor.ID_DISPONIVEL("compras", "id_compra");
            List<cl_GestorBD.SQLParametro> parametro = new List<cl_GestorBD.SQLParametro>();
            parametro.Add(new cl_GestorBD.SQLParametro("@atualizacao", DateTime.Now));

        string PRODUTO;
        int QTY;
        string VALOR_UNID;
        string VALOR_TOTAL;

        try
        {
            foreach (ListViewItem caixa in listCaixa.Items)
            {

                PRODUTO = caixa.SubItems[0].Text;
                QTY = Convert.ToInt32(caixa.SubItems[1].Text);
                VALOR_UNID = caixa.SubItems[2].Text.ToString("N2"); /*ou ToString("0.##");*/
                VALOR_TOTAL = caixa.SubItems[3].Text.ToString("N2");

                query = "INSERT INTO compras VALUES ('" + id_produto + "','" + lblID.Text + "', '" + PRODUTO + "', '" + QTY + "', '" + VALOR_UNID + "', '" + VALOR_TOTAL + "',@atualizacao)";
                gestor.EXE_NON_QUERY(query, parametro);
            }

            MessageBox.Show("Registrado com sucesso!");
        }
        catch (Exception erro)
        {
            MessageBox.Show("ERRO " + erro.Message);
        }
    }
    
06.06.2018 / 16:18