Decimal value conversion error in Insert C#

Question

I have an Insert that receives some parameters, more at the moment that mount the sql statement this giving 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 right thing would be to come like this:

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

Answer 1

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

This is usually not a problem for integers, but for numbers with decimals it is quite problematic, because in an OS in Portuguese it will use the comma as decimal separator, while in English it will use the dot.

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

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

And since all its values are numerical then you should not use the quotation marks in SQL.

Now just as an observation, the way you are doing it is pretty much the same as concatenating values into an SQL, which is not recommended, the ideal is to always use parameters in any SQL query, and the use of 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.

Answer 2

Since the fields are integer and Numeric(8,2), there is no need of quotation marks, try this format:

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