Error converting decimal value in Insert C #

Question

Error converting decimal value in Insert C #

Navigation

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

1

I have an insert that receives some parameters, but at the moment that the sql statement is giving this conversion error:

Error converting data type varchar to numeric.

        //http://localhost:7630/api/AspNetWebApi/cadastrar/jogo/4512/20.01/20.10/5
        [HttpPost]
        [Route("cadastrar/jogo/{nJogo}/{valor}/{total}/{idusuario}")]
        public HttpResponseMessage Cadastro(int nJogo, decimal valor, decimal total, int idusuario)
        {
            try
            {
                var tTabela = new JogoDetalheAplicacao();
                tTabela.Inseri(nJogo, valor,total,idusuario);
                return Request.CreateResponse(HttpStatusCode.OK, "Cadastro realizado.");
            }
            catch (Exception ex)
            {

                return Request.CreateResponse(HttpStatusCode.BadRequest, ex.Message);
            }
        }

    public void Inseri(int nJogo, decimal valor, decimal total, int idusuario)
    {
        var strQuery = "";
        strQuery += "INSERT INTO TB_JOGO_DETALHE_TEMP (NUMERO_JOGO, VALOR_JOGO,VALOR_TOTAL,IDUSUARIO)";
        strQuery += string.Format(" VALUES (");
        strQuery += string.Format(" {0}, ", nJogo);
        strQuery += string.Format(CultureInfo.InvariantCulture, " {0:0.00}, ", valor);
        strQuery += string.Format(CultureInfo.InvariantCulture, " {0:0.00}, ", total);
        strQuery += string.Format(" {0} ", idusuario);
        strQuery += string.Format(" ) ");


        using (contexto = new Contexto())
        {
            contexto.ExecutaComando(strQuery);
        }

    }

Result of the final sql query:

INSERT INTO TB_JOGO_DETALHE_TEMP (NUMERO_JOGO, VALOR_JOGO,VALOR_TOTAL,IDUSUARIO) VALUES ('4512','20,01','20,10','5' )

The correct thing would be to look like this:

INSERT INTO TB_JOGO_DETALHE_TEMP (NUMERO_JOGO, VALOR_JOGO,VALOR_TOTAL,IDUSUARIO) VALUES ('4512',20.01,20.10,'5' )

sql c# asp.net-mvc

asked by anonymous 06.01.2016 / 15:19

2 answers

2

Your problem is that string.Format uses information from the current culture to decide how to convert a number to string.

This is not usually a problem for integers, but for numbers with decimal places it is very problematic, because in a Portuguese OS it will use the comma as the decimal separator, whereas in English it will use the dot. / p>

If you are trying to build a SQL, this becomes a bigger problem, since in a SQL you need to always use dot as the decimal separator, fortunately this is very simple to solve since string.Format accepts IFormatProvider as first parameter, which it will use to define how to do the conversion, and conveniently CultureInfo implements this interface, so you can force it to use a specific CultureInfo , in this case it can be CultureInfo.InvariantCulture that always formats numbers with point as the decimal separator.

strQuery += string.Format(CultureInfo.InvariantCulture, " VALUES ({0},{1},{2},{3})", nJogo, valor, total, idusuario);

And since all of your values are numeric then you should not use quotation marks in SQL.

Now just as a remark, the way you're doing is pretty much the same as concatenating values in a SQL, which is not recommended, it's always best to use parameters in any SQL query, and using them would also avoid this error since you could pass the values already in the correct type to the database, without having to convert to string.

06.01.2016 / 17:09

Linear-gradient and compatibility with browsers Complementary error function in java?

score 1 · Accepted Answer

As the fields are integer and numeric (8,2), you do not need the quotation marks, Try this format :

strQuery += string.Format(CultureInfo.InvariantCulture," VALUES ({0},{1:0.00},{2:0.00},{3})", nJogo, valor, total, idusuario);