Select2 Field Not Properly Submitting to Server
Select2 Field Not Properly Submitting to Server
mikedmason
Posts: 39Questions: 12Answers: 0
in Plug-ins
Hello,
I have recently implemented the NodeJS server side form of DT and Editor.
I wanted to implement the Select2 Plugin on a field to be able to assign multiple groups to a role essentially.
However when selecting more than one value, and clicking update, the field will either disappear or, still display the original value.
When looking at the database, no values have been changed, still the original value.
Here is the Front End;
jQuery(document).ready(function(){
editor = new jQuery.fn.dataTable.Editor({
"scrollX": true,
dom: "Bfrtip",
'processing': true,
'serverSide': true,
ajax: {
"type":"POST",
"url": api.URL + "/dtapi/access",
},
table: "#access_settings",
fields: [
{
label: "Page Name",
name: "access_settings_table.name"
},
{
label: "Allowed Groups",
name: "access_settings_table.groups",
type: "select2",
opts:{
"multiple": true
},
seperator: ";"
}]
})
editor.on( 'submitComplete', function () {
var table = jQuery('#access_settings').DataTable()
table.ajax.reload();
} );
jQuery('#access_settings').DataTable({
"scrollX": true,
dom: "Blfrtip",
'paging': true,
'processing': true,
'serverSide': true,
"ajax": {
"url": api.URL + "/dtapi/access", //API
"type": "POST"
},
fields:[
{
"label": "id",
"name": "access_settings_table.id"
},
{
"label": "name",
"name": "access_settings_table.name"
},
{
"label": "groups",
"name": "access_settings_table.groups",
"type": "select2",
opts:{
"multiple": true
},
seperator: ";"
},
],
"columns": [
{ "data": "access_settings_table.id"},
{ "data": "access_settings_table.name"},
{
"data": "access_settings_table.groups",
"render": function(data, type, row, meta){
return data
},
"editField": "access_settings_table.groups"
},
],
select: true,
buttons: [
{
extend: "create",
editor: editor
},
{
extend: "edit",
editor: editor
},
{
extend: "remove",
editor: editor
}
]
})
});
The Server Side NodeJS script prints out
{ action: 'edit',
data: { row_0: { access_settings_table: [Object] } } }
{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''CUSTOMER', 'CUSTOMER SERVICE' where `id` = '0'' at line 1
at Query.Sequence._packetToError (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Protocol.js:278:23)
at Parser.write (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/home/kwla/master-kwla/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (/home/kwla/master-kwla/node_modules/mysql/lib/Connection.js:502:10)
at Socket.emit (events.js:189:13)
at addChunk (_stream_readable.js:284:12)
at readableAddChunk (_stream_readable.js:265:11)
at Socket.Readable.push (_stream_readable.js:220:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
--------------------
at Protocol._enqueue (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/home/kwla/master-kwla/node_modules/mysql/lib/Connection.js:200:25)
at /home/kwla/master-kwla/node_modules/knex/lib/dialects/mysql/index.js:144:18
at Promise._execute (/home/kwla/master-kwla/node_modules/bluebird/js/release/debuggability.js:313:9)
at Promise._resolveFromExecutor (/home/kwla/master-kwla/node_modules/bluebird/js/release/promise.js:483:18)
at new Promise (/home/kwla/master-kwla/node_modules/bluebird/js/release/promise.js:79:10)
at Client_MySQL._query (/home/kwla/master-kwla/node_modules/knex/lib/dialects/mysql/index.js:135:12)
at Client_MySQL.query (/home/kwla/master-kwla/node_modules/knex/lib/client.js:192:17)
at Runner.<anonymous> (/home/kwla/master-kwla/node_modules/knex/lib/runner.js:138:36)
at Runner.tryCatcher (/home/kwla/master-kwla/node_modules/bluebird/js/release/util.js:16:23)
at Runner.query (/home/kwla/master-kwla/node_modules/bluebird/js/release/method.js:15:34)
at /home/kwla/master-kwla/node_modules/knex/lib/runner.js:47:21
at tryCatcher (/home/kwla/master-kwla/node_modules/bluebird/js/release/util.js:16:23)
at /home/kwla/master-kwla/node_modules/bluebird/js/release/using.js:185:26
at tryCatcher (/home/kwla/master-kwla/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/home/kwla/master-kwla/node_modules/bluebird/js/release/promise.js:512:31)
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage:
'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'\'CUSTOMER\', \'CUSTOMER SERVICE\' where `id` = \'0\'\' at line 1',
sqlState: '42000',
index: 0,
sql:
'update `access_settings_table` set `name` = \'admin_panel\', `groups` = \'ADMIN\', \'CUSTOMER\', \'CUSTOMER SERVICE\' where `id` = \'0\'' }
{ data: [], fieldErrors: [] }
{}
{ data:
[ { DT_RowId: 'row_0', access_settings_table: [Object] },
{ DT_RowId: 'row_1', access_settings_table: [Object] },
{ DT_RowId: 'row_2', access_settings_table: [Object] },
{ DT_RowId: 'row_3', access_settings_table: [Object] },
{ DT_RowId: 'row_4', access_settings_table: [Object] },
{ DT_RowId: 'row_5', access_settings_table: [Object] },
{ DT_RowId: 'row_100', access_settings_table: [Object] },
{ DT_RowId: 'row_101', access_settings_table: [Object] },
{ DT_RowId: 'row_102', access_settings_table: [Object] },
{ DT_RowId: 'row_103', access_settings_table: [Object] },
{ DT_RowId: 'row_104', access_settings_table: [Object] },
{ DT_RowId: 'row_200', access_settings_table: [Object] },
{ DT_RowId: 'row_201', access_settings_table: [Object] },
{ DT_RowId: 'row_202', access_settings_table: [Object] } ],
fieldErrors: [],
draw: undefined,
files: {},
options:
{ 'access_settings_table.groups': [ [Object], [Object], [Object], [Object], [Object] ] },
recordsTotal: undefined,
recordsFiltered: undefined }
The route for this part of the server side script, looks as such;
dtapi.post('/access', async function(req, res, next){
var request_package = req.body.request_package
console.log(req.body)
let editor = new Editor(mysql_db, 'access_settings_table')
.fields(
new Field('access_settings_table.id'),
new Field('access_settings_table.name'),
new Field('access_settings_table.groups')
.options(new Options()
.table('user_groups_table')
.value('group_name')
.label(['group_name'])
)
)
await editor.process(req.body)
.catch(function(err){
console.log(err)
})
console.log(editor.data())
res.json(editor.data());
})
Any tips to debug this would be much appreciated.
This discussion has been closed.
Answers
I am seeing that the MySQL statement that Server Side editor is trying to print groups = ADMIN, CUSTOMER, CUSTOMER SERVICE rather than wrapping the individual items in single string.
I have added a preSumbit function which now allows me to pass the data to the Server Side and actually submit to the DB. However Select2, is not able to associated those values with the selectable groups.
preSubmit Function;
PreExisting entry, associated with ADMIN
Proceed to Edit; notice ADMIN group is recognized by Select2
Selected a couple options to add CUSTOMER, CUSTOMER SERVICE
Successfully submitted and updated DB, notice the ';' - this comes from my preSubmit function.
Click on the same field to edit again, no options are shown as they are not recognized due to the data being one string with semi colons.
Is there a way I can separate the data using splice/slice before is it processed by datatables on the front end? Or maybe I am going about this the wrong way.
I have found myself a solution to the preprocessing of the data as well.
I have added the following to my ajax options;
Hope this can help someone else in a similar situation.