Loading grid options causes an error when calling ToDataSourceResult

11 posts, 1 answers
  1. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 13 Oct Link to this post

    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?

  2. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 13 Oct Link to this post

    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.
  3. Telerik UI for ASP.NET Core is out
  4. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 14 Oct Link to this post

    Hello Andrew,

    Could you share the configuration of the grid as well?

    Regards,
    Vasil
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  5. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 14 Oct Link to this post

    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()
     
                 )

     

     

  6. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 14 Oct Link to this post

    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.");
                            }
                    });
  7. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 17 Oct Link to this post

    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.

  8. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 18 Oct Link to this post

    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.
  9. Answer
    Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 19 Oct Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  10. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 19 Oct Link to this post

    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.");
                        }
                    }

     

     

  11. Andrew
    Andrew avatar
    16 posts
    Member since:
    May 2016

    Posted 19 Oct Link to this post

    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?

  12. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 21 Oct Link to this post

    Hi Andrew,

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

    Regards,
    Vasil
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
Telerik UI for ASP.NET Core is out