Decimal numbers not rendering correctly

JPC! · April 2023

I am using DataTables Editor in a ASP.NET Core 7 environment. I want to render a simple decimal number (double) with two decimals. But unfortunately the decimals are always shown as "0". SO 123,45 is rendered to 123,00.

Without using the render-function the plain text shows all decimals. What could be my mistake?

Thank's in advance for any help.

This is my example code:

Controller (C#):

    public ActionResult GetTestNumber()
    {
        string connectionString = _config.GetConnectionString("TestConn");
        using (var db = new Database("sqlserver", connectionString))
        {
            var response = new Editor(db, "Abf_EK_Vertrag", pkey: "ID")
                                .Field(new Field("ID"))
                                .Field(new Field("EKP"))
                                .Process(HttpContext.Request)
                                .Data();
            return Json(response);
        }
    }

HTML:

    <table id="TestNumber" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" style="width:100%">
        <thead>
            <tr>
                <th>ID</th>
                <th>EKP</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
        <tfoot class="">
        </tfoot>
    </table>

JS:

    var editortest = new $.fn.dataTable.Editor({
        display: 'bootstrap',
        ajax: {
            type: 'POST',
            url: '@Url.Action("GetTestNumber")',
            dataSrc: function (result) {
                return result.data
            }
        },
        table: "#TestNumber",
        fields: [
            { label: 'ID', name: 'ID' },
            { label: 'EKP', name: 'EKP' }
        ]
    });

    var tabletest = $('#TestNumber').DataTable({
        processing: true,
        serverSide: true,
        sServerMethod: 'POST',
        pagination: true,
        aLengthMenu: [
            [5, 100, -1],
            [5, 100, "Alle"]
        ],
        iDisplayLength: 5,
        scrollX: false,
        sScrollY: 205,
        bScrollCollapse: true,
        ajax: {
            type: 'POST',
            url: '@Url.Action("GetTestNumber")',
            dataSrc: function (result) {
                return result.data
            }
        },
        columns: [
            { data: 'ID', name: 'ID', width: '80px' },
            { data: 'EKP', name: 'EKP', width: '80px', render: $.fn.dataTable.render.number(null, null, 2, null, null) }
        ],
        order: [[0, 'desc']]
    });

Resulting Table:

When rendering without the render-function for the "EKP"-field the resulting table is:

allan · April 2023

Can you show me the Ajax response from the server please? It looks like it might be returning , for the number's decimal point. However, the number rendering formatter for DataTables assumes that the wire format will use a . d.p., and will transform it to a comma if needed.

Thanks,
Allan

JPC! · April 2023

Hi Allan,

thank's for caring!

Here is the data:

[
{
"DT_RowId": "row_11889",
"ID": "11889",
"EKP": "123,123"
},
{
"DT_RowId": "row_11888",
"ID": "11888",
"EKP": "123,123"
},
{
"DT_RowId": "row_11887",
"ID": "11887",
"EKP": "0,456"
},
{
"DT_RowId": "row_11886",
"ID": "11886",
"EKP": "100,12"
},
{
"DT_RowId": "row_11885",
"ID": "11885",
"EKP": "345,67"
}
]

Hope it helps,

Jens

allan · April 2023

Yes, that would do it. We expect the wire format to not be a formatted value. Is EKP a string in the database? Or perhaps it is a locale setting?

Allan

JPC! · April 2023

No, it's a float in SQL-Server ...

JPC! · April 2023

Ist there any possibility to specify the data type for EKP in C# controller code defining the editor response? When I use the data model of .net Core - where data types are specified - in an HTML-form, decimals are processed as expected. So I don't think it depends on local settings, but of course here in Germany we are using the comma as the decimal separator.

JPC! · April 2023

After trying a few things by myself I finally could find a solution for my problem. Using the GetFormatter method in the C# controller code I just replaced the annoying "," with the correct "." and now the decimals are rendered as expected :-)

C#

            var response = new Editor(db, "Abf_EK_Vertrag", pkey: "ID")
                                .Field(new Field("ID"))
                                .Field(new Field("EKP")
                                    .GetFormatter((val, data) => val.ToString().Replace(",", ".")))
                                .Process(HttpContext.Request)
                                .Data();

Resulting table:

Thanks for pointing me in the right direction, Allan ;-)

allan · April 2023

Hi,

Thanks for the updates. Yes, the get formatter will do the trick. I wonder though if we need to specify a locale to SQL Server to have it not output a formatted number.

If you were to run:

SELECT @@LANGUAGE;

what does it output?

Allan

JPC! · April 2023

The output was "Deutsch"!

So I changed the SQL Server language version to english, even by reinstall. Now LANGUAGE outputs "us_english". But to my surprise the effect with the "," decimals is still there. I'm a bit confused about that.

allan · April 2023

Me too! That happens even if you just do a SELECT ... on the table directly?

I'm absolutely certain that it is a locale setting thing - we just need to know the right incantation to get SQL Server to respond with unformatted information. There will be a SET option that can do that on a per session / connection basis. But I'm not familiar enough with SQL Server to know what it is, and my Google-fu is letting me down this morning!

Allan

JPC! · April 2023

When I grab the data with LINQ like this:

C#:

            var ekAnalysen = db.Abf_EK_Vertrag.Select(p => new { p.EKP }).ToList();

it delivers the "." as decimal separator. When using the DataTables .NET API library,
I get the ","

Seem's I'll keep using the get formatter ;-)

allan · April 2023

Interesting. I'll try experimenting a bit with my SQL Server install here. Thanks for looking into it and good to know you've got a workaround!

Allan

JPC! · April 2023

Yes, I would be very interested, if you should find the reason for that behaviour. Thanks again for your advice concerning the comma. That was definitely the key

Jens

Decimal numbers not rendering correctly

Decimal numbers not rendering correctly

Answers

Howdy, Stranger!

Categories

DataTables

Decimal numbers not rendering correctly

Decimal numbers not rendering correctly

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables