Data not showing properly with serverside processing [MVC/WebAPI]

Thyrador

Hey there, got some serious issues here where I need some advice to get through this.

A while ago I had similar issues but got them resolved. Many things have changed, so I had to restructure my application and change to serverside processing (due to lots of data that can be returned to the client). So I decided to serve partial results, depending on the filter and page the client actually has applied.

To get straight into it, these are my columns (which are shown properly):

[{
    "title": "DWDOCID",
    "data": "DWDOCID",
    "name": "DWDOCID",
    "defaultContent": "",
    "type": "num-fmt",
    visible: false
}, {
    "title": "DWPAGECOUNT",
    "data": "DWPAGECOUNT",
    "name": "DWPAGECOUNT",
    "defaultContent": "",
    "type": "num-fmt",
    visible: false
}, {
    "title": "DWDISKNO",
    "data": "DWDISKNO",
    "name": "DWDISKNO",
    "defaultContent": "",
    "type": "num-fmt",
    visible: false
}, {
    "title": "DWFLAGS",
    "data": "DWFLAGS",
    "name": "DWFLAGS",
    "defaultContent": "",
    "type": "num-fmt",
    visible: false
}, {
    "title": "DWSTOREDATETIME",
    "data": "DWSTOREDATETIME",
    "name": "DWSTOREDATETIME",
    "defaultContent": "",
    "type": "de_date",
    visible: false
}, {
    "title": "DWSTOREUSER",
    "data": "DWSTOREUSER",
    "name": "DWSTOREUSER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "DWMODDATETIME",
    "data": "DWMODDATETIME",
    "name": "DWMODDATETIME",
    "defaultContent": "",
    "type": "de_date",
    visible: false
}, {
    "title": "DWMODUSER",
    "data": "DWMODUSER",
    "name": "DWMODUSER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "DWLASTACCESSDATETIME",
    "data": "DWLASTACCESSDATETIME",
    "name": "DWLASTACCESSDATETIME",
    "defaultContent": "",
    "type": "de_date",
    visible: false
}, {
    "title": "DWLASTACCESSUSER",
    "data": "DWLASTACCESSUSER",
    "name": "DWLASTACCESSUSER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "DWEXTENSION",
    "data": "DWEXTENSION",
    "name": "DWEXTENSION",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Kennzeichen",
    "data": "KENNZEICHEN",
    "name": "KENNZEICHEN",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Fahrgestellnummer",
    "data": "FAHRGESTELLNUMMER",
    "name": "FAHRGESTELLNUMMER",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Name",
    "data": "NAME",
    "name": "NAME",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Ablagedatum",
    "data": "ABLAGEDATUM",
    "name": "ABLAGEDATUM",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Absender",
    "data": "ABSENDER",
    "name": "ABSENDER",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Abteilung",
    "data": "ABTEILUNG",
    "name": "ABTEILUNG",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Aktennummer",
    "data": "AKTENNUMMER",
    "name": "AKTENNUMMER",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "GW-Nr.",
    "data": "GW_NR_",
    "name": "GW_NR_",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Herstellercode",
    "data": "HERSTELLERCODE",
    "name": "HERSTELLERCODE",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Mandant",
    "data": "MANDANT",
    "name": "MANDANT",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Mandant_Fibu",
    "data": "MANDANT_FIBU",
    "name": "MANDANT_FIBU",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "StateTabField",
    "data": "STATETABFIELD",
    "name": "STATETABFIELD",
    "defaultContent": "",
    "type": "string"
}, {
    "title": "Aktenstatus",
    "data": "AKTENSTATUS",
    "name": "AKTENSTATUS",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Änderungsdatum",
    "data": "_NDERUNGSDATUM",
    "name": "_NDERUNGSDATUM",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Annehmer",
    "data": "ANNEHMER",
    "name": "ANNEHMER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "AutoIndex",
    "data": "AUTOINDEX",
    "name": "AUTOINDEX",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Barcode",
    "data": "BARCODE",
    "name": "BARCODE",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Bearbeitet von:",
    "data": "BEARBEITET_VON_",
    "name": "BEARBEITET_VON_",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Beleg-Datum",
    "data": "BELEG_DATUM",
    "name": "BELEG_DATUM",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Belegtyp",
    "data": "BELEGTYP",
    "name": "BELEGTYP",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Bemerkung",
    "data": "BEMERKUNG",
    "name": "BEMERKUNG",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Buchungstext",
    "data": "BUCHUNGSTEXT",
    "name": "BUCHUNGSTEXT",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Empfänger",
    "data": "EMPF_NGER",
    "name": "EMPF_NGER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Erstzulassung",
    "data": "ERSTZULASSUNG",
    "name": "ERSTZULASSUNG",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Eskalationsmanager",
    "data": "ESKALATIONSMANAGER",
    "name": "ESKALATIONSMANAGER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Externe Belegnummer",
    "data": "EXTERNE_BELEGNUMMER",
    "name": "EXTERNE_BELEGNUMMER",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Fahrzeugtyp",
    "data": "FAHRZEUGTYP",
    "name": "FAHRZEUGTYP",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
                                                            ... clipped
    "title": "Weitergeleitet an:",
    "data": "WEITERGELEITET_AN_",
    "name": "WEITERGELEITET_AN_",
    "defaultContent": "",
    "type": "string",
    visible: false
}, {
    "title": "Wiedervorlage am:",
    "data": "WIEDERVORLAGE_AM_",
    "name": "WIEDERVORLAGE_AM_",
    "defaultContent": "",
    "type": "string",
    visible: false,
    "width": "100%"
}]

In the datatable I call the result like this:

ajax: {
                        url: "@Url.Action("GetDataForDatatable", "SearchResult")?filecabinet=@Model.FileCabinet.Id&searchmask=@Model.DefaultSearchDialogContainer&search=@Model.SearchId",
                        type: 'POST',
                        dataSrc: function (json) {
                            console.log(json);
                            if (json !== null) {
                                return json;
                            }
                        }
                    }

This is, what returns:

{
    "draw": 1,
    "recordsTotal": 1,
    "recordsFiltered": 1,
    "data": "[{"
    resultCheckbox ": " < input class = 'resultCheckbox'
    type = 'checkbox'
    onclick = 'setToolbox()'
    aria - label = 'CheckBox' > ","
    info ": " < a tabindex = '0'
    class = 'resultInfo bs-docs-popover'
    data - content ? = 'Daten werden geholt bitte warten...'
    target = '_blank'
    role = 'button'
    data - trigger = 'focus'
    data - toggle = 'popover' > < div align = 'center'
    style = 'padding-top: 1px;width:28px;height:28px;' > < img src = '../images/icon_info_blue_16px.png' / > < /div></a > ","
    DWSTOREUSER ": "
    ADMIN ","
    DWMODUSER ": "
    ADMIN ","
    DWLASTACCESSUSER ": "
    ADMIN ","
    DWEXTENSION ": ".pdf ","
    ABLAGE_IN_BUCHHALTUNG ": "
    TEST ","
    EXTERNE_BELEGNUMMER ": "
    1063\ / 490477 ","
    NAME ": "
    DAIMLER AG "}]"
}

And this, what it looks like:
https://datatables.net/forums/uploads/editor/bc/p764bi0tlb6t.png

Im not quite sure what causes this behaviour, so I hope you guys can tell me wht went wrong here.

Link to debug: debug.datatables.net/ihiley

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

kthorngren

Seems like it has something to do with your dataSrc. If you look at the debug output and go to Table > Rows then click through each row you will see each row has one letter. Looks like it is parsing the JSON as a string one character at a time.

Maybe you need to change the return to something like this:

JSON.parse(json);

Kevin

Thyrador

Thanks for your reply. Did that before, but this results in the following error:
Uncaught SyntaxError: Unexpected token r in JSON at position 58

Oh, I see. Seems that the "'s within that array did not get escaped correctly

EDIT: If I remove these "'s, then nothing will be returned (which is wrong, since there is a single row inside the container.
If I remove JSON.parse it looks like before
So there's still something wrong

EDIT 2:
debug.datatables.net/uhekot

Now this is my return value:
{"draw":1,"recordsTotal":1,"recordsFiltered":1,"data":[{"resultCheckbox": "<input class='resultCheckbox' type='checkbox' onclick='setToolbox()' aria-label='CheckBox'>","info": "<a tabindex='0' class='resultInfo bs-docs-popover' data-content?='Daten werden geholt bitte warten...' target='_blank' role='button' data-trigger='focus' data-toggle='popover' ><div align='center' style='padding-top: 1px;width:28px;height:28px;'><img src='../images/icon_info_blue_16px.png' /></div></a>","DWSTOREUSER": "ADMIN","DWMODUSER": "ADMIN","DWLASTACCESSUSER": "ADMIN","DWEXTENSION": ".pdf","ABLAGE_IN_BUCHHALTUNG": "TEST","EXTERNE_BELEGNUMMER": "1063\/490477","NAME": "DAIMLER AG"}]}

kthorngren

Is there a reason for the dataSrc?

Maybe try without to see what happens.

Kevin

allan

You can't use ajax.dataSrc with server-side processing I'm afraid - at least not in 1.10 - I plan to change that in future.

You need to return valid JSON from the server. At the moment it is returning a string value.

Allan

Thyrador

Well, I just have to have this call as a POST, so I'm not quite sure how to use this without dataSrc

allan

I don't quite understand I'm afraid. You have "type": "POST" which will cause the Ajax request to be a POST request.

The key is to make the server return valid JSON. That isn't something that can be altered on the client-side.

Allan

Thyrador

Silly me, haven't slept enough the last few days. I just passed the url like in the example given. I now removed the ajax.dataSrc - part and it works again via POST.

Thanks für your help. This approach unfortunately breaks a few things, but I'll have to fix them one by one.