fill HTML table with JQuery DataTable library with BD records

Question

fill HTML table with JQuery DataTable library with BD records

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm doing a field edit through JQuery's DataTable library, the problem is that I have to feed the table through data I get from a DB, and I have not been able to feed that table through the library, What I want is to do something like this (examples taken from the DataTables page)

and edit the record

I have my html table already configured with the library in this way and I think it works perfectly

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://editor.datatables.net/extensions/Editor/css/editor.dataTables.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

<script src="~/Scripts/dataTables_editor.js"></script>
<script type="text/javascript">

$(document).ready(function () {
    var editor; // use a global for the submit and return data rendering in the examples
    editor = new $.fn.dataTable.Editor({
        dom: "Tfrtip",
        "ajax": "adendum1",
        "table": "#example",
        "fields": [{
            "label": "First name:",
            "name": "first_name"
        }, {
            "label": "Last name:",
            "name": "last_name"
        }, {
            "label": "Position:",
            "name": "position"
        }, {
            "label": "Office:",
            "name": "office"
        }, {
            "label": "Extension:",
            "name": "extn"
        }, {
            "label": "Start date:",
            "name": "start_date",
            "type": "datetime"
        }, {
            "label": "Salary:",
            "name": "salary"
        }
        ]
    });

    // New record
    $('a.editor_create').on('click', function (e) {
        e.preventDefault();

        editor.create({
            title: 'Create new record',
            buttons: 'Add'
        });
    });

    // Edit record
    $('#example').on('click', 'a.editor_edit', function (e) {
        e.preventDefault();

        editor.edit($(this).closest('tr'), {
            title: 'Edit record',
            buttons: 'Update'
        });
    });

    // Delete a record
    $('#example').on('click', 'a.editor_remove', function (e) {
        e.preventDefault();

        editor.remove($(this).closest('tr'), {
            title: 'Delete record',
            message: 'Are you sure you wish to remove this record?',
            buttons: 'Delete'
        });
    });

    $('#example').DataTable({
        dom: "Tfrtip",
        ajax: "adendum1",
        //data: function (data) { return data = JSON.stringify(data); },
        columns: [

            
            {
                data: null, render: function (data, type, row) {
                    // Combine the first and last names into a single table field
                    return data.first_name + ' ' + data.last_name;
                }
            },
            { data: "position" },
            { data: "office" },
            { data: "extn"},
            { data: "start_date"},
            { data: "salary"},
            {
                data: null,
                className: "center",
                defaultContent: '<a href="" class="editor_edit">Edit</a> / <a href="" class="editor_remove">Delete</a>'
            }
        ]
    });
});
</script>

<div>

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th></th>
            <th>Position</th>
            <th>Office</th>
            <th>Extn.</th>
            <th>Start date</th>
            <th>Salary</th>
            <th>Edit / Delete</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th></th>
            <th>Position</th>
            <th>Office</th>
            <th>Extn.</th>
            <th>Start date</th>
            <th>Salary</th>
            <th>Edit / Delete</th>
        </tr>
    </tfoot>
</table>
</div>

The page where you must get the data to fill the DataTable is addendum1 and this is my code, I think that's where I have the problem, to fill, I do not know how to retrieve the records that the page returns, this is my code

@Code 
Dim db = Database.Open("Factura1") 
Dim muestraAdendum = db.Query("SELECT*FROM EMPRESARIALES.ADENDA WHERE CUENTA_PADRE IS NULL") 
End Code

{
  "data": [

@Code For Each item In muestraAdendum End Code
{
  "DT_RowId": "row_1",
  "first_name": "Tiger",
  "last_name": "Nixon",
  "position": "System Architect",      
  "office": "Edinburgh",
  "extn": "5421",      
  "salary": "320800",
  "start_date": "2011-04-25"
}
@Code If Not item is Nothing Then End Code
       ,   
@Code End If       
  Next
End Code

 ],
  "options": [],
  "files": []
}

Note that when I remove the For Each there is no problem to load the data from the page adendum1 in the dataTable, but what I need is with the for each since from there I get the data from the database to fill the dataTable

* The page of adendum1 is a bit strange since I use web pages from webmatrix

jquery datatables

asked by Ivxn 11.11.2016 в 16:19

source

2 answers

0

I did something similar by replicating a dynamic of angular.js which is the famous "ng-repet" and more or less it is like this:

var $g = {};

$g.datos = [
  {"id": 1,
  "cuenta": "cuenta no. 1",
  "razon": "razon social 1"},
  {"id": 2,
  "cuenta": "cuenta no. 2",
  "razon": "razon social 2"},
  {"id": 3,
  "cuenta": "cuenta numero 3",
  "razon": "razon numero 3"},
  {"id": 4,
  "cuenta": "Titulo",
  "razon": "Hola mundo"}
];


$g.cargar_db = function () {
  var Repeat = $('[repeat]'),
      Tpl;
  
  $.each($g.datos, function (index, value) {
      Tpl = Repeat.clone();
      Tpl.find("*:contains({{ID}})").text(value.id);
      Tpl.find("*:contains({{CUENTA}})").text(value.cuenta);
      Tpl.find("*:contains({{RAZON_SOCIAL}})").text(value.razon);
      
      Repeat.after(Tpl);
  });
  Repeat.remove();
};

//Document ready
$(document).ready(function () {
    $g.cargar_db();
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
      <tr>
        <th>ID</th>
        <th>CUENTA</th>
        <th>RAZON SOCIAL</th>
        <th>Edit / Delete</th>
      </tr>
    </thead>

    <tfoot>
      <tr repeat>
        <th>{{ID}}</th>
        <th>{{CUENTA}}</th>
        <th>{{RAZON_SOCIAL}}</th>
        <th>Edit / Delete</th>
      </tr>
    </tfoot>
  </table>

You can use an "apirest" to obtain the data with AJAX as in your example and it works in the same way.

I hope it works for you. Greetings.

answered by 11.11.2016 в 17:18

Entity framework directives How to save a copy of an image when using gd php library

score 0 · Accepted Answer

The solution I have made for this case is that on the page adendum1, the JSON string that was forming was wrong, because at the end of each {"variable": "value"} a comma must go, so I made a comparison so that the last record does not have a comma just the key or bracket and it's like this:

{
      "data":  [
            @Code 
                Dim i = 1
                For Each item In muestraAdendum
            End Code
{
        "DT_RowId":"row_@i",
        "first_name":"xxx",
        "last_name":"aaa",
        "position":"@item("ID")",
        "email":"@item("CUENTA")",
        "office":"@item("ICCID")",
        "extn":"@item("TELEFONO")",
        "age":"@item("IMEI")",
        "salary":"@item("EQUIPO")",
        "start_date":"@item("ID")"
@Code If i <> muestraAdendum.Count Then End code
},
 @code Else end code
} 
@code            
          End If
End Code
@Code                    
    i = i + 1
    Next
End Code

],
  "options": [],
  "files": []
}