How can I modify the message for no results (zeroRecords) based on a JSON response/ other parameter

Soundwaves

For Context: I have an application using DataTables that uses the default search capabilities and works wonderfully. However there are other filters and permission restrictions that define what is displayed on the page. I am getting user complaints that when they can't find an existing item they would like to know why.

That being said, I can easily create a custom message when zero results are found. However I have no idea how I can pass it back to the DataTable so it can be displayed.

Code shared below uses a handler to fetch the data. In this example I add the custom message as a field and hide it on the screen. However this obviously doesn't work because there are no records displayed when there are no records ..

Here is the code for my handler and ASPX page. It is more for reference as I know this strategy won't work.

Any Ideas would be greatly appreciated!

Public Sub ProcessRequest(context As HttpContext) Implements IHttpHandler.ProcessRequest
    Dim displayLength As Integer = Integer.Parse(context.Request("iDisplayLength"))
    Dim displayStart As Integer = Integer.Parse(context.Request("iDisplayStart"))
    Dim sortCol As Integer = Integer.Parse(context.Request("iSortCol_0"))
    Dim sortDir As String = context.Request("sSortDir_0")
    Dim search As String = context.Request("sSearch")

    Dim Name As String = context.Server.HtmlEncode(context.Request.Cookies("User_Profile")("User_Name"))
    Dim User_Name As String = FindName(Name)
    Dim User_Id As String = FindId(Name)
    Dim Area As String = context.Server.HtmlEncode(context.Request.Cookies("User_Profile")("Default_Area"))
    Dim Dept As String = ""
    Dim Status As String = ""
    Dim ViewMyWOs As String = "All"
    Try
        ViewMyWOs = context.Server.HtmlEncode(context.Request.Cookies("WOInfo")("SaveMyWO"))
    Catch ex As Exception
    End Try
    Try
        If Not context.Request.Cookies("userInfo") Is Nothing Then
            Dept = _
                 context.Server.HtmlEncode(context.Request.Cookies("userInfo")("SaveDeptQuery"))
            Status = _
                 context.Server.HtmlEncode(context.Request.Cookies("userInfo")("SaveStatusQuery"))
        End If
    Catch ex As Exception

    End Try
    Dim spName As String = "spGetWORKORDER"

    Dim cs As String = ConfigurationManager.ConnectionStrings("WorkOrdersConString").ConnectionString
    Dim listWOs As New List(Of WorkOrder)()
    Dim filteredCount As Integer = 0
    Using con As New SqlConnection(cs)
        Dim cmd As New SqlCommand(spName, con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@DisplayLength", displayLength)
        cmd.Parameters.AddWithValue("@DisplayStart", displayStart)
        cmd.Parameters.AddWithValue("@SortCol", sortCol)
        cmd.Parameters.AddWithValue("@SortDir", sortDir)
        cmd.Parameters.AddWithValue("@Search", If(String.IsNullOrEmpty(search), Nothing, search))
        cmd.Parameters.AddWithValue("@Area", Area)
        cmd.Parameters.AddWithValue("@Dept", If((Dept = "All Departments"), Nothing, Dept))
        cmd.Parameters.AddWithValue("@Status", Status)
        cmd.Parameters.AddWithValue("@User_Name", User_Name)
        cmd.Parameters.AddWithValue("@User_Id", User_Id)
        cmd.Parameters.AddWithValue("@ViewMyWOs", ViewMyWOs)
        con.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader()
        While rdr.Read()
            Dim workOrder As New WorkOrder()
            workOrder.WO_Id = Convert.ToInt32(rdr("WO_Id"))
            workOrder.Priority = rdr("Priority").ToString()
            ...
            workOrder.Completion_Date = rdr("Completion_Date").ToString()
            If workOrder.Completion_Date <> "" Then
                workOrder.Completion_Date = Convert.ToDateTime(workOrder.Completion_Date).ToShortDateString
            End If

            workOrder.Late = DateTime.Compare(Convert.ToDateTime(workOrder.Requested_Completion), DateTime.Now.AddDays(-1))
            filteredCount = Convert.ToInt32(rdr("TotalCount"))
            Dim message As String = "No matching records found"
            If filteredCount = 0 Then
                'Modify message based on the reason for no results
                message = "I like to Party"
            End If
            workOrder.Message = message
            listWOs.Add(workOrder)
        End While
    End Using

    Dim result = New With { _
        Key .iTotalRecords = GetTotalCount(), _
        Key .iTotalDisplayRecords = filteredCount, _
        Key .aaData = listWOs _
    }


    Dim js As New JavaScriptSerializer()
    context.Response.Write(js.Serialize(result))
End Sub

ASPX Page

$(document).ready(function () {
                var table = $('#npwoList').dataTable({
                    dom: 'Bfrtip',
                    columns: [
                        { 'data': 'WO_Id' },
                        { 'data': 'Priority' },
                        ...
                        { 'data': 'Message' }
                    ],
                    columnDefs:
                        [
                            { //these columns are always visible
                                "targets": [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15],  
                                "visible": true,
                                "orderable": true,
                                "sortable": true,
                                "searchable": true,
                            },
                            {  //these columns are always invisible
                                "targets": [16,17],
                                "visible": false,
                                "orderable": false,
                                "sortable": false,
                                "searchable": false
                            }
                        ],                        
                    bServerSide: true,
                    sAjaxSource: 'Handlers/WODataHandler.ashx',
                    deferRender: true,
                    lengthMenu: [
                    [25, 50, 100, 1000],
                    ['25 rows', '50 rows', '100 rows', 'Show all']
                    ],
                    buttons: ['pageLength',
                         {
                             extend: 'colvis',
                             collectionLayout: 'fixed two-column',
                             postfixButtons: ['colvisRestore']
                         }
                    ],
                    language: {
                        buttons: {
                            colvis: 'Hide/Show Columns'                                
                        },                            
                        zeroRecords: **Add Message Here!!**//$('#npwoList tbody tr:eq(2) td:eq(17)').text()
                    },
                    "scrolly": 400,
                    "stateSave": true,
                    "order": [[0, "desc"]],
                    "stateSaveParams": function (settings, data) {
                        data.start = 0;
                    },

                    fnRowCallback: function (row, data, displayIndex) {
                        var api = this.api();
                        if ($(api.cell(displayIndex, 13).node()).text() == 'In-Process' &&
                                $(api.cell(displayIndex, 16).node()).text() > 0) {
                            $(row).addClass('green');
                        }
                        if ($(api.cell(displayIndex, 13).node()).text() != 'Complete' &&
                                $(api.cell(displayIndex, 13).node()).text() == 'Waiting - Engineering' &&
                                    $(api.cell(displayIndex, 16).node()).text() >= 0){
                            $(row).addClass('blue');
                        }
                        if ($(api.cell(displayIndex, 13).node()).text() != 'Complete' &&
                                $(api.cell(displayIndex, 16).node()).text() < 0) {
                            $(row).addClass('red');
                        }
                        if ($(api.cell(displayIndex, 13).node()).text() != 'Complete' &&
                                $(api.cell(displayIndex, 13).node()).text() != 'In-Process' &&
                                $(api.cell(displayIndex, 16).node()).text() > 0) {
                            $(row).addClass('black');
                        }
                        if ($(api.cell(displayIndex, 13).node()).text() != 'Complete' &&
                                $(api.cell(displayIndex, 13).node()).text() == 'Monitor') {
                            $(row).addClass('blue');
                        }                            
                    }
                });
                $('#npwoList tbody').on('click', 'tr', function () {
                    var id = $('td', this).eq(0).text();
                    document.getElementById('<%=txtWorkOrderId.ClientID%>').value = id;
                    var clickButton = document.getElementById("<%= btnOpenNPWO.ClientID%>");
                    clickButton.click();
                });
            });

colin

You could put that hidden message into language.emptyTable - that's displayed when the table has no records. Otherwise, I guess you could display that message above the table or somewhere that the user would see.

Colin

Soundwaves

Thanks Colin, setting the message isn't an issue. The problem is how to send it from the server side handler as an extra parameter to consume it on the client side. Or an alternative method, like using a function inside the launguage:emptytable to retrieve it?

kthorngren

I didn't think it would work but it looks like using language.zeroRecords as a function to get the JSON response works. See this example:
https://live.datatables.net/qoliyehi/77/edit

Looks like you can return your zero records reason as an object in the JSON response then access it to display the language.zeroRecords string.

Kevin

Soundwaves

Kevin,
I think that may lead me down the right path, thank you.
I get an error by using

table.ajax.json().recordsTotal;

"ViewNPWO.aspx:1511 Uncaught TypeError: Cannot read properties of undefined (reading 'json')"

but I can adapt the syntax to my application this should work.
stay tuned!

kthorngren

I think the problem is the issue discussed in this FAQ. You will need to use this instead:

$('#npwoList').DataTable().ajax.json()

Or change your initialization to use $('#npwoList').DataTable({...}) instead of $('#npwoList').dataTable({...}).

For example:
https://live.datatables.net/qoliyehi/78/edit

Kevin

Soundwaves

I will give Kevin full credit for solving this for me, but would like to add some context for anyone who may run into this.

First add an extra item to the JSON response. Example below, I added imessage as a parameter that come with the response even when it has no results.

Dim result = New With {
            Key .iTotalRecords = GetTotalCount(),
            Key .iTotalDisplayRecords = filteredCount,
            Key .imessage = message,
            Key .aaData = listWOs
        }

To implement on the client was just as started above.

language: {
                           zeroRecords: function () {
                                return function () {
                                    return $('#npwoList').DataTable().ajax.json().imessage;
                                };
                            }
                        },