Data Does Not Load When I Use jQuery AJAX & the "columns" Attribute

Tags: #<Tag:0x00007f51c6d09bb0> #<Tag:0x00007f51c6d09a70>
#1

When I use jQuery AJAX to load data from SQL Server and use the “columns” attribute at the same time, the data does not show in the Handsontable.

If I don’t use the “columns” attribute, it works just fine. Also, If I set the “data” attribute explicitly when using the “columns” attribute it also works fine.

Example 1: If explicitly set the “data” attribute it works fine.

<script type="text/javascript">
    $(document).ready(function () {
        const vCURRENTPAGE = window.location.pathname;
        const vDIVCONTAINER = document.querySelector('#DivHandsontable');

        const vHOT = new Handsontable(vDIVCONTAINER, {
            data: [ ['A1', 'B1', 'C1', 'D1', 'E1', 'F1'], ],
            colHeaders: true,
            rowHeaders: true,
            autoWrapRow: true,
            autoWrapCol: true,
            height: 'auto',
            headerClassName: 'htCenter',
            columns: [{ headerClassName: 'htRight' }, { headerClassName: 'htLeft' }, {}, {}, {}, {}],
            licenseKey: 'non-commercial-and-evaluation',
        });
    });
</script>

Example 2: However, If I set the “data” attribute using AJAX which gets data from SQL server, it doesn’t work. The Handsontable is blank. If I don’t use the “columns” attribute the data loads fine. When I do use the “columns” attribute the data does not load.

<script type="text/javascript">
    $(document).ready(function () {
        const vCURRENTPAGE = window.location.pathname;
        const vDIVCONTAINER = document.querySelector('#DivHandsontable');

        const vHOT = new Handsontable(vDIVCONTAINER, {
            colHeaders: true,
            rowHeaders: true,
            autoWrapRow: true,
            autoWrapCol: true,
            height: 'auto',
            headerClassName: 'htCenter',
            columns: [{ headerClassName: 'htRight' }, { headerClassName: 'htLeft' }, {}, {}, {}, {}],
            licenseKey: 'non-commercial-and-evaluation',
        });

        ExecuteAction_LoadSqlServerJson();

        function ExecuteAction_LoadSqlServerJson() {
            $.ajax({
                type: "POST",
                url: vCURRENTPAGE + "/LoadSqlServerJson",
                data: '{MyRecordID: "1" }',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (MyResponse) {
                    var vMYDATA = JSON.parse(MyResponse.d);
                    vHOT.loadData(vMYDATA);
                    $('#DivMessages').html('AJAX success response: Data loaded from SQL Server.');
                },
                error: function (jqXHR, textStatus, errorThrown) {
                    $('#DivMessages').html('AJAX failure response: An error occurred.');
                }
            });
        };
    });
</script>

Extra Information

In case you need it, here’s the structure of my aspx page and the associated code behind page.

My main aspx page:

<%@ Page Language="VB" blah, blah, blah %>
<!DOCTYPE html>
<html lang="en">
<head runat="server">
    <title>Handsontable Question</title>
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.css" />
</head>
<body>
    <form id="form1" runat="server">
        <div id="DivHandsontable"></div>
        <div id="DivMessages" style="margin-top: 24px;"></div>
</form>
<script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script>
<script type="text/javascript">
    $(document).ready(function () {
        //... see the two examples I provide above ... //
    });
</script>
</body>
</html>

My associated code behind page:

Imports System.Data.SqlClient

Partial Class column_property_with_sqlserver_data
    Inherits System.Web.UI.Page

    <System.Web.Services.WebMethod()>
    Public Shared Function LoadSqlServerJson(ByVal MyRecordID As String) As String
        Dim MySqlServerResult As String = ""

        '... code to retrieve data from SQL server in JSON format and set result to the MySqlServerResult variable ... '

        LoadSqlServerJson = MySqlServerResult
    End Function
End Class
#2

Hi @ptownbro

I checked our example from the documentation, where we load the data from the server but it’s a bit different and I can’t get the same results. The table is loaded correctly when there’s no initial data and the columns are being set. You can check it here: https://jsfiddle.net/5xzo8hwk/

To check you example I would need to also see a code demo where the problem can be replicated.

#3

Ok. You provided methods using Fetch instead of AJAX. I’m played around with Fetch, but haven’t fully learned it yet, so I’ll have to take next couple days to try your version and get back with a response. It will also give me an opportunity to transition to Fetch as well. Please keep open for a few days. Thanks

#4

I was able to get it to work using your example and now feel comfortable enough to transition to Fetch. Give me one more day to incorporate your example into mine and see if I can get it to work. Thanks

#5

Hi @ptownbro

Thank you for the update. I will wait for the results.

#6

I was able to narrow down the issue. It’s a bit difficult to explain, but I will try my best.

Your example is loading an “Array of Array” to the Handsontable. However, SQL Server returns the data in JSON format, which converts to an “Array of Objects” after you convert it with the “JSON.parse()” method. And… when the data format is “Array of Objects”, the data does not load when you also use the “columns” attribute. If you don’t use the “columns” attribute, it will load.

Here’s a slightly altered version of your example that focuses on the “load” button event where the issue is occurring.

const hot = new Handsontable(container, {
    ... blah, blah, blah ...
    columns: [ {}, {}, {}, {}, {}, {} ],
    licenseKey: 'non-commercial-and-evaluation',
});

load.addEventListener('click', () => {
    fetch('/myaspnetpage.aspx/LoadSqlServerJson', { method: 'POST', headers: { 'Content-Type': 'application/json' } } ).then((response) => {
        response.json().then((data) => {
            var vMYDATA = JSON.parse(data.d);
            vHOT.loadData(vMYDATA);
            exampleConsole.innerText = 'Data loaded';
        });
    });
});

If you look closely, my example is similar to yours with the exception of changes I needed to make to read the results from a function that lives in my ASP.NET code-behind page instead of the URL you use in your example. The changes include:

  1. The URL is pointing to a function that lives in my ASP.NET code-behind page
  2. I had to add the additional Fetch options: “Post” method and “Json” content type.
  3. I had to apply the “JSON.parse()” to the JSON data return by my function to turn it int an “Array of Objects”. I did this through the use of a variable and used that variable to load the data.
#7

I think I figured it out, when using the “Array of Objects” data format, you have to explicitly define each column in the column attribute.

For example, if your data set is in the Array of Objects format as follows

const ArrayofObjectsData = [
  { id: 1, name: 'Ted Right', address: '' },
  { id: 2, name: 'Frank Honest', address: '' }
];

You have to define each column in the columns attribute. And you have to define them even if you are not going to any properties:

const hot = new Handsontable(vDIVCONTAINER, {
    data: ArrayofObjectsData,
    columns: [ { data : 'id' }, { data : 'name' }, { data : 'address' } ],
});

//This won't work:
//c columns: [ {  }, {  }, {  } ],
#8

Hi @ptownbro

That seems like a good approach. I’m glad that you’ve found a solution.