Sorting and Filtering Not working in Kendo Grid

1 Answer 3899 Views
Data Source
Amanda.Hicks@navcanada.ca
Top achievements
Rank 1
Amanda.Hicks@navcanada.ca asked on 04 Feb 2014, 03:29 PM
I am just starting to use the Telerik kendo grid so not sure if I left something out

We have an MVC project and are using razr views.  I have a Kendo grid displaying the results of a dynamic SQL query which doesn't allow for sorting or filtering.   Are these attributes inherent in the grid or am I missing something?

My grid:

@(Html.Kendo().Grid(Model)
.Name("Grid")
.Columns(columns =>
{
foreach (System.Data.DataColumn column in Model.Columns)
{
  columns.Bound(column.ColumnName).Groupable(true).Sortable(true).Filterable(true);
}
})
.Sortable(sortable => sortable.AllowUnsort(false))
.Scrollable()
.Filterable()
.Groupable()
.DataSource(dataSource => dataSource
.Ajax()
.Model(model =>
{
foreach (System.Data.DataColumn column in Model.Columns)
{
if (column.ColumnName.Contains("-"))
{
column.ColumnName = column.ColumnName.Replace("-", "");
}
model.Field( column.ColumnName , column.DataType);
}
})
.Read(read => read.Action("ReadData", "Grid"))
)
)

My data source:

public ActionResult ReadData([DataSourceRequest] DataSourceRequest request, int id)
{
DataTable queryResults = QueryResults(id);
return Json(queryResults.ToDataSourceResult(request), "text/html", System.Text.Encoding.Unicode, JsonRequestBehavior.AllowGet);
}

private DataTable QueryResults(int id)

{

String queryString = (from a in _qryList where a.Id == id select a.Sql).First() ;
var dataTable = new DataTable();
try
{
var dataAdapter = new SqlDataAdapter(queryString, connection);
dataAdapter.Fill(dataTable);
}
catch (SqlException e)
{
ViewBag.TextareaHTML = "Your SQL Query is invalid.\r"+e.Message;
}
return dataTable;

}

Thanks

1 Answer, 1 is accepted

Sort by
0
Alexander Popov
Telerik team
answered on 05 Feb 2014, 11:44 AM
Hello Amanda,

Basically after the Read controller is reached the data gets stored in the Grid's DataSource, which supports client-side operations like sorting and filtering (see serverOperation option).

Regards,
Alexander Popov
Telerik
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
Paolo
Top achievements
Rank 1
commented on 12 Nov 2015, 05:39 PM

In Kendo UI for PHP I have the same problem on a grid.

Filtering and sorting display an empty grid.

The mysql query log shows sorting is missing the order by field:

1096 Query SELECT a.idPDV, a.codiceSAP, a.ragioneSociale, a.indirizzo, a.CAP, a.comune, a.telefono FROM anagraficapdv a ORDER BY  LIMIT 0,10

and the filter query log shows that the where clause is empty in the count query:

1100 Query  SELECT COUNT(*) FROM anagraficapdv a  WHERE ()

I have the same code working on other grids and I use this method fr the grid options:

$grid
        ->addColumn($idpdvCol)
        ->addColumn($category)
        ->addColumn($supplier)
        ->dataSource($dataSource)
        ->pageable(true)
        ->editable("popup")
        ->selectable('row single')
        ->change("onChange")
        ->dataBound('onDataBound')
        ->dataBinding('onDataBinding')
        ->sortable(true)
        ->resizable(true)
        ->reorderable(true)
        ->filterable(true);

Any suggestions?
Alexander Popov
Telerik team
commented on 16 Nov 2015, 02:14 PM

Hi Paolo,

I am afraid that the provided information does is not sufficient to determine what causes the issue. Would you please share the DataSource configuration as well as the server-side PHP code responsible for handling the read requests? 

Regards,
Alexander Popov
Telerik
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
Mark
Top achievements
Rank 1
commented on 07 Jan 2016, 02:22 PM

this is no help

using the grid builder fluent syntax, with either a strongly typed model or not, the filter does not work at all.

the read method gets pounded every time the filter on the column is accessed on the page, but there are no messages in the command for the filter, and the client side code is either missing or broken.

since there is no documentation on any theory of operations, and the examples all look like what I have,  I don't see the value of a telerik solution.

Mark
Top achievements
Rank 1
commented on 07 Jan 2016, 05:09 PM

for the mvc extensions, in the datasource, there is support for .ServerOperation(<true | false>)

for client operation - false, for server operation - true

the grid will post the sort,filter,page,pagesize,aggregate,... in the forms string of the http request so that you can handle that in your data rendering.

nothing works if .ServerOperation(true) and you don't handle it on the server side.  The trick is to find the grid state for the current options.  AFAIK, there is nothing on the telerik website doco for this.

Alexander Popov
Telerik team
commented on 11 Jan 2016, 04:16 PM

Hello Mark,

I am not sure I understand you very well. Basically, if the ServerOperation option is set to false, then the Read should be performed once. All following operations such as paging, filtering and etc should be performed on the client side without any further requests to the server. Setting the ServerOperation to true will send the DataSourceRequest arguments to the server, where they should be handled by the ToDataSourceRequest method, as explained in the Grid Ajax binding article. 

Regards,
Alexander Popov
Telerik
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
Mark
Top achievements
Rank 1
commented on 11 Jan 2016, 04:42 PM

sorry, the referenced example has a lot of noise about creating a sample project.  somewhere in there, the reference to the helper markup shows the .ServerOperation() option on the helper, but does not explain how the sort and filter conditions are sent to the server callback.  The answer is in the httprequest form variables sort and filter.  not in the datasourcerequest object as I would expect.

As noted above, the .ServerOperation() should always be stated so that you don't see random behaviors such as I have noticed during debugging (the grid seems to pick client or server on it's own, or based on some prior context)

Mark
Top achievements
Rank 1
commented on 11 Jan 2016, 04:45 PM

I *thought* I posted that response to myself on this forum, but I guess I just answered the question when I closed the support ticket.  The question was 'where or how are the sort and filter conditions passed back to the server when using server side sorting and filtering"  the answer is in the httprequest form variables
Alexander Popov
Telerik team
commented on 12 Jan 2016, 12:33 PM

Hi Mark,

Parameters are sent through the form data and parsed on the server by the DataSourceResult ModelBinder. For further information, please check the following:

Regards,
Alexander Popov
Telerik
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
Priyanka
Top achievements
Rank 1
commented on 19 Oct 2017, 02:15 PM

I have the same issue , can you please send me a sample code?
Stefan
Telerik team
commented on 23 Oct 2017, 09:53 AM

Hello, Priyanka,

Could you please clarify the exact issue that occurs?

I can assume that the data is not showing and server operations are set to true.

If this is correct, please check in the network tab the request which is made to the server, and if all of the data is correctly parsed on the server.

Then if the data is correct, check the data which is received from the server and observe if the response is empty.

Also, please advise if this is the jQuery Grid or one of the wrappers.

Regards,
Stefan
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Vigneswar
Top achievements
Rank 1
commented on 31 May 2018, 11:09 AM

Kendo UI grid sortable(or reordering or drag drop rows in grid) not disabled on using kendoSortable destroy jQuery in all scenarios.

I've a scenario in which the grid reordering or drag-drop rows(can be achieved using kendoSortable in kendo UI) should be enabled or disabled. 
For ex., if a particular set of columns are sorted or grouped the drag-drop function should be disabled and if one specific column is sorted it should be enabled. 
The problem occurs when applying filter to some columns. During filtering if kendoSortable is applied to a column it stays the same even if ("kendoSortable").destroy() is executed i.e the drag-drop functionality should be disabled on executing destroy() only that it wasn't. All it did was return some kinda error while the page was inspected.
Find attachment screen of error with this question or the text of error,

Error in event handler for (unknown): TypeError: Cannot read property 'options' of undefined
    at y.<anonymous> (chrome-extension://mgijmajocgfcbeboacabfgobmjgjcoja/content.min.js:16:421)

Attaching the jQuery method(will be invoked from databound event of grid) that implements the function,

    KendoCustom.OnSortAndGroup = function (e) {
    var grid = e.sender;    
    if (grid.dataSource.group() && grid.dataSource.group().length > 0
        && grid.dataSource.sort() && grid.dataSource.sort().length > 0) {
        grid.tbody.find('>tr').each(
                 function () {
                     $(this).css('cursor', 'default');
                 });

        if ($("#RulesGrid").data("kendoSortable")) {
            $("#RulesGrid").data("kendoSortable").destroy();
        }
    }
    else if (grid.dataSource.sort() && grid.dataSource.sort().length > 0) {
        var col = grid.dataSource.sort();
        if (col[0]["field"] == 'Rank' || col[0]["field"] == 'VisualRank') {
            grid.tbody.find('>tr').each(
                 function () {
                     $(this).css('cursor', 'move');
                 });

            $("#RulesGrid").kendoSortable({
                "change": onChangeDrag,
                "filter": "table > tbody > tr",
                "container": "#RulesGrid tbody",
                "cursor": "move",
                "hint": noHint,
                "placeholder": placeholder
            });
        }
        else {
            grid.tbody.find('>tr').each(
                 function () {
                     $(this).css('cursor', 'default');
                 });

            if ($("#RulesGrid").data("kendoSortable")) {
                $("#RulesGrid").data("kendoSortable").destroy();
            }
        }
    }
}

Stefan
Telerik team
commented on 01 Jun 2018, 05:46 AM

Hello, Vigneswar,

Thank you for the details.

I tried different scenarios, but on my end, if the Sortable was destroyed no errors occurred.

Could you please share an example where the issue could be observed as I can assume that there is a factor which we are overlooking at this moment.

https://dojo.telerik.com/ETaBoVaj

Thank you in advance for the collaboration.

Regards,
Stefan
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Vigneswar
Top achievements
Rank 1
commented on 01 Jun 2018, 01:55 PM

Hey Stefan,

Thanks a lot for the reply and suggesting your answer. I've modified the same code present in dojo and will add the code at end of this reply to showcase a common error or issue that's part of kendo grid.

Here i have made one change. Commented out the code for button(Toggle Sortable) click. Enabled the kendoSortable function whenever the column 'ProductName' is sorted through databound event of grid. If any other columns(like Unit Price, Units In Stock, Discontinued) other than 'ProductName' is sorted i will disable the kendoSortable function of grid.

There are multiple ways to reproduce the issue(prerequisite - please save the code as a html file and run it). Adding those below,

1) Sort the 'ProductName' column twice or more by clicking it continuously. At this point the grid rows will be draggable. Now click on any other column, say 'Unit Price'. Now it is expected to disable the drag-drop rows(or kendoSortable) functionality of grid. But you still can perform drag-drop function even though the code to disable(grid.table.data("kendoSortable").destroy();) it gets executed.

2) Now filter a column, type something like 'cha' by choosing 'contains' option in filter box. You would see three rows with 'cha' filtered. Now sort 'ProductName' column which will make grid rows drag-droppable. Next click on 'Unit Price' column to sort it, expected functionality is to disable drag-drop. But you still can perform drag-drop function on grid rows.

Pasting the code below,

<!DOCTYPE html>
<html>
<head>
    <base href="http://demos.telerik.com/kendo-ui/sortable/integration-grid">
    <style>html { font-size: 14px; font-family: Arial, Helvetica, sans-serif; }</style>
    <title></title>
    <link rel="stylesheet" href="//kendo.cdn.telerik.com/2015.3.930/styles/kendo.common.min.css" />
    <link rel="stylesheet" href="//kendo.cdn.telerik.com/2015.3.930/styles/kendo.default.min.css" />

    <script src="//kendo.cdn.telerik.com/2015.3.930/js/jquery.min.js"></script>
    <script src="//kendo.cdn.telerik.com/2015.3.930/js/kendo.all.min.js"></script>
</head>
<body>
    <script src="../content/shared/js/products.js"></script>

        <div id="example">
            <a class="k-button" id="button">Toggle Sortable</a>
            <div id="grid"></div>

            <script>
                var grid;
                $(document).ready(function() {
                    grid = $("#grid").kendoGrid({
                        dataSource: {
                            data: products,
                            schema: {
                                model: {
                                    fields: {
                                        ProductName: { type: "string" },
                                        UnitPrice: { type: "number" },
                                        UnitsInStock: { type: "number" },
                                        Discontinued: { type: "boolean" }
                                    }
                                }
                            },
                            pageSize: 16,
sort: {field: "ProductName", dir: "asc"}
                        },
dataBound: OnDataBound,
sortable: true,
                        scrollable: false,
filterable: true,
groupable: true,
                        columns: [
                            "ProductName",
                            { field: "UnitPrice", title: "Unit Price", format: "{0:c}", width: "130px" },
                            { field: "UnitsInStock", title: "Units In Stock", width: "130px" },
                            { field: "Discontinued", width: "130px" }
                        ]
                    }).data("kendoGrid");
                    //$("#button").on("click", sortable);
                });

function OnDataBound(e)
{
var grid = e.sender;
//for disabling drag-drop of rows in grid while grouping
if (grid.dataSource.group() && grid.dataSource.group().length > 0
&& grid.dataSource.sort() && grid.dataSource.sort().length > 0) {
if (grid.table.data("kendoSortable")) {
grid.table.data("kendoSortable").destroy();
$(this).removeClass("k-state-selected");
}
}

else if (grid.dataSource.sort() && grid.dataSource.sort().length > 0) {
var col = grid.dataSource.sort();
//for enabling drag-drop of rows in grid while sorting'ProductName' column
if (col[0]["field"] == 'ProductName')
{
grid.table.kendoSortable({
                            filter: ">tbody >tr",
                            hint: $.noop,
                            cursor: "move",
                            placeholder: function(element) {
                                return element.clone().addClass("k-state-hover").css("opacity", 0.65);
                            },
                            container: "#grid tbody",
                            change: function(e) {
                                var skip = grid.dataSource.skip(),
                                    oldIndex = e.oldIndex + skip,
                                    newIndex = e.newIndex + skip,
                                    data = grid.dataSource.data(),
                                    dataItem = grid.dataSource.getByUid(e.item.data("uid"));

                                grid.dataSource.remove(dataItem);
                                grid.dataSource.insert(newIndex, dataItem);
                            }
});
$(this).addClass("k-state-selected");
}
//for disabling drag-drop of rows in grid while any columns other than 'ProductName' is sorted
else
{
if (grid.table.data("kendoSortable")) {
grid.table.data("kendoSortable").destroy();
$(this).removeClass("k-state-selected");
}
}
}
}

                function sortable() {
                    if (grid.table.data("kendoSortable")) {
                        grid.table.data("kendoSortable").destroy();
                        $(this).removeClass("k-state-selected");
                    } else {
                        grid.table.kendoSortable({
                            filter: ">tbody >tr",
                            hint: $.noop,
                            cursor: "move",
                            placeholder: function(element) {
                                return element.clone().addClass("k-state-hover").css("opacity", 0.65);
                            },
                            container: "#grid tbody",
                            change: function(e) {
                                var skip = grid.dataSource.skip(),
                                    oldIndex = e.oldIndex + skip,
                                    newIndex = e.newIndex + skip,
                                    data = grid.dataSource.data(),
                                    dataItem = grid.dataSource.getByUid(e.item.data("uid"));

                                grid.dataSource.remove(dataItem);
                                grid.dataSource.insert(newIndex, dataItem);
                            }
                        });
                        $(this).addClass("k-state-selected");
                    }
                }
            </script>

            <style>
                .k-grid tbody tr {
                    cursor: move;
                }
            </style>
        </div>


</body>
</html>

Please reply back if you need more information. Thanks again.

 

Stefan
Telerik team
commented on 04 Jun 2018, 06:40 AM

Hello, Vigneswar,

Thank you for the example and all of the details.

I have tested this on my end and was able to observe the described behavior.

After testing I noticed that this occurs because the destroy is actually called once the Grid is already initialized with the sorting and the reference exists.

I can suggest refreshing the Grid as soon as the Sortable is destroyed to initially a fresh Grid once the Sortable reference is destroyed:

else
{
  if (grid.table.data("kendoSortable")) {
    grid.table.data("kendoSortable").destroy();
    $(this).removeClass("k-state-selected");
    e.sender.refresh()
  }
}

https://dojo.telerik.com/AHAFOFox

I do understand that this will cause and additional re-render, but it is currently required in order to initialize the Grid without the dereferences to the Sortable widget.

Regards,
Stefan
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Vigneswar
Top achievements
Rank 1
commented on 04 Jun 2018, 02:54 PM

Hi Stefan

Thanks for the suggestion. But it didn't make any difference to the issue and i am still facing the same. It's evident in the dojo too - https://dojo.telerik.com/AHAFOFox. Is there any other alternative way you would recommend ?

Regards,

Vigneswar

Stefan
Telerik team
commented on 05 Jun 2018, 06:30 AM

Hi,

Thank you for the clarification.

This is indeed strange as when I tested it, only after this change the undesired result was fixed.

Still, I made more tests and added a check to only add the Sortable if there is not already one attached to prevent attaching multiple ones.

I modified the example and made a video of the results on my end:

https://www.screencast.com/t/Q0uS5KPXS3vh

https://dojo.telerik.com/AHAFOFox/2

I hope that this approach will resolve the issue.

Regards,
Stefan
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Vigneswar
Top achievements
Rank 1
commented on 07 Jun 2018, 12:31 PM

Hey Stefan,

Thanks a lot for the solution. It somehow did the trick, adding a condition in 'if' to check whether kendoSortable is in use. There was a small issue in the change you've made, while sorting it was enabling kendoSortable only when a sort was made in ascending order. Add this to the 'if' in else block i.e.,

change else from this,

if (grid.table.data("kendoSortable")) {
                grid.table.data("kendoSortable").destroy();
                $(this).removeClass("k-state-selected");
              }

to this,

if (grid.table.data("kendoSortable") && col[0]["field"] != 'ProductName') {
                grid.table.data("kendoSortable").destroy();
                $(this).removeClass("k-state-selected");
              }

Thanks again. 

Regards,

Vigneswar

 

Stefan
Telerik team
commented on 08 Jun 2018, 07:35 AM

Hello, Vigneswar,

I'm glad to hear that the issue is resolved.

Thank you for sharing the additional modification as well.

Regards,
Stefan
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Sivaramakrishna Reddy
Top achievements
Rank 1
Veteran
commented on 31 Mar 2020, 01:48 PM

Thank you very much. It's working for me now.
Tags
Data Source
Asked by
Amanda.Hicks@navcanada.ca
Top achievements
Rank 1
Answers by
Alexander Popov
Telerik team
Share this question
or