How to parse data from SQL Server to Jqgrid

Question

I am using datatable to create a table and it's working:

<table id="display" class="display" width="100%" data-search="true"></table>
<script src="//cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css"></script>
<script>
    $(document).ready(function () {
        var data = JSON.parse('@DATA_QUERIED');
        var keys = Object.keys(data[0]);
        var columns = [];
        for (var k in keys) {
            columns.push({

                'title': keys[k],
                'data': keys[k],
                'targets': k,
                'width': 60
            });
        }
        $('#display').DataTable({
            'data': data,
            'columns': columns,
            'searching': true,
            "paging": true,
            "ordering": true,
            "info": true
        });
    });
</script>

So how can I do it with jqgrid?

<table id="list5"></table>
<div id="paper5"></div>
<script>
    $(document).ready(function () {
        jQuery("#list5").jqGrid({
            datatype: "json",
            data: "{}",
            colModel: [
                { name: 'USER_ID', index: 'USER_ID', width: 100 },
                { name: 'USER_NAME', index: 'USER_NAME', width: 140 },
                { name: 'LOGIN_NAME', index: 'LOGIN_NAME', width: 140 },
                { name: 'PASSWORD', index: 'PASSWORD', width: 140, align: "right" },
                { name: 'DESCRIPTION', index: 'DESCRIPTION', width: 140, align: "right" },
                { name: 'GROPU_ID', index: 'ROPU_ID', width: 140, align: "right" },
                { name: 'EMAIL', index: 'EMAIL', width: 200, sortable: false },
                { name: 'ACTIVE', index: 'ACTIVE', width: 50 },
                { name: 'ORGANIZATION_ID', index: 'RGANIZATION_IDz', width: 140}
            ],
                paper:"lits5",
                rowNum: 10,
                rowList: [10, 20, 30],
                pager: '#pager5',
                sortname: 'USER_ID',
            viewrecords: true,
            sortorder: "desc",
            caption: "Simple data manipulation",
          
        })
        jQuery("#list5").jqGrid('navGrid','#paper5',{edit:false,add:false,del:false});
    });
</script>

I have this SQL query:

SECLECT * 
FROM sec_log

And the code I used to parse it:

var data = JSON.parse('@DATA_QUERIED');
var keys = Object.keys(data[0]);
var columns = [];

for (var k in keys) {
        columns.push({

            'title': keys[k],
            'data': keys[k],
            'targets': k,
            'width': 60
        });

My question is: how to do it with JQGrid?

I need to show that data in a table, how can I work with jqgrid, I already had success with datatable, and could you explain in detail how jqgrid work with JSON.

Answer 1

after reading document about jqgrid, this code is working for me:

<table id="list47" ></table>
<div id="plist47"></div>

<script>

    var mydata = JSON.parse('@DATA_QUERIED');


    $(document).ready(function () {

        jQuery("#list47").jqGrid({
            data: mydata,
            datatype: "local",
            height: 'auto',
            rowNum: 30,
            rowList: [10, 20, 30],
            colNames:['USER_ID', 'USER_NAME', 'LOGIN_NAME', 'PASSWORD', 'DESCRIPTION', 'GROUP_ID', 'EMAIL', 'ACTIVE', 'ORGANIZATION_ID'],
            colModel: [
                { name: 'USER_ID', index: 'USER_ID', width: 100, },
                { name: 'USER_NAME', index: 'USER_NAME', width: 140, },
                { name: 'LOGIN_NAME', index: 'LOGIN_NAME', width: 140, },
                { name: 'PASSWORD', index: 'PASSWORD', width: 140, },
                { name: 'DESCRIPTION', index: 'DESCRIPTION', width: 140, },
                { name: 'GROUP_ID', index: 'GROUP_ID', width: 140, },
                { name: 'EMAIL', index: 'EMAIL', width: 200, },
                { name: 'ACTIVE', index: 'ACTIVE', width: 70, sorttype: "int" },
                { name: 'ORGANIZATION_ID', index: 'RGANIZATION_IDz', width: 140, sorttype: "int" }
            ],
            pager: "#plist47",
            viewrecords: true,
            sortname: 'USER_ID',

            grouping: true,
            groupingView: {
                groupField: ['USER_ID'],
                groupColumnShow: [false]
            },
            caption: "Đây là ví dụ mẫu về Grid"
        });

    });
</script>