locked
how to put condition on column datatable RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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 {
        color: gray;
    }
 
    .dataTables_length {
        color: gray;
    }
 
    table.dataTable th {
        color: gray;
        background-color: #eff3f9;
        font-weight: bold;
        font-family: 'Century Gothic';
        white-space: nowrap;
        font-size: 14px;
    }
 
    table.dataTable tbody tr td {
        /*background-color: #ffffff;*/
        font-size: 14px;
        /*color:rebeccapurple;*/
        font-family: Bahnschrift SemiBold;
        white-space: nowrap;
    }
 
 
    .div_item_header {
        width: 1355px;
        height: 70px;
        border-style: solid;
        border-width: 0px 0px 1px 1px;
        border-color: lightgray;
        margin-left: -9px;
        margin-top: -10px;
    }
 
    .div_content_border {
        width: 950px;
        border-style: solid;
        border-width: 0px 1px 1px 1px;
        border-color: lightgray;
        margin-left: 200px;
    }
 
 
 
    .account_title {
        font-size: 25px;
        margin-left: 620px;
        font-weight: normal;
    }
 
    .label_content {
        font-weight: bold;
    }
 
 
 
    a:link {
        text-decoration: none;
        color: black;
    }
 
    a:visited {
        text-decoration: none;
        color: black;
    }
 
    a:hover {
        text-decoration: underline;
        color: black;
    }
 
    a:active {
        text-decoration: underline;
        color: black;
    }
 
    .width_ListTextbox {
        width: 50px;
    }
 
 
    /* Style Checkbox */
    .contain {
        display: block;
        position: relative;
        padding-left: 25px;
        padding-bottom: 12px;
        cursor: pointer;
        font-size: 13px;
        font-family: arial;
        font-weight: normal;
        -webkit-user-select: none;
        -moz-user-select: none;
        -ms-user-select: none;
        user-select: none;
    }
 
        /* Hide the browser's default checkbox */
        .contain input {
            position: absolute;
            opacity: 0;
            cursor: pointer;
        }
 
    /* Create a custom checkbox */
    .checkmark {
        position: absolute;
        top: 0;
        left: 0;
        height: 15px;
        width: 15px;
        background-color: #eee;
        border-radius: 3px;
    }
 
    /* 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: "";
        position: absolute;
        display: none;
    }
 
    /* Show the checkmark when checked */
    .contain input:checked ~ .checkmark:after {
        display: block;
    }
 
    /* Style the checkmark/indicator */
    .contain .checkmark:after {
        left: 5px;
        top: 3px;
        width: 4px;
        height: 7px;
        border: solid white;
        border-width: 0 2px 2px 0;
        -webkit-transform: rotate(45deg);
        -ms-transform: rotate(45deg);
        transform: rotate(45deg);
    }
 
 
 
 
    /* Accordion*/
 
 
    .accordion {
        background-color: none;
        color: none;
        cursor: pointer;
        padding: 18px;
        width: 100%;
        border: none;
        text-align: left;
        outline: none;
        font-size: 15px;
        transition: 0.4s;
    }
 
    .search {
        background-image: url('../../img/search-icon.png');
        background-repeat: no-repeat;
    }
 
    a:hover {
        color: black;
    }
 
    .numberCircle {
        border-radius: 20%;
        behavior: url(PIE.htc); /* remove if you don't care about IE8 */
        width: 27px;
        height: 25px;
        padding: 4px;
        background: red;
        border: 2px solid red;
        color: white;
        text-align: center;
        font-weight: bold;
        font: 12px Arial, sans-serif;
    }
 
    .erp {
        border-radius: 20%;
        behavior: url(PIE.htc); /* remove if you don't care about IE8 */
        width: 30px;
        height: 30px;
        padding: 4px;
        margin-top: -26px;
        margin-left: 100px;
        background: #5f2b9b;
        border: 2px solid white;
        color: white;
        text-align: center;
        font-weight: bold;
        font: 15px Arial, sans-serif;
    }
 
    a:hover {
        color: black;
        text-decoration: none;
    }
 
    a {
        color: darkgray;
    }
</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-color: white;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-color: white;width:2050px;border-bottom:1px solid #eaeaed"></td>
 
 
            <td style="background-color: white;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-color: white;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-color: white">
                <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:auto; width: 1000px; margin-left: 170px; margin-top: -1px; ">
 
    <div class="container">
        <div class="row">
 
            <div style="background-color:#efefef;width:998px; height: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<string, object> row;
         foreach (DataRow dr in dt.Rows)
         {
             row = new Dictionary<string, object>();
             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 { get; set; }
     }
    Monday, October 22, 2018 4:20 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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