DataTable Editor (MJoin with Link Table) not work (C# .Net version)

Ali Adnan

Dear Allan,

I have purchased DataTables Editor and its a really very nice product I ever seen, I am following the example which is included in download (Join tables - one-to-many join) Example Using .Net WebApi.

The example works fine after setup, I followed same code to incorporate in my Project then I suspect an error behavior and when I did same to the sample data I get the same error result. as the example is based on three table

USERS
USER_PERMISSION
PERMISSION

if I truncate USERS table and I try to insert fresh record the Permission check boxes disappeared means not loaded from database to select.

See Image JoinArrayPermission.png

The Ajax Load TAB show the following Data Received from Server

{
  "draw": null,
  "data": [],
  "recordsTotal": null,
  "recordsFiltered": null,
  "error": null,
  "fieldErrors": [],
  "id": null,
  "meta": {},
  "options": {
    "users.site": [
      {
        "value": 1,
        "label": "Edinburgh"
      },
      {
        "value": 2,
        "label": "London"
      },
      {
        "value": 6,
        "label": "Los Angeles"
      },
      {
        "value": 4,
        "label": "New York"
      },
      {
        "value": 3,
        "label": "Paris"
      },
      {
        "value": 5,
        "label": "Singapore"
      }
    ]
  },
  "files": {},
  "upload": {
    "id": null
  },
  "debugSql": null,
  "cancelled": []
}

If I insert the record and select it and make it edit still the Permission check boxes disappeared means not loaded from database to select. Then I just Refresh the page by hitting (F5) and after that the Ajax Load TAB show the following Data Received from Server

{
  "draw": null,
  "data": [
    {
      "DT_RowId": "row_26",
      "users": {
        "site": 1,
        "first_name": "ALI",
        "last_name": "ADNAN",
        "phone": null
      },
      "sites": {
        "name": "Edinburgh"
      }
    }
  ],
  "recordsTotal": null,
  "recordsFiltered": null,
  "error": null,
  "fieldErrors": [],
  "id": null,
  "meta": {},
  "options": {
    "users.site": [
      {
        "value": 1,
        "label": "Edinburgh"
      },
      {
        "value": 2,
        "label": "London"
      },
      {
        "value": 6,
        "label": "Los Angeles"
      },
      {
        "value": 4,
        "label": "New York"
      },
      {
        "value": 3,
        "label": "Paris"
      },
      {
        "value": 5,
        "label": "Singapore"
      }
    ],
    "permission[].id": [
      {
        "value": 6,
        "label": "Accounts"
      },
      {
        "value": 3,
        "label": "Desktop"
      },
      {
        "value": 1,
        "label": "Printer"
      },
      {
        "value": 2,
        "label": "Servers"
      },
      {
        "value": 4,
        "label": "VMs"
      },
      {
        "value": 5,
        "label": "Web-site"
      }
    ]
  },
  "files": {},
  "upload": {
    "id": null
  },
  "debugSql": null,
  "cancelled": []
}

and its available to update
see images

How to solve this problem ? please guide me.

I dont have any public available server to upload my project, but I am sure same can be reproduce as you need to only truncate users table and check.

Best Regards

allan

Hi,

Thanks for posting this. I don't have a Windows computer with me right now, but I'll check this out when I get back.

In the mean time, I'm reasonably confident that the fix is to change the following in the Data method of the Mjoin class:

            if (!response.data.Any())
            {
                return;
            }

Instead, it should be:

            if (!_get)
            {
                return;
            }

I'll double check that later on, and commit it in assuming all is good.

Thanks,
Allan

Ali Adnan

Hi Allan,
you suggestion works and it show the "permission" as it was not showed before. however when I create new Entry the records successfully inserted in USER Table but in USER_PERMISSION table the user_id inserted "null" and PERMISSION_ID inseted as desired. please see the screen shot and server data

Page Load Data

{
  "draw": null,
  "data": [],
  "recordsTotal": null,
  "recordsFiltered": null,
  "error": null,
  "fieldErrors": [],
  "id": null,
  "meta": {},
  "options": {
    "users.site": [
      {
        "value": 1,
        "label": "Edinburgh"
      },
      {
        "value": 2,
        "label": "London"
      },
      {
        "value": 6,
        "label": "Los Angeles"
      },
      {
        "value": 4,
        "label": "New York"
      },
      {
        "value": 3,
        "label": "Paris"
      },
      {
        "value": 5,
        "label": "Singapore"
      }
    ],
    "permission[].id": [
      {
        "value": 6,
        "label": "Accounts"
      },
      {
        "value": 3,
        "label": "Desktop"
      },
      {
        "value": 1,
        "label": "Printer"
      },
      {
        "value": 2,
        "label": "Servers"
      },
      {
        "value": 4,
        "label": "VMs"
      },
      {
        "value": 5,
        "label": "Web-site"
      }
    ]
  },
  "files": {},
  "upload": {
    "id": null
  },
  "debugSql": [
    {
      "Query": "SELECT  users.id as \"users.id\", users.site as \"users.site\", users.first_name as \"users.first_name\", users.last_name as \"users.last_name\", users.phone as \"users.phone\", sites.name as \"sites.name\" FROM  users LEFT JOIN sites ON sites.id = users.site ",
      "Bindings": []
    },
    {
      "Query": "SELECT DISTINCT  name as \"name\", id as \"id\" FROM  sites ",
      "Bindings": []
    },
    {
      "Query": "SELECT DISTINCT  users.id as \"dteditor_pkey\", permission.id as \"id\", permission.name as \"name\" FROM  users  JOIN user_permission ON users.id = user_permission.user_id   JOIN permission ON permission.id = user_permission.permission_id  ORDER BY permission.name  ",
      "Bindings": []
    },
    {
      "Query": "SELECT DISTINCT  name as \"name\", id as \"id\" FROM  permission ",
      "Bindings": []
    }
  ],
  "cancelled": []
}

Submitted data:

action=create
data[0][users][first_name]=Ali
data[0][users][last_name]=Adnan
data[0][users][site]=2
data[0][permission][0][id]=6
data[0][permission][1][id]=5
data[0][permission-many-count]=2

Server response:

{
  "draw": null,
  "data": [],
  "recordsTotal": null,
  "recordsFiltered": null,
  "error": null,
  "fieldErrors": [],
  "id": null,
  "meta": {},
  "options": {},
  "files": {},
  "upload": {
    "id": null
  },
  "debugSql": [
    {
      "Query": "INSERT INTO  users  ( site, first_name, last_name ) VALUES (  :site,  :first_name,  :last_name ) RETURNING users.id INTO :dtvalue",
      "Bindings": [
        {
          "Name": ":site",
          "Value": 2,
          "Type": null
        },
        {
          "Name": ":first_name",
          "Value": "Ali",
          "Type": null
        },
        {
          "Name": ":last_name",
          "Value": "Adnan",
          "Type": null
        }
      ]
    },
    {
      "Query": "INSERT INTO  user_permission  ( user_id, permission_id ) VALUES (  :user_id,  :permission_id )",
      "Bindings": [
        {
          "Name": ":user_id",
          "Value": "",
          "Type": null
        },
        {
          "Name": ":permission_id",
          "Value": 6,
          "Type": null
        }
      ]
    },
    {
      "Query": "INSERT INTO  user_permission  ( user_id, permission_id ) VALUES (  :user_id,  :permission_id )",
      "Bindings": [
        {
          "Name": ":user_id",
          "Value": "",
          "Type": null
        },
        {
          "Name": ":permission_id",
          "Value": 5,
          "Type": null
        }
      ]
    },
    {
      "Query": "SELECT  users.id as \"users.id\", users.site as \"users.site\", users.first_name as \"users.first_name\", users.last_name as \"users.last_name\", users.phone as \"users.phone\", sites.name as \"sites.name\" FROM  users LEFT JOIN sites ON sites.id = users.site WHERE users.id = :where_0 ",
      "Bindings": [
        {
          "Name": ":where_0",
          "Value": "",
          "Type": null
        }
      ]
    },
    {
      "Query": "SELECT DISTINCT  name as \"name\", id as \"id\" FROM  sites ",
      "Bindings": []
    },
    {
      "Query": "SELECT DISTINCT  users.id as \"dteditor_pkey\", permission.id as \"id\", permission.name as \"name\" FROM  users  JOIN user_permission ON users.id = user_permission.user_id   JOIN permission ON permission.id = user_permission.permission_id  ORDER BY permission.name  ",
      "Bindings": []
    },
    {
      "Query": "SELECT DISTINCT  name as \"name\", id as \"id\" FROM  permission ",
      "Bindings": []
    }
  ],
  "cancelled": []
}

Please advice

Ali Adnan

Dear Allan,
The above problem solved if the Insert out parameter Data Type is Int32, I have already open a seperate discussion in below line

https://datatables.net/forums/discussion/43445/insert-return-value-datatype-only-allowed-int-data-type#latest

if I change outParam.DbType = DbType.String; to DbType.Int32; in

DataTables\DataBaseUtil\Oracle\Query.cs File

its work fine but I have other Table whose primary key are varchar2 and if I change this one I got error in others tables on insert.

Please advice,

allan

Thanks. I've added a reply in your other thread. I'll let you know when I get that resolved.

Regards,
Allan

allan

Just to confirm, the fix for the options not appearing when the parent table is empty has been committed and will be in 1.6.4 which will drop soon.

I've still to look into the other issue, and will post back there when done.

Allan