How to create table dynamically with dynamic column names & rows too.

How to create table dynamically with dynamic column names & rows too.

tajtaj Posts: 20Questions: 5Answers: 0

controller -

        public JsonResult GetTableSchema(string Tablename)
        {
            // List<TABLESCHEMADATAMODEL> objTableSchemaDm = new MasterBL().GetTableSchema(Tablename);
            //List<MasterConfiguration.TableSchema> objTableVm = new MasterConverter().ConvertTableSchema(objTableSchemaDm);
            DataTable objTableSchemaDm = new MasterBL().GetTableSchema(Tablename);

            string JSONString = string.Empty;
            JSONString = JsonConvert.SerializeObject(objTableSchemaDm);

            return Json(JSONString);
        }

Ajax Call -

    $("#ddltable").change(function () {
            debugger;
            var Tablenameselect = $("#ddltable").find("option:selected").text();
            var columns = [];
            //alert(Tablenameselect);
            $.ajax({
            type: "POST",
                url: "@Url.Action("GetTableSchema", "Master")",
                data: { Tablename: Tablenameselect },
                dataType: "json",

                success: function (data) {
                    debugger;

                    console.log(data);
                    columnNames = Object.keys(data[0]);
                    console.log(columnNames);
                    for (var i in columnNames) {
                        columns.push({
                            data: columnNames[i],
                            title: columnNames[i]
                        });
                    }

                    $("#divGridSales").css({ display: "block" });
                    $('#tblViewPartDetails').DataTable({
                        "processing": true, // for show progress bar
                        "serverSide": false, // for process server side
                        "filter": true, // this is for disable filter (search box)
                        "orderMulti": false, // for disable multiple column at once
                        "bDestroy": true,   //for reinitialize the datatable
                        "data": data,
                        "columns": columns,
                        "responsive": true
                    });
                    },
                error: function (data) {
                    alert("Error while fetching Data");
                }
            });

        });

e.g. from API code as below

        public DataTable GetTableSchema(string pTablename)
        {
            try
            {
                DataSet dsTable = _sql.ExecuteDataSet(null, "Select * from " + Tablename).ResultSet;

                dsTable.Tables[0].TableName = "TableSchema";
                return dsTable.Tables["TableSchema"];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

**here table name will be any table it is not fixed, so as per table schema Datatable has to create dynamic table with column names & rows.
**

Note: Here by using above code I am getting blank Datatable

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Here by using above code I am getting blank Datatable

    Are you getting a properly formatted Datatable with the search and paging elements and the dynamic columns are built properly?

    Do you get errors in the browser's console?

    What are the results of console.log(data); on line 15? Is there data and is it an array of objects?

    Please provide a link to your page or a test case showing the issue so we can help debug. If you are unable to do that then provide more details of the debugging you have done and what the JSON response is.

    Kevin

  • tajtaj Posts: 20Questions: 5Answers: 0

    On console.log(data); on line 15 I am getting data like below

    [{"CountryID":1,"CountryCode":"AF","CountryName":"Afghanistan","ISO_CODES":"AF / AFG","STR2":"AF","STR3":"AFG"},{"CountryID":2,"CountryCode":"AL","CountryName":"Albania","ISO_CODES":"AL / ALB","STR2":"AL","STR3":"ALB"},{"CountryID":3,"CountryCode":"DZ","CountryName":"Algeria","ISO_CODES":"DZ / DZA","STR2":"DZ","STR3":"DZA"},{"CountryID":4,"CountryCode":"AS","CountryName":"American Samoa","ISO_CODES":"AS / ASM","STR2":"AS","STR3":"ASM"}]
    

    On console.log(columnNames); on Line 17 I am getting data as below

    ["0"]
    0:"0"
    length:1
    _proto_:Array(0)
    

    Hi @Kevin,
    Test scenario you can easily create. I will give one example
    here I am taking any master data table from given DB. then by API taking data from that table for further CRUD operation.(e.g. select * from tblCountryMaster)

    Imp. - main thing is that table structure/schema is not fixed i.e. main issue for binding columns & rows to Data table plugin is data coming in json format not an array of object.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited July 2021

    It doesn't seem like columnNames = Object.keys(data[0]); is providing the expected result. That statement with your data example works here:
    http://live.datatables.net/bejexevo/1/edit

    Is your data code snippet showing the full structure of the response?

    Your code snippets don't have anything obvious to indicate the problem. Live debugging of the page or a test case showing the issue is the only we we will be able to understand what is happening to help debug.

    Kevin

  • tajtaj Posts: 20Questions: 5Answers: 0

    Hi @kthorngren your are right when I am assigning json string manually (hardcoded) to data then it works fine like below,

    e.g. -

    data = [{ "CountryID": 1, "CountryCode": "AF", "CountryName": "Afghanistan", "ISO_CODES": "AF / AFG", "STR2": "AF", "STR3": "AFG" }, { "CountryID": 2, "CountryCode": "AL", "CountryName": "Albania", "ISO_CODES": "AL / ALB", "STR2": "AL", "STR3": "ALB" }, { "CountryID": 3, "CountryCode": "DZ", "CountryName": "Algeria", "ISO_CODES": "DZ / DZA", "STR2": "DZ", "STR3": "DZA" }, { "CountryID": 4, "CountryCode": "AS", "CountryName": "American Samoa", "ISO_CODES": "AS / ASM", "STR2": "AS", "STR3": "ASM" }];
    

    But when I am directly using data which is coming from Ajax call (line no. 12), this time I am getting blank data table.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited July 2021

    There is something different with your data response (function (data)) then. Is the structure different, ie, is the row data nested in another property? Is it a sting or an object?

    Kevin

  • tajtaj Posts: 20Questions: 5Answers: 0

    Hi @kthorngren now working fine with following code

     data = JSON.parse(data);
    

    but now next issue is when I am going to change table from dropdown(line no.1) that time following error occurs

    dataTables.js:240 Uncaught TypeError: Cannot read property 'style' of undefined
    at Ya (dataTables.js:240)
    at za (dataTables.js:198)
    at f (dataTables.js:340)
    at HTMLTableElement.<anonymous> (dataTables.js:341)
    at Function.each (jquery-3.4.1.min.js:381)
    at jQuery.fn.init.each (jquery-3.4.1.min.js:203)
    at jQuery.fn.init.u [as dataTable] (dataTables.js:322)
    at jQuery.fn.init.k.fn.DataTable (dataTables.js:601)
    at Object.success (ConfigureTables:429)
    at fire (jquery-3.4.1.min.js:3497)

    and datatable shows columns of new table but data of previous table

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Sounds similar to this thread.

    Kevin

  • tajtaj Posts: 20Questions: 5Answers: 0
    edited July 2021

    Hi @kthorngren I changed my java script like below

                        $('#tblViewPartDetails').DataTable({
                            "processing": true, // for show progress bar
                            "serverSide": false, // for process server side
                            "filter": true, // this is for disable filter (search box)
                            "orderMulti": false, // for disable multiple column at once
                            "bDestroy": true,   //for reinitialize the datatable
                            "data": data,
                            "columns": columns,
                            "responsive": true,
                            "empty":true
                        });
    

    but still same issue persists.

  • tajtaj Posts: 20Questions: 5Answers: 0

    Hi @kthorngren here I am not getting how do I give call to empty function.

  • tajtaj Posts: 20Questions: 5Answers: 0
    edited July 2021

    Hi @colin can you help me out from above issue?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Cheers,

    Colin

  • tajtaj Posts: 20Questions: 5Answers: 0

    Hi @colin I have uploaded code & details here in comment but getting here

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    how do I give call to empty function

    Use the $.fn.dataTable.isDataTable() to determine if you have initialized the Datatable. If you have then destroy and use jQuery empty() to empty the table. Something like this:

    if ( $.fn.DataTable.isDataTable( '#tblViewPartDetails' ) ) {
        table = $('#tblViewPartDetails').DataTable();
        table.destroy();
        $('#tblViewPartDetails').empty();
    }
    $('#tblViewPartDetails').DataTable({ ... });  // Your init options
    

    Kevin

  • tajtaj Posts: 20Questions: 5Answers: 0
    edited July 2021

    thanks lot @kthorngren, its working now.
    please one more help - tell me how do I implement Inline CRUD operations on Datatable created as above.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited July 2021

    The best option is to use the Editor. Here is an inline editing example.

    Kevin

  • tajtaj Posts: 20Questions: 5Answers: 0

    @kthorngren I know this example but this is related to php & I am working with c#.
    is it suitable for c#?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Looks like Datatables provides PHP, .NET and NodeJS server side libraries libraries. You can write your own that follows this client server protocol. I use Python so I wrote my own. You might be able to find third party C# Datatables Editor libraries. Or you can create your own client and server side solution.

    Kevin

Sign In or Register to comment.