Datatable similar to .NET datagridview

Graynoble

Hi all,

Unusual question. I hope someone can assist.

I am trying to decide how best to use Datatables (the Editor specifically) with inline editing. In VB.NET I can create a datagridview. I can add, remove and update all within that datagridview. Then, when I want to send the data to SQL, I can do it in bulk (using bulkcopy) or iterating through the records to inject the data row by row,into a SQL table.
I want to do something similar with Datatables and/or the Editor.

What I want to do but don't know if it is possible:

1) Start with empty Editor
2) Using an "Add" button, add a blank row (or have the editor come up with a new row (row 1) with empty cells
3) Using inline editing, the user modifies all the columns they need
4) When the user types in any of the initial fields on row 1, a new second row, is created, empty and waiting on input
5) Repeat until all data is entered for 1, 2, 10 or 20 rows
6) Upload all data from table to SQL via AJAX (this is the only time AJAX is called/used)

Is this something that can be done with the Editor?

Kind Regards,
Jason

allan

Yes - that can be done. It will need some use of the API since it doesn't work that way out of the box, but what I would suggest is that you need a local editing table and call the create() method to create a new row (submit immediately by calling submit() so there is an empty row in the table). Then use inline editing as normal.

You can add another row to the table at any time by calling create() and submit() again.

Finally use rows().data() to get the data from the table and you can then Ajax submit that to the server.

Allan

Graynoble

Hi Allan!

Thank you for the quick response. I will work with your suggestion and everyone know how it goes!

Regards,
Jason

Graynoble

Hi Allan,

Well I was able to get it working (more or less!). Currently, when I move from the dropdown column to another column, the value reverts back to default. All other columns work properly that do not have the dropdown.

Additionally, when I select "New" and fill out the form, the dropdown populates in the form, but I get the error :

Datatables warning: table id=example - Requested unknown parameter 'material.name' for row 3, column 3. For more information about this error, please see http://datatables.net/tn/4

Here is my JSON:

{
    "recordsTotal": 5,
    "recordsFiltered": 5,
    "data": [{
        "DT_RowId": "row_1",
        "id": 1,
        "item_number": 0,
        "quantity": 0,
        "mats": 100000020,
        "material": {
            "name": "16N80134316"
        },
        "unitprice": 0,
        "netvalue": 0
    }],
    "options": {
        "mats": [{
            "label": "16N80134315",
            "value": 100000019
        }, {
            "label": "16N80134316",
            "value": 100000020
        }, {
            "label": "16N80134317",
            "value": 100000021
        }, {
            "label": "16N80134318",
            "value": 100000022
        }, {
            "label": "809A06CA0A",
            "value": 100000052
        }]
    }
}

Here is my Editor javascript code:

    editor = new $.fn.dataTable.Editor({
        table: "#example",
        fields: [{
            label: "Item Number:",
            name: "item_number"
        }, {
            label: "Quantity:",
            name: "quantity"
        }, {
            label: "Material:",
            name: "mats",
            type: "select"
        }, {
            label: "Unit Price:",
            name: "unitprice"
        }, {
            label: "Net Value:",
            name: "netvalue"
        }
        ]
    });

    // Activate an inline edit on click of a table cell
    $('#example').on('click', 'tbody td:not(:first-child)', function (e) {
        editor.inline(this, {
            onBlur: 'submit'
        });
    });

    $('#example').DataTable({
        dom: 'Brtip',
        ajax: {
            url: $("#base-directory").val() + "Ajax/GetOptionsData",
            type: "POST",
            data: function (d) {
                d.id = "",
                d.data_mode = 'Materials';
            }
        },
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            { data: "item_number" },
            { data: "quantity" },
            {
                data: "material.name", editField: "mats"
            },
            { data: "unitprice", render: $.fn.dataTable.render.number(',', '.', 2, '$') },
            { data: "netvalue", render: $.fn.dataTable.render.number(',', '.', 2, '$') }
        ],
        order: [1, 'asc'],
        select: {
            style: 'os',
            selector: 'td:first-child'
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ],
    });

I have been working on this since yesterday and cannot seem to get past this issue. I know I am just missing something simple, but my eyes are glazing over.

Graynoble

I have more! So I was able to cut down on my JSON to just the basics, I am now populating the dropdown and it is keeping the data when I move to another column, however it is not choosing the label, it is choosing the value when it does.

I'll continue working on this tonight.

allan

There is no mats data point in your JSON data, so no value can be populated into it. It looks like you actually want to use material.id but you'd need to add the id (or whatever that column is called) into your material object. Its missing that bit of information at the moment.

Allan

Graynoble

If you don't mind, could you please clarify your previous message? I trimmed down the code a bit below and everything is working except it is displaying the value instead of the label when calling the submit on cell transition:

Code:

    editor = new $.fn.dataTable.Editor({
        table: "#example",
        fields: [{
            label: "Item Number:",
            name: "item_number"
        }, {
            label: "Quantity:",
            name: "quantity"
        }, {
            label: "Material:",
            name: "materials",
            type: "select"
        }, {
            label: "Unit Price:",
            name: "unitprice"
        }, {
            label: "Net Value:",
            name: "netvalue"
        }
        ]
    });

    // Activate an inline edit on click of a table cell
    $('#example').on('click', 'tbody td:not(:first-child)', function (e) {
        editor.inline(this, {
            onBlur: 'submit'
        });
    });

    $('#example').DataTable({
        dom: 'Brtip',
        ajax: {
            url: $("#base-directory").val() + "Ajax/GetDataTableOptionsData",
            type: "POST",
            data: function (d) {
                d.id = "",
                d.data_mode = 'Materials';
            }
        },
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            { data: "item_number" },
            { data: "quantity" },
            {
                data: "materials"
            },
            { data: "unitprice", render: $.fn.dataTable.render.number(',', '.', 2, '$') },
            { data: "netvalue", render: $.fn.dataTable.render.number(',', '.', 2, '$') }
        ],
        order: [1, 'asc'],
        select: {
            style: 'os',
            selector: 'td:first-child'
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ],
    });

JSON

{
    "data": [{
        "DT_RowId": "row_1",
        "id": 1,
        "item_number": 0,
        "quantity": 0,
        "materials": "16N80134316",
        "unitprice": 0,
        "netvalue": 0
    }],
    "options": {
        "materials": [{
            "label": "16N80134315",
            "value": 100000019
        }, {
            "label": "16N80134316",
            "value": 100000020
        }]
    }
}

Graynoble

Hi again Allan,

I am not sure if this is the best way to do this. I created a dataSrc object (optlist) of the options list. In the render for the column I parsed that object and found a match for the column data. Then return the .label from the object.

The object creation for "whatever"

            dataSrc: function (json) {
                optlist = json.options
                return json.data;
            }

The column render

            {
                data: "materials",
                render: function(data) {
                    for (var i = 0, ien = optlist.materials.length ; i < ien ; i++) {
                        if (optlist.materials[i].value === data) {
                            return optlist.materials[i].label;
                        }
                    }
                }
            },

tangerine

Don't you need to return something in the event that optlist.materials[i].value != data ?

Graynoble

Ah good point tangerine, I would just return (data) at that point.

allan

The looks like a good workaround - nice one.

Allan

garciazajo

good afternoon can someone please pass me an example of a table on vb.net please thank you very much greetings