How can I modify the message for no results (zeroRecords) based on a JSON response/ other parameter
How can I modify the message for no results (zeroRecords) based on a JSON response/ other parameter
![Soundwaves](https://secure.gravatar.com/avatar/7c25ab023d3f709284c9832f9362a188/?default=https%3A%2F%2Fvanillicon.com%2F7c25ab023d3f709284c9832f9362a188_200.png&rating=g&size=120)
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();
});
});
This question has an accepted answers - jump to answer
Answers
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
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?
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
Kevin,
I think that may lead me down the right path, thank you.
I get an error by using
"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!
I think the problem is the issue discussed in this FAQ. You will need to use this instead:
Or change your initialization to use
$('#npwoList').DataTable({...})
instead of$('#npwoList').dataTable({...})
.For example:
https://live.datatables.net/qoliyehi/78/edit
Kevin
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.
To implement on the client was just as started above.