locked
how to put condition on column datatable RRS feed

  • Question

  • User-952877843 posted

    i have this datatable i want to put condition on second column which is date if that date is less than current date write expired else write not expired i wrote this code but is not working

    <table id="example" class="table table-hover display" style="width:100%;cursor:pointer">
           <thead>
     
           </thead>
           <tfoot>
               <tr></tr>
           </tfoot>
       </table>
    function ToDatatable() {
     
          var Arr = [];
          var searches = "";
          $.ajax({
              type: "GET",
              url: "/Batch/getColumns",
              data: {
                  id: $("#id").val(),
                  item: $("#item").val(),
                  type: "GET",
     
              },
              success: function (response) {
                  searches += "<tr>";
                  data = $.map(response, function (item, a) {
                      Arr.push({ data: item.variable, title: item.variable });
                      //   searches += "<td><input style='width:100%' type='text' id=" + item.variable + " class='form-control' placeholder='Search " + item.variable + "'/></td>";
                  });
                  //  searches += "</tr>";
              },
              dataType: "json"
          }).done(function () {
              $('#example').append(searches);
              var sample = $('#example').DataTable({
                  "language": {
                      "zeroRecords""Nothing found - sorry",
                      "emptyTable""No data Available.",
                      "loadingRecords""Loading...",
                      "processing""Processing...",
                      "lengthMenu""Display _MENU_ records per page",
                      // "info": "Showing page _PAGE_ of _PAGES_",
                      "infoFiltered""(filtered from _MAX_ total records)",
                      // "sDom": 'Rfrtlip',
                  },
     
                  "dom"'<"top"f>rt<"bottom"ilp><"clear">',
                  "ajax": {
                      "processing"true,
                      "serverSide"true,
                      "url""/Batch/ConvertDataTabletoString",
                      "data": {
                          "id": $("#id").val(),
                          "item": $("#item").val(),
                          "type""GET",
     
                      },
                      "dataSrc""",
                      "deferRender"true,
     
     
                  },
                  "columns": Arr,
                  "sortable"true,
                  "bFilter"true,
                  "scrollX"true,
                  "searching"false,
                  "bInfo"false,
                  "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                  "columnDefs": [
                      //{
                      //      "render": function (data, type, row) {
                      //                var dateSplit = data;
                      //               return  type === "display" || type === "filter" ?
                      //                   dateSplit.substring(0, 10) : data;
     
     
     
                      //            },
                      //    "targets": 2,
                      //},
     
     
     
                       {
                           "render"function (data, type, row) {
     
     
                               var job_start_date = $("#current_date").val();
                               var job_end_date = data;
                               job_start_date = job_start_date.split('/');
                               job_end_date = job_end_date.split('/');
     
                               var new_start_date = new Date(job_start_date[2], job_start_date[0], job_start_date[1]);
                               var new_end_date = new Date(job_end_date[2], job_end_date[0], job_end_date[1]);
     
                               if (new_end_date > new_start_date) {
                               return '&nbsp;<img src="@Url.Content("~/img/green.png")" style="width:8px;height:8px;margin-left:10px">&nbsp;&nbsp  Expired';
     
                               }
     
                               else {
     
                             return '&nbsp;<img src="@Url.Content("~/img/green.png")" style="width:8px;height:8px;margin-left:10px">&nbsp;&nbsp Not Expired';
     
                               }
     
     
     
                           },
                           "targets": 2,
                       },
     
                  ]
              });
     
              var row;
              for (var i = 0; i < Arr.length; i++) {
     
                  row += "<option>" + Arr[i].title + "</option>";
              }
              $("#list1").append(row);
          });
     
          //$("#example").off("click", "tr").on("click", "tr", function () {
     
          //    var series = $(this).find("td:eq(1)").text();
          //    window.open("edit_Terms_and_Conditions_Template?series=" + series + "", "_self");
     
          //});
     
     
          var rowlist = [];
          $("#example").off("click""tr").on("click""tbody tr"function () {
              // alert($(this).index());
              var series = $(this).find("td:eq(0)").text();
     
              window.open("edit_batch?series=" + series + """_self");
     
          });
     
      }


    $(window).on("load"function () {
     
     
          if ($.fn.dataTable.isDataTable('#example')) {
              $('#example').DataTable().destroy();
              $('#example').empty();
              ToDatatable();
          }
          else {
              ToDatatable();
          }
     
     
      });
    Sunday, October 21, 2018 7:50 PM

All replies

  • User-893317190 posted

    Hi chnar,

    Js's Date api need parameter of type number and its month start with 0. For example, new Date(1990,1,1) represents February 1, 1990.

    Below is my code.

    <h2>Index</h2>
    <table id="example" class="table table-hover display" style="width:100%;cursor:pointer">
        <thead>
    
        </thead>
        
    </table>
    
    
    
    @section scripts{
    
    
    
        <script>
            function ToDatatable() {
    
          var Arr = [];
          var searches = "";
          $.ajax({
              type: "GET",
              url: "/Batch/getColumns",
    
              success: function (response) {
                  searches += "<tr>";
                  data = $.map(response, function (item, a) {
                      Arr.push({ data: item.variable, title: item.variable });
                      //   searches += "<td><input style='width:100%' type='text' id=" + item.variable + " class='form-control' placeholder='Search " + item.variable + "'/></td>";
                      searches += "<th>" + item.variable + "</th>";
                  });
                    searches += "</tr>";
              },
              dataType: "json"
          }).done(function () {
              $('#example thead').html(searches);
              var sample = $('#example').DataTable({
                 
                  "ajax": {
                      "processing": true,
                      "serverSide": true,
                      "url": "/Batch/ConvertDataTabletoString?name=2",
                      
                      "dataSrc": "",
                      "deferRender": true,
    
                  },
                  "columns": Arr,
                  "sortable": true,
                  "bFilter": true,
                  "scrollX": true,
                  "searching": false,
                  "bInfo": false,
                  "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                  "columnDefs": [
                   
    
                       {
                           "render": function (data, type, row) {
                               var date = data.split("/");
                             
                               date = new Date(parseInt(date[0]), parseInt(date[1])-1, parseInt(date[2]));
                              
                               if (date < new Date()) {
                                   return "expired";
                               } else{
                                   return "not expired";
                               }
                              
    
                           },
                           "targets": 1,
                       },
    
                  ]
              });
    
            
          });
    
    
      }
            $(window).on("load", function () {
    
    
                if ($.fn.dataTable.isDataTable('#example')) {
                    $('#example').DataTable().destroy();
                    $('#example').empty();
                    ToDatatable();
                }
                else {
                    ToDatatable();
                }
    
    
            });
        </script>

    My controller.

      
            public JsonResult getColumns() {
    
                ArrayList array = new ArrayList();
                array.Add(new { variable = "column1" } );
                array.Add( new { variable = "column2" });
                array.Add(new { variable = "column3" });
                return Json(array, JsonRequestBehavior.AllowGet);
    
            }
    
            public JsonResult ConvertDataTabletoString()
            {
                ArrayList array = new ArrayList();
                array.Add(new {column1="11",column2="2020/1/1",column3="13" });
                array.Add(new {column1= "21", column2 = "2018/10/6", column3 = "23" });
                array.Add(new {column1="31",column2="2018/12/4",column3="33" });
                return Json(array, JsonRequestBehavior.AllowGet);
            }

    The result.

    Best regards,

    Ackerly Xu

    Monday, October 22, 2018 3:33 AM
  • User-952877843 posted

    but why not working on my side

    here is my code

    view

    <link href="~/CSS/form_style.css" rel="stylesheet" />
    <link href="~/bootstrapp/css/bootstrap.min.css" rel="stylesheet" />
    <link href="~/DataTableStyle/dataTables.bootstrap.min.css" rel="stylesheet" />
    <script src="~/DataTableStyle/jquery-3.3.1.js"></script>
    <script src="~/DataTableStyle/jquery_dataTables_min.js"></script>
    <script src="~/DataTableStyle/dataTables.bootstrap.min.js"></script>
    <link href="~/CSS/bootstrap_datepicker1.css" rel="stylesheet" />
    <script src="~/CSS/bootstrap-datepicker2.js"></script>
    <script src="~/bootstrapp/js/bootstrap.min.js"></script> 
     
     
    <style>
        .dataTables_info {
            colorgray;
        }
     
        .dataTables_length {
            colorgray;
        }
     
        table.dataTable th {
            colorgray;
            background-color#eff3f9;
            font-weightbold;
            font-family'Century Gothic';
            white-spacenowrap;
            font-size14px;
        }
     
        table.dataTable tbody tr td {
            /*background-color: #ffffff;*/
            font-size14px;
            /*color:rebeccapurple;*/
            font-familyBahnschrift SemiBold;
            white-spacenowrap;
        }
     
     
        .div_item_header {
            width1355px;
            height70px;
            border-stylesolid;
            border-width0px 0px 1px 1px;
            border-colorlightgray;
            margin-left-9px;
            margin-top-10px;
        }
     
        .div_content_border {
            width950px;
            border-stylesolid;
            border-width0px 1px 1px 1px;
            border-colorlightgray;
            margin-left200px;
        }
     
     
     
        .account_title {
            font-size25px;
            margin-left620px;
            font-weightnormal;
        }
     
        .label_content {
            font-weightbold;
        }
     
     
     
        a:link {
            text-decorationnone;
            colorblack;
        }
     
        a:visited {
            text-decorationnone;
            colorblack;
        }
     
        a:hover {
            text-decorationunderline;
            colorblack;
        }
     
        a:active {
            text-decorationunderline;
            colorblack;
        }
     
        .width_ListTextbox {
            width50px;
        }
     
     
        /* Style Checkbox */
        .contain {
            displayblock;
            positionrelative;
            padding-left25px;
            padding-bottom12px;
            cursorpointer;
            font-size13px;
            font-familyarial;
            font-weightnormal;
            -webkit-user-selectnone;
            -moz-user-selectnone;
            -ms-user-selectnone;
            user-selectnone;
        }
     
            /* Hide the browser's default checkbox */
            .contain input {
                positionabsolute;
                opacity0;
                cursorpointer;
            }
     
        /* Create a custom checkbox */
        .checkmark {
            positionabsolute;
            top0;
            left0;
            height15px;
            width15px;
            background-color#eee;
            border-radius3px;
        }
     
        /* On mouse-over, add a grey background color */
        .contain:hover input ~ .checkmark {
            background-color#ccc;
        }
     
        /* When the checkbox is checked, add a blue background */
        .contain input:checked ~ .checkmark {
            background-color#2196F3;
        }
     
        /* Create the checkmark/indicator (hidden when not checked) */
        .checkmark:after {
            content"";
            positionabsolute;
            displaynone;
        }
     
        /* Show the checkmark when checked */
        .contain input:checked ~ .checkmark:after {
            displayblock;
        }
     
        /* Style the checkmark/indicator */
        .contain .checkmark:after {
            left5px;
            top3px;
            width4px;
            height7px;
            bordersolid white;
            border-width0 2px 2px 0;
            -webkit-transformrotate(45deg);
            -ms-transformrotate(45deg);
            transformrotate(45deg);
        }
     
     
     
     
        /* Accordion*/
     
     
        .accordion {
            background-colornone;
            colornone;
            cursorpointer;
            padding18px;
            width100%;
            bordernone;
            text-alignleft;
            outlinenone;
            font-size15px;
            transition0.4s;
        }
     
        .search {
            background-imageurl('../../img/search-icon.png');
            background-repeatno-repeat;
        }
     
        a:hover {
            colorblack;
        }
     
        .numberCircle {
            border-radius20%;
            behaviorurl(PIE.htc)/* remove if you don't care about IE8 */
            width27px;
            height25px;
            padding4px;
            backgroundred;
            border2px solid red;
            colorwhite;
            text-aligncenter;
            font-weightbold;
            font12px Arialsans-serif;
        }
     
        .erp {
            border-radius20%;
            behaviorurl(PIE.htc)/* remove if you don't care about IE8 */
            width30px;
            height30px;
            padding4px;
            margin-top-26px;
            margin-left100px;
            background#5f2b9b;
            border2px solid white;
            colorwhite;
            text-aligncenter;
            font-weightbold;
            font15px Arialsans-serif;
        }
     
        a:hover {
            colorblack;
            text-decorationnone;
        }
     
        a {
            colordarkgray;
        }
    </style>
     
     
     
    <br><br>
    <div style="position:fixed;z-index:1">
        <table border="0" style="background-color:#efefef;position:fixed;margin-left:-10px;margin-top:-45px;width:1380px;">
            <tr>
                <td style="height:55px;width:100px">
     
                    <div class="erp" style="margin-top:-1px;cursor:pointer" id="logo_erp">
                        E
                    </div>
     
     
                </td>
                <td>
                    <div style="margin-left:10px">
                        <a style="font-size:11pt;font-weight:bold;cursor:pointer;font-family:Calibri" id="stock"> > &nbsp; Stock</a>
                    </div>
                </td>
                <td style="width:190px">
                    <div>
                        <input class="form-control" type="text" style="width:210px;height:30px;font-size:12px" name="search" placeholder="Search...">
                    </div>
                </td>
     
                <td style="width:70px">
                    <div class="btn-group">
                        <div class="dropdown">
                            <button class="btn  dropdown-toggle" style="font-size:9pt;color:gray;background-color:#efefef" type="button" data-toggle="dropdown">
                                User
                                <span class="caret" style="z-index:1"></span>
                            </button>
                            <ul class="dropdown-menu">
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">Usage Info</a></li>
                                <li class="divider"></li>
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">My Settings</a></li>
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">Reload</a></li>
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">View Website</a></li>
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">Background Jobs</a></li>
                                <li class="divider"></li>
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">Chat Support</a></li>
                                <li class="divider"></li>
                                <li><a href="#" style="font-family:Calibri;font-size:10pt">Logout</a></li>
                            </ul>
                        </div>
                    </div>
                </td>
     
                <td style="width:70px">
                    <div>
                        <div class="btn-group">
                            <div class="dropdown">
                                <button class="btn  dropdown-toggle" style="font-size:9pt;color:gray;background-color:#efefef" type="button" data-toggle="dropdown">
                                    Help
                                    <span class="caret"></span>
                                </button>
                                <ul class="dropdown-menu">
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">help</a></li>
     
                                </ul>
                            </div>
                        </div>
                    </div>
                </td>
     
                <td style="width:100px">
                    <div class="numberCircle">1</div>
                </td>
            </tr>
        </table>
     
        <table border="0" style="background-color:white">
            <tr>
                <td style="background-colorwhite;border-bottom:1px solid #eaeaed;width:500px;height:60px">
     
                    <div style="margin-left:90px;margin-top:-10px;padding-top:20px;font-size:25px;font-weight:100">Batch</div>
                </td>
     
                <td style="background-colorwhite;width:2050px;border-bottom:1px solid #eaeaed"></td>
     
     
                <td style="background-colorwhite;width:70px;border-bottom:1px solid #eaeaed">
                    <div style="padding-top:10px">
                        <div class="btn-group">
                            <div class="dropdown">
                                <button class="btn  dropdown-toggle" style="font-size:9pt;color:black;background-color:#efefef" type="button" data-toggle="dropdown">
                                    Menu
                                    <span class="caret"></span>
                                </button>
                                <ul class="dropdown-menu">
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">Import</a></li>
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">User Permissions</a></li>
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">Role Permissions Manager</a></li>
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">Customize</a></li>
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">Assign To</a></li>
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">Print</a></li>
                                    <li><a href="#" style="font-family:Calibri;font-size:10pt">Add to Desktop </a></li>
     
                                </ul>
                            </div>
                        </div>
                    </div>
                </td>
     
                <td style="background-colorwhite;width:80px;border-bottom:1px solid #eaeaed">
                    <div style="padding-top:10px">
                        <input class="btn" id="referesh" type="submit" value="Referesh" style="color:black;background-color:#efefef;font-size:9pt" />
                    </div>
                </td>
     
                <td style="border-bottom:1px solid #eaeaed;width:280px;background-colorwhite">
                    <div style="padding-top:25px">
                        @using (Html.BeginForm(FormMethod.Post))
                {
                            <input class="btn btn-primary" formaction="@Url.Action("new_batch""Batch")" id="Button1" type="submit" value="New" style="font-size:9pt" />
                        }
                    </div>
                </td>
     
            </tr>
     
        </table>
    </div>
     
    <div class="div_content_border" style="height:autowidth1000pxmargin-left170pxmargin-top-1px">
     
        <div class="container">
            <div class="row">
     
                <div style="background-color:#efefef;width:998pxheight:56px;margin-top:73px">
                    <form class="form-inline" style="padding-top:10px;margin-left:10px;">
     
                        <div class="form-group">
                            <img src="~/img/search-icon.png" style="width:30px;height:30px" />
                        </div>
     
                        <div class="form-group">
                            <input class="form-control" type="text" id="id" placeholder="ID" data-toggle="tooltip" title="ID">
                        </div>
     
     
     
                        <div class="form-group">
                            <input class="form-control" type="text" list="get_item_code" id="item" placeholder="Item" data-toggle="tooltip" title="Item">
                        </div>
     
                      
                    </form>
                </div>
     
     
                <div style="width:998px;height:40px">
                </div>
                <table style="width:998px">
                    <tr>
                        <td style="border-bottom:1px solid lightgray;height:10px">
                            <div style="margin-top:-35px;margin-left:10px">
                                <input class="btn" id="button_add_filter" type="submit" value="Add Filter" style="color:gray;background-color:#efefef;font-size:9pt" />
                            </div>
                        </td>
                    </tr>
                </table>
     
     
                <table border="0" style="width:998px;display:none" id="table_filter">
                    <tr style="height:50px">
     
                        <td style="border-bottom:1px solid lightgray;height:30px">
                            <datalist id="list1"></datalist>
                            <div style="margin-left:10px">
                                <input class="form-control" id="column" list="list1" type="text" style="width:230px;height:30px">
                            </div>
                        </td>
     
     
     
                        <td style="border-bottom:1px solid lightgray;height:10px">
                            <div>
                                <select class="form-control" id="operator" style="width:150px;height:30px" data-toggle="tooltip" title="Order Type">
                                    <option></option>
                                    <option>Equals</option>
                                    <option>Like</option>
                                    <option>In</option>
                                    <option>Not In</option>
                                    <option>Not Equals</option>
                                    <option>Not Like</option>
                                    <option>></option>
                                    <option><</option>
                                    <option>>=</option>
                                    <option><=</option>
                                    <option>Between</option>
     
                                </select>
                            </div>
                        </td>
     
                        <td style="border-bottom:1px solid lightgray;height:10px">
                            <datalist id="get_series"></datalist>
                            <datalist id="get_col_name"></datalist>
     
                            <input class="form-control" id="value" type="text" list="get_col_name" style="width:230px;height:30px">
                        </td>
     
                        <td style="border-bottom:1px solid lightgray;height:10px">
     
                            <input class="btn btn-primary" id="apply" type="submit" value="Apply" style="font-size:9pt" />
                        </td>
     
                        <td style="border-bottom:1px solid lightgray;height:10px">
     
                            <input class="btn" type="submit" id="remove" value="Remove" style="color:gray;background-color:#efefef;font-size:9pt" />
                        </td>
                    </tr>
                </table>
     
            </div>
        </div>
        <br />
        <table id="example" class="table table-hover display" style="width:100%;cursor:pointer">
            <thead>
     
            </thead>
            <tfoot>
                <tr></tr>
            </tfoot>
        </table>
        <br><br>
     
    </div>
    <br /><br />
    <br /><br />
     
    <br /><br />
    <br />
    <datalist id="get_item_code"></datalist>
    <input type="hidden" id="current_date">
    <script>
     
        $('#referesh').click(function () {
            location.reload()
     
        });
     
     
        var d = new Date();
        // Set it to one month ago
        d.setMonth(d.getMonth() - 1);
        // Zero the hours
        d.setHours(0, 0, 0);
        // Zero the milliseconds
        d.setMilliseconds(0);
        $("#current_date").datepicker("setDate"new Date());
     
     
        $('#button_add_filter').click(function () {
            $('#table_filter').show();
     
        });
     
        $('#remove').click(function () {
            $('#table_filter').hide();
     
        });
     
        function ToDatatable() {
     
            var Arr = [];
            var searches = "";
            $.ajax({
                type: "GET",
                url: "/Batch/getColumns",
                data: {
                    id: $("#id").val(),
                    item: $("#item").val(),
                    type: "GET",
     
                },
                success: function (response) {
                    searches += "<tr>";
                    data = $.map(response, function (item, a) {
                        Arr.push({ data: item.variable, title: item.variable });
                        //   searches += "<td><input style='width:100%' type='text' id=" + item.variable + " class='form-control' placeholder='Search " + item.variable + "'/></td>";
                    });
                    //  searches += "</tr>";
                },
                dataType: "json"
            }).done(function () {
                $('#example').append(searches);
                var sample = $('#example').DataTable({
                    "language": {
                        "zeroRecords""Nothing found - sorry",
                        "emptyTable""No data Available.",
                        "loadingRecords""Loading...",
                        "processing""Processing...",
                        "lengthMenu""Display _MENU_ records per page",
                        // "info": "Showing page _PAGE_ of _PAGES_",
                        "infoFiltered""(filtered from _MAX_ total records)",
                        // "sDom": 'Rfrtlip',
                    },
     
                    "dom"'<"top"f>rt<"bottom"ilp><"clear">',
                    "ajax": {
                        "processing"true,
                        "serverSide"true,
                        "url""/Batch/ConvertDataTabletoString",
                        "data": {
                            "id": $("#id").val(),
                            "item": $("#item").val(),
                            "type""GET",
     
                        },
                        "dataSrc""",
                        "deferRender"true,
     
     
                    },
                    "columns": Arr,
                    "sortable"true,
                    "bFilter"true,
                    "scrollX"true,
                    "searching"false,
                    "bInfo"false,
                    "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                    "columnDefs": [
                        //{
                        //      "render": function (data, type, row) {
                        //                var dateSplit = data;
                        //               return  type === "display" || type === "filter" ?
                        //                   dateSplit.substring(0, 10) : data;
     
     
     
                        //            },
                        //    "targets": 2,
                        //},
     
     
     
                          {
                           "render"function (data, type, row) {
                               var date = data.split("/");
                               var date2=($("#current_date").val()).split("/")
                               date = new Date(parseInt(date[2]), parseInt(date[1])-1, parseInt(date[0]));
                               date2 = new Date(parseInt(date[2]), parseInt(date[1]) - 1, parseInt(date[0]));
     
                               if (date < date2) {
                                   return "expired";
                               } else{
                                   return "not expired";
                               }
     
     
                           },
                           "targets": 2,
                       },
     
                    ]
                });
     
                var row;
                for (var i = 0; i < Arr.length; i++) {
     
                    row += "<option>" + Arr[i].title + "</option>";
                }
                $("#list1").append(row);
            });
     
            //$("#example").off("click", "tr").on("click", "tr", function () {
     
            //    var series = $(this).find("td:eq(1)").text();
            //    window.open("edit_Terms_and_Conditions_Template?series=" + series + "", "_self");
     
            //});
     
     
            var rowlist = [];
            $("#example").off("click""tr").on("click""tbody tr"function () {
                // alert($(this).index());
                var series = $(this).find("td:eq(0)").text();
     
                window.open("edit_batch?series=" + series + """_self");
     
            });
     
        }
    //----------------------------------------------------------------------
        function ToDatatable2() {
     
            var Arr = [];
            var searches = "";
            $.ajax({
                type: "GET",
                url: "/Batch/getColumns2",
                data: {
                    Value: $("#value").val(),
                    operators: $("#operator").val(),
                    ColumnName: $("#column").val(),
     
                    type: "GET",
     
                },
                success: function (response) {
                    searches += "<tr>";
                    data = $.map(response, function (item, a) {
                        Arr.push({ data: item.variable, title: item.variable });
                        //   searches += "<td><input style='width:100%' type='text' id=" + item.variable + " class='form-control' placeholder='Search " + item.variable + "'/></td>";
                    });
                    //  searches += "</tr>";
                },
                dataType: "json"
            }).done(function () {
                $('#example').append(searches);
                var sample = $('#example').DataTable({
                    "language": {
                        "zeroRecords""Nothing found - sorry",
                        "emptyTable""No data Available.",
                        "loadingRecords""Loading...",
                        "processing""Processing...",
                        "lengthMenu""Display _MENU_ records per page",
                        // "info": "Showing page _PAGE_ of _PAGES_",
                        "infoFiltered""(filtered from _MAX_ total records)",
                        // "sDom": 'Rfrtlip',
                    },
     
                    "dom"'<"top"f>rt<"bottom"ilp><"clear">',
                    "ajax": {
                        "processing"true,
                        "serverSide"true,
                        "url""/Batch/ConvertDataTabletoString2",
                        "data": {
                            "Value": $("#value").val(),
                            "operators": $("#operator").val(),
                            "ColumnName": $("#column").val(),
     
     
                            type: "GET",
     
                        },
                        "dataSrc""",
                        "deferRender"true,
     
     
                    },
                    "columns": Arr,
                    "sortable"true,
                    "bFilter"true,
                    "scrollX"true,
                    "searching"false,
                    "bInfo"false,
                    "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                    "columnDefs": [
                        {
                              "render"function (data, type, row) {
                                        var dateSplit = data;
                                       return  type === "display" || type === "filter" ?
                                           dateSplit.substring(0, 10) : data;
     
     
     
                                    },
                            "targets": 2,
                        },
     
     
     
                         @*{
                             "render": function (data, type, row) {
     
                                 switch (data) {
                                     case $("#current_date").val(): return '&nbsp;<img src="@Url.Content("~/img/red.png")" style="width:20px;height:20px;margin-left:10px">&nbsp;&nbsp Expired'; break;
                                     default: return '&nbsp;<img src="@Url.Content("~/img/green.png")" style="width:8px;height:8px;margin-left:10px">&nbsp;&nbsp Not Expired';
                                 }
     
                                 var dateSplit = data;
                                 return type === "display" || type === "filter" ?
                                dateSplit.substring(0, 10) : data;
     
                             },
                             "targets": 2,
                         },*@
     
                    ]
                });
     
            });
     
     
        }
    ///==================================================================
        $("#apply").on("click"function () {
     
            if ($.fn.dataTable.isDataTable('#example')) {
                $('#example').DataTable().destroy();
                $('#example').empty();
                ToDatatable2();
     
            }
            else {
                ToDatatable2();
     
            }
     
        });
     
     
        $("#column").on("change"function () {
            $.get("/Batch/Get_batch_Data", {
     
                variable: $("#column").val(),
     
            }, function (data) {
                var row;
                $.each(data, function (i, v) {
                    row += "<option>" + v.col_name + "</option>";
                });
     
                $("#get_col_name").empty();
                $("#get_col_name").append(row);
            });
        });
     
     
     
    //---------------------------------------------------------------------
        $("#example").on('keyup''input'function () {
            $('#example').DataTable().columns($(this).parent().index()).search(this.value).draw();
        });
        $(window).on("load"function () {
     
     
            if ($.fn.dataTable.isDataTable('#example')) {
                $('#example').DataTable().destroy();
                $('#example').empty();
                ToDatatable();
            }
            else {
                ToDatatable();
            }
     
     
        });
     
     
        $("#id").on("change"function () {
     
            if ($.fn.dataTable.isDataTable('#example')) {
                $('#example').DataTable().destroy();
                $('#example').empty();
                ToDatatable();
     
            }
            else {
                ToDatatable();
     
            }
     
        });
     
        $("#item").on("change"function () {
     
            if ($.fn.dataTable.isDataTable('#example')) {
                $('#example').DataTable().destroy();
                $('#example').empty();
                ToDatatable();
     
            }
            else {
                ToDatatable();
     
            }
     
        });
     
        //list of company
        $.get("/AutoComplete/Get_item_code", {
        }, function (data) {
            var row;
            $.each(data, function (i, v) {
                row += "<option>" + v.item_code + "</option>";
            });
     
            $("#get_item_code").append(row);
        });
     
     
     
        $("#stock").on("click"function () {
            window.location.href = '@Url.Action("StockItem""StockItem")';
     
        });
     
        $("#logo_erp").on("click"function () {
            window.location.href = '@Url.Action("Main""Main")';
     
        });
     
        $.get("/AutoComplete/Get_Material_Request_Series", {
        }, function (data) {
            var row;
            $.each(data, function (i, v) {
                row += "<option>" + v.series + "</option>";
            });
     
            $("#get_series").append(row);
        });
    </script>
     
     
     
     
    
    controller
    public String ConvertDataTabletoString(String id, String item)
         {
             getColumns(id, item);
             JavaScriptSerializer serializer = new JavaScriptSerializer();
             return serializer.Serialize(rows);
         }
     
         public JsonResult getColumns(String id, String item)
         {
             List<dummy> listreg = new List<dummy>();
             SqlCommand cmd = new SqlCommand("[dbo].[show_batch_search]", con);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.AddWithValue("@id", id);
             cmd.Parameters.AddWithValue("@item", item);
     
     
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             adapter.Fill(dt);
             foreach (DataColumn dr in dt.Columns)
             {
                 listreg.Add(new dummy
                 {
                     variable = dr.ColumnName.ToString(),
                 });
             }
             Dictionary<stringobject> row;
             foreach (DataRow dr in dt.Rows)
             {
                 row = new Dictionary<stringobject>();
                 foreach (DataColumn col in dt.Columns)
                 {
                     row.Add(col.ColumnName, dr[col].ToString());
                 }
                 rows.Add(row);
             }
             return Json(listreg, JsonRequestBehavior.AllowGet);
         }
         class dummy
         {
             public string variable { getset; }
         }
     
    
    Monday, October 22, 2018 4:20 AM
  • User-893317190 posted

    Hi chnar,

    I have highlighted my code which is different with yours.Please have a look and change your code.

       function ToDatatable() {
    
          var Arr = [];
          var searches = "";
          $.ajax({
              type: "GET",
              url: "/DataTable/getColumns",
    
              success: function (response) {
                  searches += "<tr>";
                  data = $.map(response, function (item, a) {
                      Arr.push({ data: item.variable, title: item.variable });
                      //   searches += "<td><input style='width:100%' type='text' id=" + item.variable + " class='form-control' placeholder='Search " + item.variable + "'/></td>";
                      searches += "<th>" + item.variable + "</th>";
                  });
                    searches += "</tr>";
              },
              dataType: "json"
          }).done(function () {
              $('#example thead').html(searches);
              var sample = $('#example').DataTable({
                  "language": {
                      "zeroRecords": "Nothing found - sorry",
                      "emptyTable": "No data Available.",
                      "loadingRecords": "Loading...",
                      "processing": "Processing...",
                      "lengthMenu": "Display _MENU_ records per page",
                       "info": "Showing page _PAGE_ of _PAGES_",
                      "infoFiltered": "(filtered from _MAX_ total records)",
                       "sDom": 'Rfrtlip',
                  },
    
                  "dom": '<"top"f>rt<"bottom"ilp><"clear">',
                  "ajax": {
                      "processing": true,
                      "serverSide": true,
                      "url": "/DataTable/ConvertDataTabletoString",
                      
                      "dataSrc": "",
                      "deferRender": true,
    
    
                  },
                  "columns": Arr,
                  "sortable": true,
                  "bFilter": true,
                  "scrollX": true,
                  "searching": false,
                  "bInfo": false,
                  "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                  "columnDefs": [
                   
    
                       {
                           "render": function (data, type, row) {
                               var date = data.split("/");
                             
                               date = new Date(parseInt(date[0]), parseInt(date[1])-1, parseInt(date[2]));
                              
                               if (date < new Date()) {
                                   return "expired";
                               } else{
                                   return "not expired";
                               }
                              
    
                           },
                           "targets": 1,
                       },
    
                  ]
              });
    
            
          });
    
    
      }
    <table id="example" class="table table-hover display" style="width:100%;cursor:pointer">
        <thead>
    
        </thead>
        
    </table>

    Controller.The method  ConvertDataTabletoString should return json of format [{column1:"11",column2:"1993/12/11",column3:"13},{column1:"11",column2:"1994/12/11",column3:"13"}],please check the format of your returned json string. Please convert your datatable to the json of this format.

    I also suggest you could use  SqlDataReader to get your data and  convert the data got from SqlDataReader to json, it will be easier and  more clear.

     public JsonResult getColumns() {
    
                ArrayList array = new ArrayList();
                array.Add(new { variable = "column1" } );
                array.Add( new { variable = "column2" });
                array.Add(new { variable = "column3" });
                return Json(array, JsonRequestBehavior.AllowGet);
    
            }
    
            public JsonResult ConvertDataTabletoString()
            {
                ArrayList array = new ArrayList();
                array.Add(new {column1="11",column2="2020/1/1",column3="13" });
                array.Add(new {column1= "21", column2 = "2018/10/6", column3 = "23" });
                array.Add(new {column1="31",column2="2018/12/4",column3="33" });
                return Json(array, JsonRequestBehavior.AllowGet);
            }

    Please debug using your F12 developer tool, if you don't know how to use it , please refer to https://developers.google.com/web/tools/chrome-devtools/javascript/

    https://developers.google.com/web/tools/chrome-devtools/network-performance/

    Best regards,

    Ackerly Xu

    Monday, October 22, 2018 10:05 AM