Decimal numbers not rendering correctly

JPC!

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

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!

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

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!

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

JPC!

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!

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

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!

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

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!

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

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!

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