Creating new DB record with joined tables

CMOFNL · February 19

In an ASP.Net MVC 5 project, I am trying to get Datables Editor (v2.0.5) to create a new record when the table is based on a two joined tables.

My table Has Employee details and also diplays the Department for that Employee.

When creating a new Employee I want to use a Dropdown in the Create form to select a Department.

If I just have the Employee details in the table then the new record creates OK, but with a joined table nothing is created.

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
[ValidateAntiForgeryToken()]
public ActionResult GetServices()
{
    var formData = HttpContext.Request.Form;

    using (var db = new Database("mysql", _dbFactory.CreateConnection(ConnectionDatabase.CustomerSpecific)))
    {
        var editor = new Editor(db, "customerspecific.dt_employee", "id")
            .Field(new Field("dt_employee.id").Set(true))
            .Field(new Field("dt_employee.firstName").Set(true))
            .Field(new Field("dt_employee.lastName").Set(true))
            .Field(new Field("dt_employee.deptId").Set(true)
                .Options(new Options()
                    .Table("dt_department")
                    .Value("id").Label("deptName")
                    .Order("id")))
            .LeftJoin("dt_department", "dt_employee.deptId", "=", "dt_department.id");

        var response = editor.Transaction(true).Debug(true).Process(formData).Data();

        return Json(response, JsonRequestBehavior.AllowGet);
    }
}

This is my jQuery to build the table and editor on the page.

var table = $('#tblSC').DataTable({
        ajax:
            {
                "url": "@Url.Action("GetServices", "ServiceCatalogue", new { boid = 1 })",
                "type": "POST",
                "data": function (d) {
                    d.__RequestVerificationToken = $('input[name=__RequestVerificationToken]').val();
                },
            },
            serverSide: true,
            searching: true,
            orderable: true,
            processing: true,
            dom: 'lBfrtip',
            select: true,
            columns: [
                { "data": "dt_employee.id", "searchable": true},
                { "data": "dt_employee.firstName", "searchable": true},
                { "data": "dt_employee.lastName", "searchable": true},
                { "data": "dt_employee.deptId", render: function ( data, type, row ) {
                                                                        return row.dt_department.deptName;
                                                                }, "searchable": true },
            ],
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit", editor: editor },
                { extend: "remove", editor: editor },
            ]
        });

        console.log('Creating Editor');
        editor = new $.fn.dataTable.Editor({
            "ajax": {
                "url": "@Url.Action("GetServices", "ServiceCatalogue", new { boid = 1 })",
                "type": "POST",
                "data": function (d) {
                    d.__RequestVerificationToken = $('input[name=__RequestVerificationToken]').val();
                }
            },
            table: "#tblSC",
            fields: [
                {
                    name: "dt_employee.id",
                    type: 'hidden'
                },
                {
                    name: "dt_employee.firstName",
                },
                {
                    name: "dt_employee.lastName",
                },
                {
                    label: "Department:",
                    name: "dt_employee.deptId",
                    type: "select",
                    placeholder: "--- select a department ---"
                }
            ]
        });

What am I missing ? Thanks.

allan · February 19

Nothing obvious from what I can see. I'm wondering if the schema name in the second parameter of the Editor constructor might have an impact, but if it is working without the deptId field, then I don't see why adding it would make any difference.

I see you have the .Debug() option enabled - could you show me the Ajax response from the server when this error happens please? The SQL in the debug might give a clue as to what is going on.

Allan

CMOFNL · February 20

Hi, it seems to be empty

{
    "draw": null,
    "data": [],
    "recordsTotal": null,
    "recordsFiltered": null,
    "error": null,
    "fieldErrors": [],
    "id": null,
    "meta": {},
    "options": {},
    "searchPanes": {
        "options": {}
    },
    "files": {},
    "upload": {
        "id": null
    },
    "debug": [],
    "cancelled": []
}

CMOFNL · February 20

This is the response when the table loads

{
    "draw": 2,
    "data": [
        {
            "DT_RowId": "row_1",
            "dt_employee": {
                "id": "1",
                "firstName": "John",
                "lastName": "Smith",
                "deptId": "1"
            },
            "dt_department": {
                "deptName": "HR"
            }
        },
        {
            "DT_RowId": "row_2",
            "dt_employee": {
                "id": "2",
                "firstName": "Dave",
                "lastName": "Jones",
                "deptId": "2"
            },
            "dt_department": {
                "deptName": "IT"
            }
        }
    ],
    "recordsTotal": 2,
    "recordsFiltered": 2,
    "error": null,
    "fieldErrors": [],
    "id": null,
    "meta": {},
    "options": {
        "dt_employee.deptId": [
            {
                "value": 1,
                "label": "HR"
            },
            {
                "value": 2,
                "label": "IT"
            },
            {
                "value": 3,
                "label": "Finance"
            }
        ]
    },
    "searchPanes": null,
    "files": {},
    "upload": {
        "id": null
    },
    "debug": [
        {
            "Query": "SELECT  COUNT( customerspecific.dt_employee.id ) as \u0027cnt\u0027 FROM  `customerspecific`.`dt_employee` LEFT JOIN `dt_department` ON `dt_employee`.`deptId` = `dt_department`.`id` WHERE (1=1)",
            "Bindings": []
        },
        {
            "Query": "SELECT  COUNT( customerspecific.dt_employee.id ) as \u0027cnt\u0027 FROM  `customerspecific`.`dt_employee` ",
            "Bindings": []
        },
        {
            "Query": "SELECT  `customerspecific`.`dt_employee`.`id` as \u0027customerspecific.dt_employee.id\u0027, `dt_employee`.`id` as \u0027dt_employee.id\u0027, `dt_employee`.`firstName` as \u0027dt_employee.firstName\u0027, `dt_employee`.`lastName` as \u0027dt_employee.lastName\u0027, `dt_employee`.`deptId` as \u0027dt_employee.deptId\u0027, `dt_department`.`deptName` as \u0027dt_department.deptName\u0027 FROM  `customerspecific`.`dt_employee` LEFT JOIN `dt_department` ON `dt_employee`.`deptId` = `dt_department`.`id` WHERE (1=1) ORDER BY `dt_employee`.`id`  asc  LIMIT 10 OFFSET 0",
            "Bindings": []
        },
        {
            "Query": "SELECT DISTINCT  `deptName` as \u0027deptName\u0027, `id` as \u0027id\u0027 FROM  `dt_department`  ORDER BY `id`  ",
            "Bindings": []
        }
    ],
    "cancelled": []
}

allan · February 20

If I understand your original post, if you were to remove the Department field from the client-side Editor instance, it would insert the row correctly?

One thing that is bothering me about the code above is that you use the editor variable before it is defined! It is used on lines 25-27, which by the code above it should be undefined (it is then defined on line 32), and thus throwing an error.

The fact that it appears to be a global variable suggests to me you might have another Editor instance somewhere already defined and assigned into that global variable, and that would explain why it it isn't throwing an error.

Could you try this:

var employeeEditor = new $.fn.dataTable.Editor({
    "ajax": {
        "url": "@Url.Action("GetServices", "ServiceCatalogue", new { boid = 1 })",
        "type": "POST",
        "data": function (d) {
            d.__RequestVerificationToken = $('input[name=__RequestVerificationToken]').val();
        }
    },
    table: "#tblSC",
    fields: [
        {
            name: "dt_employee.id",
            type: 'hidden'
        },
        {
            name: "dt_employee.firstName",
        },
        {
            name: "dt_employee.lastName",
        },
        {
            label: "Department:",
            name: "dt_employee.deptId",
            type: "select",
            placeholder: "--- select a department ---"
        }
    ]
});

var table = $('#tblSC').DataTable({
    ajax: {
        "url": "@Url.Action("GetServices", "ServiceCatalogue", new { boid = 1 })",
        "type": "POST",
        "data": function (d) {
            d.__RequestVerificationToken = $('input[name=__RequestVerificationToken]').val();
        },
    },
    serverSide: true,
    searching: true,
    orderable: true,
    processing: true,
    dom: 'lBfrtip',
    select: true,
    columns: [
        { "data": "dt_employee.id", "searchable": true},
        { "data": "dt_employee.firstName", "searchable": true},
        { "data": "dt_employee.lastName", "searchable": true},
        { "data": "dt_employee.deptId", render: function ( data, type, row ) {
                                                                return row.dt_department.deptName;
                                                        }, "searchable": true },
    ],
    buttons: [
        { extend: "create", editor: employeeEditor },
        { extend: "edit", editor: employeeEditor },
        { extend: "remove", editor: employeeEditor },
    ]
});

Allan

CMOFNL · February 20

Hi, I copy/pasted the jQuery code in the wrong order, I do have the editor being created before the table. Regardless, I tried your version but the result is the same, i.e. empty result.

CMOFNL · February 20

Also, regarding this question

If I understand your original post, if you were to remove the Department field from the client-side Editor instance, it would insert the row correctly?

If I remove the joined table from the controller and view, i.e. just using a single table, I can create a new record.

allan · February 20

Just the .LeftJoin(...) line, or are you making any other changes as well? I'm wondering about the customerspecific. part in particular.

Allan

CMOFNL · February 20

So if I remove the join and any reference to the Department table and its fields, so just using a single table, it works fine. The customerspecific is just the database schema name.

allan · February 20

Could you try this please?:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
[ValidateAntiForgeryToken()]
public ActionResult GetServices()
{
    var formData = HttpContext.Request.Form;
 
    using (var db = new Database("mysql", _dbFactory.CreateConnection(ConnectionDatabase.CustomerSpecific)))
    {
        var editor = new Editor(db, "customerspecific.dt_employee", "id")
            .Field(new Field("dt_employee.id").Set(true))
            .Field(new Field("dt_employee.firstName").Set(true))
            .Field(new Field("dt_employee.lastName").Set(true))
            .Field(new Field("dt_employee.deptId").Set(true)
                .Options(new Options()
                    .Table("dt_department")
                    .Value("id").Label("deptName")
                    .Order("id")));
 
        var response = editor.Transaction(true).Debug(true).Process(formData).Data();
 
        return Json(response, JsonRequestBehavior.AllowGet);
    }
}

We don't need the .LeftJoin because you aren't reading any fields from dt_department. If that works, then try:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
[ValidateAntiForgeryToken()]
public ActionResult GetServices()
{
    var formData = HttpContext.Request.Form;
 
    using (var db = new Database("mysql", _dbFactory.CreateConnection(ConnectionDatabase.CustomerSpecific)))
    {
        var editor = new Editor(db, "customerspecific.dt_employee", "id")
            .Field(new Field("dt_employee.id").Set(true))
            .Field(new Field("dt_employee.firstName").Set(true))
            .Field(new Field("dt_employee.lastName").Set(true))
            .Field(new Field("dt_employee.deptId").Set(true)
                .Options(new Options()
                    .Table("dt_department")
                    .Value("id").Label("deptName")
                    .Order("id")))
            .Field(new Field("dt_department.deptName").Set(true)
            .LeftJoin("dt_department", "dt_employee.deptId", "=", "dt_department.id");
 
        var response = editor.Transaction(true).Debug(true).Process(formData).Data();
 
        return Json(response, JsonRequestBehavior.AllowGet);
    }
}

I suspect that it was getting upset due to dt_department not being used in a field. You probably do want dt_department.deptName anyway, assuming you are showing the department in the table.

Allan

CMOFNL · February 20

The first method worked, the 2nd didn't (I had to add an additional ')' on line 18)

allan · February 20

With the closing parentheses (sorry about that) you can't create a new row? But edit an existing row, can you edit all four fields?

Its the darnest thing - my C# example with a join has no problem creating new records.

The JSON response still has nothing in the Debug response? What happens if your set .Transaction(false)?

Allan

CMOFNL · February 21

Hi, Edit doesn't work either. I thries setting Transaction to false but no change, I must be missing something.

allan · February 21

Me too! I honestly don't know what though. Are you able to remove the customerspecific. schema? Or does your database connection default to a different schema?

It is very odd that the first code block from my post (two ago) works unmodified, but the second one fails (corrected for the missing )), and doesn't even put anything into the JSON debug response! It should at the very least be recording the library version. What version of the libraries are you using here?

Allan

CMOFNL · February 21

Library is 2.0.5, I'll try removing customerspecific

CMOFNL · February 21

Ahem, ummm, it was the "customerspecific", really sorry to take up your time. It seems to work OK now. Many, many thanks.

Creating new DB record with joined tables

Creating new DB record with joined tables

Answers

Howdy, Stranger!

Categories

DataTables

Creating new DB record with joined tables

Creating new DB record with joined tables

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables