Problem to send values of type 11.50 to SQL Server database

Asked

Viewed 116 times

-2

I have a problem formatting values type R$ when sending to the data bank 11.50 in the data bank is type 1150.

This condition is from the send button to 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);
        }
    }
c# .net sql-server typing type-conversion
  • 1

    Try trading the comma for a point before sending to the bank

  • 2

    Please share the snippet of code that Voce is using, what types are you using, double, real, etc., usually like Matheus said point by comma, but you have to see how your code is to avoid replacing and doing it the right way.

  • This? https://answall.com/q/81237/101. Of course, the solution may be different, but if you have 1150, and you want 11.50, you only have to divide by 100. Now, if the problem is that you’re getting the dice wrong maybe it’s more interesting to fix it.

  • from a glance there I just updated the information above

  • i changed the type in the money database to Numeric(15.2) so now when uploader appears an error message: Date Confailed version [OLE DB status value (if-know) = 0 ]

  • now I have changed the type of data bank Numeric(15.2) to int, data send and not error, so value 11.50 it stands as 115

  • Do not change anything for the whole, if the data has the corresponding type in the database do the way it has to be done, already caught there a number break, that will generate a big problem, care. Put in the decimal or corresponding database and do the conversion in the code and ready, missed tell which is your database and your code by the update has big problems, one of them is not using Parameters ...

  • @Raphaelshembek Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already done so. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site

Show 3 more comments

2 answers

1

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

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

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

This code is extremely insecure. That’s kind of how you do it.

The way you’re recording can violate the atomicity and consistency of the database, I just can’t guarantee it because I don’t know the requirements.

Whenever you capture Exception, except when you’re very experienced, you’re looking for a magic solution to the problems. Choose which exception to treat and make a proper treatment, what has been done is not helpful.

The right kind is Money same. And you need to enter the type in the code, something like that:

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

I put in the Github for future reference.

There are some things that don’t seem to make sense, but you’ll know... For example SubItems seems odd to me.

There are other problems that don’t make the code go wrong, but that’s not how you do it.

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

-1

Leave the field on the bench as Numeric(18.2) and try so:

Note: I’m not with C# here to test, maybe you need to change something there in the 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);
        }
    }

Browser other questions tagged c# .net sql-server typing type-conversion

You are not signed in. Login or sign up in order to post.