Loading grid options causes an error when calling ToDataSourceResult

[Andrew]

I am using the save/load grid state feature in JS for my Kendo UI MVC Grid. It looks like this:
Saving state:

var grid = $("#Items").data("kendoGrid");
                sessionStorage.GridState = kendo.stringify(grid.getOptions());

Loading state:

$(document).ready(function () {
                var grid = $("#Items").data("kendoGrid");
                var originalOptions = grid.getOptions();
                var stringifiedOptions = sessionStorage.GridState;

                if (stringifiedOptions) {
                    console.log("Loaded Grid State.");

                    var loadedOptions = JSON.parse(stringifiedOptions);
                    loadedOptions.columns = originalOptions.columns;
                    grid.setOptions(loadedOptions);

                    sessionStorage.removeItem("GridState");

                    console.log("Clearing Grid State Storage.");
                }
            });

The first time the grid loads, grid state gets saved after clicking the edit button, which takes me to another page, but then if I click back from this edit page to go back to the grid page, it shows this error:

{"There is already an open DataReader associated with this Command which must be closed first."}
dataSourceResult.Total = source1.Count();

And I can see the load grid ajax action is being called twice, and it errors out on the second time. The error occurs when I call MyBaseEntitiesQueryable.ToDataSourceResult(request). I can't debug the Kendo UI code, but when I was able to copy the classes in the grid filtering to mimic it and it occurs on this line in ToDataSourceResult:

dataSourceResult.Total = source1.Count();

The full error and stack trace:
{"There is already an open DataReader associated with this Command which must be closed first."}
   at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
{"An error occurred while executing the command definition. See the inner exception for details."}
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at Kendo.Mvc.Extensions.QueryableExtensions.Count(IQueryable source)
   at Kendo.Mvc.Extensions.QueryableExtensions.CreateDataSourceResult[TModel,TResult](IQueryable queryable, DataSourceRequest request, ModelStateDictionary modelState, Func`2 selector)
   at Kendo.Mvc.Extensions.QueryableExtensions.ToDataSourceResult(IQueryable queryable, DataSourceRequest request, ModelStateDictionary modelState)
   at Kendo.Mvc.Extensions.QueryableExtensions.ToDataSourceResult(IQueryable enumerable, DataSourceRequest request)
   at Sandbox.Controllers.EntityController`5.LoadGrid(DataSourceRequest request) in C:\Users\shol726\Documents\Visual Studio 2015\Projects\Drs\samples\Sandbox\Controllers\EntityController.cs:line 75
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()

To make matters worse, this error happens only about 75% of the time, not all the time. Sometimes I get "The underlying provider failed to Open." as well. But that is much rarer.

How can I fix this?

[Andrew]

I've confirmed this doesn't happen, with the exact same setup in the MVC 5 version of the grid, I've also confirmed the grid state loading is causing this error. If I remove the JS loading of grid state, this error does not occur.

[Vasil]

Hello Andrew,

Could you share the configuration of the grid as well?

Regards,
Vasil
Telerik by Progress

[Andrew]

Here is the full grid. The outcome is the same whether it is deferred or not and yes I am calling deferred scripts load later.

@(Html.Kendo().Grid<UserModel>()
             .Name("Items")
             .DataSource(dataSource => dataSource
                 .Ajax()
                 .Destroy(delete => delete.Action("DeleteGridItem", @ViewContext.RouteData.Values["controller"].ToString()))
                 .PageSize(25)
                 .Update(update => update.Action("Save", @ViewContext.RouteData.Values["controller"].ToString()))
                 .Sort(sort => sort.Add("Name"))
                 .Read(read => read.Action("LoadGrid", @ViewContext.RouteData.Values["controller"].ToString())))
             .ToolBar(tools =>
             {
                 tools.Custom().Text("Clear Filter").HtmlAttributes(new { @class = "gridAllFilterReset" });
                 tools.Excel().Text("Excel");
             })
             .Excel(excel => excel
                 .AllPages(true).FileName($"{ViewContext.RouteData.Values["controller"].ToString()}.xlsx").Filterable(true)
             )
             .Events(e => { e.ExcelExport("excelExport");
                              e.Remove("deleteItem");
             })
             .Columns(columns =>
             {
                 columns.Bound(x => x.Name);
                 columns.Bound(x => x.Email).ClientTemplate("<a href='mailto:#=Email#'>#=Email#</a>");
                 columns.Bound(x => x.Departments);
                 columns.Bound(x => x.Titles);
                 columns.Command(command =>
                 {
                     command.Custom("Edit").Text("<span class='k-icon k-edit'></span>").Click("editRecord");
                     command.Custom("Delete").Text("<span class='k-icon k-i-delete'></span>").Click("deleteItem");
                     //command.Destroy().Text(" ");
                 }).Width(150);
             })
             .Filterable(f =>
             {
                 //f.Extra(false);
                 f.Operators(o => o.ForString(x => x
                     .Clear()
                     .Contains("Contains")
                     .IsEqualTo("Is Equal To")
                     .StartsWith("Starts With")
                     .EndsWith("Ends With")
                     .DoesNotContain("Does Not Contain")
                     .IsNotEqualTo("Is Not Equal To")
                     .IsEmpty("Is Empty")
                     .IsNotEmpty("Is Not Empty")));
             })
             .Sortable(x => x.AllowUnsort(false))
             .Pageable()
             .Deferred()

             )

 

 

[Andrew]

And the full javascript related to the error.

function editRecord(e) {
                    e.preventDefault();

                    var grid = $("#Items").data("kendoGrid");
                    sessionStorage.GridState = kendo.stringify(grid.getOptions());
                    console.log("Saved Grid State.");

                    var dataItem = this.dataItem($(e.currentTarget).closest("tr"));
                    window.location = "@Url.Action("Save")/" + dataItem.Id;
                }

function deleteItem(e) {
                    e.preventDefault(); //prevent page scroll reset
                    console.log("deleting item");
                    var tr = $(e.target).closest("tr"); //get the row for deletion
                    var data = this.dataItem(tr); //get the row data so it can be referred later
                    $window.content(windowTemplate(data)); //send the row data object to the template and render it
                    $window.center().open();

                    $("#yesButton").click(function () {
                        var datasource = $("#Items").data("kendoGrid").dataSource;
                        datasource.remove(data);  //prepare a "destroy" request
                        datasource.sync();  //actually send the request (might be ommited if the autoSync option is enabled in the dataSource)
                        $window.close();
                    });
                    $("#noButton").click(function () {
                        $window.close();
                    });
                }

$(document).ready(function () {
                    $(".gridAllFilterReset").click(function (e) {
                        e.preventDefault();
                        console.log("Clearing filters");
                        var datasource = $("#Items").data("kendoGrid").dataSource;
                        //Clear filters and sort:
                        datasource.filter([]);
                        datasource.sort([]);
                    });

                    $(".k-grid-excel").click(function () {
                        $(this).attr("data-loading-text", "<i class='fa fa-circle-o-notch fa-spin'></i> Exporting...");
                        $(this).button('loading');
                    });

                        var grid = $("#Items").data("kendoGrid");
                        var originalOptions = grid.getOptions();
                        var stringifiedOptions = sessionStorage.GridState;

                        if (stringifiedOptions) {
                            console.log("Loaded Grid State.");

                            var loadedOptions = JSON.parse(stringifiedOptions);
                            loadedOptions.columns = originalOptions.columns;
                            grid.setOptions(loadedOptions);

                            sessionStorage.removeItem("GridState");

                            console.log("Clearing Grid State Storage.");
                        }
                });

[Andrew]

I was able to get this working by plugging into the databound event. It was calling the load grid action twice, the second time before the first time even finished when putting the loading of grid options in the document.ready(). Your documentation might want to mention this. Here is the revised javascript that doesn't cause the error:

 

function editRecord(e) {
                    e.preventDefault();

                    var grid = $("#Items").data("kendoGrid");
                    saveGridOptionsToSessionStorage("@ViewContext.RouteData.Values["controller"].ToString()", kendo.stringify(grid.getOptions()));
                    console.log("Saved Grid State.");

                    var dataItem = this.dataItem($(e.currentTarget).closest("tr"));
                    window.location = "@Url.Action("Save")/" + dataItem.Id;
                }

                function onFilter(e) {
                    console.log(e);
                }

                function onDataBound(e) {
                    var grid = e.sender;
                    if (grid.dataSource.total() === 0) {
                        var colCount = grid.columns.length;
                        $(e.sender.wrapper)
                            .find('tbody')
                            .append('<tr class="kendo-data-row"><td colspan="' +
                                colCount +
                                '" class="no-data">No records to display.</td></tr>');
                    } else {
                        var stringifiedOptions = loadGridOptionsFromSessionStorage("@ViewContext.RouteData.Values["controller"].ToString()");
                        console.log("Grid options loaded.");
                        if (stringifiedOptions) {

                            console.log("Grid Options is not null.");
                            var loadedOptions = JSON.parse(stringifiedOptions);
                            var originalOptions = grid.getOptions();
                            loadedOptions.columns = originalOptions.columns;

                            grid.setOptions(loadedOptions);
                            clearAllGridOptions();
                            console.log("Clearing Grid State Storage.");
                        } else {
                            console.log("Grid Options is null.");
                        }
                    }


                };

 

However, now when I restore my grid, I get a javascript e

 

Uncaught TypeError: Cannot read property 'removeClass' of null(…)
refresh kendo.all.js:26432
proxy @ jquery.js:497
trigger @ kendo.all.js:124
_process @ kendo.all.js:6924
success @ kendo.all.js:6669
success @ kendo.all.js:6596
n.success @ kendo.all.js:5587
fire @ jquery.js:3187
fireWith @ jquery.js:3317
done @ jquery.js:8785
(anonymous function) @ jquery.js:9151
Users:125 Grid options loaded.

 

When I call the line:

grid.setOptions(loadedOptions);

 

How can I resolve this? I really need this working because it breaks all other javascript on my page when it errors out, even though the restoring of the grid state works.

[Andrew]

Please see this link for an example project that duplicates this error. You can reproduce it by clicking the edit icon for any record in the row, then using the back button in your browser, the javascript console will show the error I am talking about. This breaks all other javascript on the page.

[Vasil]

Hi Andrew,

Thank you for the sample. I debugged it and found that the problem is caused by the calling grid.setOptions(loadedOptions) during the DataBound event.

When the event fires the success handler is not fully complete. When you load the state at this point the logic breaks and success does not finish correctly, so it is not removed from the query and second attempt is made, causing second callback. You could fix this by loading the settings a bit later. Here is an example based on your code.

function onDataBound(e) {
    var grid = e.sender;

    window.setTimeout(
        function(){


    if (grid.dataSource.total() === 0) {
        var colCount = grid.columns.length;
        $(e.sender.wrapper)
            .find('tbody')
            .append('<tr class="kendo-data-row"><td colspan="' +
                colCount +
                '" class="no-data">No records to display.</td></tr>');
    } else {
        var stringifiedOptions = loadGridOptionsFromSessionStorage("@ViewContext.RouteData.Values["controller"].ToString()");
        console.log("Grid options loaded.");
        if (stringifiedOptions) {
            debugger;
            console.log("Grid Options is not null.");
            var loadedOptions = JSON.parse(stringifiedOptions);
            var originalOptions = grid.getOptions();
            loadedOptions.columns = originalOptions.columns;

            grid.setOptions(loadedOptions);
            clearAllGridOptions();
            console.log("Clearing Grid State Storage.");
        } else {
            console.log("Grid Options is null.");
        }
    }
        } , 1000);
};

Regards,
Vasil
Telerik by Progress

[Andrew]

Thanks, this kind of works, but it's somewhat awkward, the whole grid loads first in the original state, then the grid flashes and applies the filtering one second later. Is there a smoother way to do this? Is there some way I can intercept the initial grid load before it happens to stop it? 

I tried doing the same thing with the DataBinding event, but the exact same behavior occurs. I get the error unless I add a timeout, but then the grid loads unfiltered first:

function onDataBinding(e) {
                    var grid = e.sender;
                    var stringifiedOptions = loadGridOptionsFromSessionStorage("@ViewContext.RouteData.Values["controller"].ToString()");
                    console.log("Grid options loaded.");
                    if (stringifiedOptions) {
                        window.setTimeout(
                        function(){
                        e.preventDefault();
                        console.log("Grid Options is not null.");
                        var loadedOptions = JSON.parse(stringifiedOptions);
                        var originalOptions = grid.getOptions();
                        loadedOptions.columns = originalOptions.columns;

                        grid.setOptions(loadedOptions);
                        clearAllGridOptions();
                        console.log("Clearing Grid State Storage.");
                        }, 1000);
                    } else {
                        console.log("Grid Options is null.");
                    }
                }

 

 

[Andrew]

I figured this out in a somewhat sloppy way:

var applyingFilters = false;

function onDataBinding(e) {
                    var stringifiedOptions = loadGridOptionsFromSessionStorage("@ViewContext.RouteData.Values["controller"].ToString()");
                    console.log("Grid options loaded.");
                    var grid = e.sender;
                    kendo.ui.progress(grid.element, true);

                    if (stringifiedOptions && applyingFilters === false) {
                        console.log("Grid Options is not null.");
                        e.preventDefault();
                        clearAllOtherGridOptions("@ViewContext.RouteData.Values["controller"].ToString()");
                        console.log("Clearing Grid State Storage.");

                        window.setTimeout(
                            function(){
                                var loadedOptions = JSON.parse(stringifiedOptions);
                                var originalOptions = grid.getOptions();
                                loadedOptions.columns = originalOptions.columns;

                                grid.setOptions(loadedOptions);


                            }, 700);

                        applyingFilters = true;
                    } else {
                        console.log("Grid Options is null.");
                    }
                    applyButtonEvents();
                }

 

I just set a variable outside of JS, to control if a grid state gets loaded when page loads. Of course setting the grid options causes data bind to fire again, hence the applyingFiltering variables. It works and doesn't cause the double flash of the grid I described earlier, but it's somewhat sloppy since the grid is loading twice. 

Is there a way to do it without the grid loading twice?

[Vasil]

Hi Andrew,

Binding the grid twice at this context can not be avoided, because setOptions rebinds grid. 

Regards,
Vasil
Telerik by Progress