Json + C# web services - decimal numbers being save incorrectly

254 Views Asked by At

I'm creating an application using jQuery and consuming C# web services through json, everything works great, however I'm trying to insert records in a database, and everytime that I try to insert a decimal number (ie. 2.1) I get an error "500 undefined" and if I change the dot for comma (2,1) the number is saved as "21". This is the jquery code:

        //Function to add a report line
        function addReportLine()
        {
            var productVal  = $("#productCode").val();
            var nameVal     = $("#productName").val();
            var unitVal     = $("#unitPrice").val(); //This is the decimal number
            var qtyVal      = $("#qty").val();  
            var totalVal    = $("#totalPrice").val();                           
            var whVal       = "";                       
            var userVal     = $("#username").val();         

            $.ajax({
                type: "POST",
                contentType: "application/json",
                url: "http://IpAddress/Service1.svc/addReportLine",
                data: JSON.stringify({ productId: productVal, productName: nameVal, warehouse: whVal, unitPrice: unitVal, totalPrice: totalVal, qty: qtyVal, currentUser: userVal }),
                dataType: "json",
                success: function (data){
                    populateReportGrid();                                                       
                },
                error: function(result){
                    alert(result.status + ' ' + result.StatusText); 
                }
            });                 
        }

This is the code that is being used from the web service:

    //Add line to report
    public void addReportLine(string productId, string productName, string warehouse, string unitPrice, string totalPrice, string qty, string currentUser)
    {
        try
        {
            SqlConnection connection = DbManagement.getConnection();
            connection.Open();

            SqlDataReader reader = null;
            SqlCommand command = null; ;
            SqlCommand insertCommand, updateCommand;
            Boolean isUpdate = false;

            //Insert
            insertCommand = new SqlCommand("INSERT INTO isProductReport(productId, productName, unitPrice, qty, userId, warehouseId, totalPrice) " +
                                           "VALUES('" + productId + "', " +
                                                  "'" + productName + "', " +
                                                   "" + Double.Parse(unitValue, CultureInfo.InvariantCulture) + ", " +
                                                   "" + Convert.ToInt32(qtyValue) + ", " +
                                                  "'" + currentUser + "', " +
                                                  "'" + warehouse + "', " +
                                                   "" + Double.Parse(totalValue, CultureInfo.InvariantCulture) + ")", connection);
            insertCommand.ExecuteNonQuery();

            connection.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Here's the problem: The server where the DB is, have configured a "," (comma) as decimal separator, and "." (dot) as thousands separator, so I initially ended up using CultureInfo.InvariantCulture in another application (which does not use json) to save it correctly and it worked. If I run the web service in my computer locally, the data is saved correctly, but if I call the web service hosted in the server, then I get the errors.

If I leave the number with the "." I get error "500 undefined", but if I change it to "," (for testing purposes) it saves the number without any separator. I'd like to know what can I do to save it properly in the database, I would appreciate your help, thanks!

EDIT

I have to thank Chuck Conway, not for the direct answer, but for telling me about the "network" tab in Firefox, which allowed me to see the exact error, after enabling "debug" in the web.config. It turns out that, since the configuration in the server was replacing dots in decimals for commas, the insert query was recognizing all the decimals as separate parameters (for example 2.21 was being recognized as '2','21') so the number of parameters did not match the number of fields specified in the query. I ended up inserting the values without any conversion to Double and SQL server did the trick.

THANKS A LOT!! (I'd like to mark any of your comments as the answer, but it seems like comments cannot be marked as answers.

0

There are 0 best solutions below