SQL Server insert - erroneous table name in OUTPUT clause

grapefruitmoon

Hi,
I'm running Editor using Node.js to create a simple table of data. SQL Server (Azure) is the database. Editing works fine, but when I use the Create functionality, I get an error as below.

insert into [Member] ([Address1], [Address2], [Address3], <snip>) output inserted.[Member].[MemberID] values (@p0, @p1, @p2, @p3, <snip>) - Invalid column name 'Member'.
app.js:98
stack: RequestError: Invalid column name 'Member'.

The [Member] part of inserted.[Member].[MemberID] should not be there - any ideas what I'm doing wrong? There is a join to another table but that displays fine and works no problem when editing.

Thanks!

colin

Hi @grapefruitmoon ,

Could you post your PHP and Editor/DataTables JS and config, please.

Cheers,

Colin

grapefruitmoon

Sure!

DataTables JS -

    script.init(type='text/javascript', language='javascript').
      var editor;
      $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
        ajax: "/api/member",
        table: "#Members",
        //- template: "#membersForm",
        fields: [
          //- {
          //-   label: "Member ID:",
          //-   name: "Member.MemberID"
          //- },
          {
            label: "First name:",
            name: "Member.Forename"
          },
          {
            label: "Last name:",
            name: "Member.Surname"
          },
          {
            label: "Branch:",
            name: "Member.BranchID",
            type: "select"
          },
          {
            label: "Address 1:",
            name: "Member.Address1"
          },
          {
            label: "Address 2:",
            name: "Member.Address2"
          },
          {
            label: "Address 3:",
            name: "Member.Address3"
          },
          {
            label: "Town:",
            name: "Member.Town"
          },
          {
            label: "Postcode:",
            name: "Member.Postcode"
          },
          {
            label: "Phone Number:",
            name: "Member.TelephoneNumber"
          },
          {
            label: "DoB:",
            name: "Member.DateOfBirth",
            type: "date",
            def:    function () { return new Date(); },
            format: "D/M/YYYY"
          },

<snipped for brevity>

          {
            label: "Join Date:",
            name: "Member.JoinDate",
            type: "date",
            def:    function () { return new Date(); },
            format: "D/M/YYYY"
          }
        ]
      } );
      var table = $('#Members').DataTable( {
      lengthChange: false,
      ajax: "/api/member",
      columns: [
        { data: "Member.MemberID" },
        { data: "Member.Forename" },
        { data: "Member.Surname" },
        { data: "Branch.BranchName", editField: "Member.BranchID"},
        { data: "Member.Town" },
        { data: "Member.DateOfBirth" },
        { data: "Member.Lottery",
          render: function (val, type, row) {
              return val == true ? "Yes" : "No";
          }        
        }
      ],
      keys: {
        columns: ':not(:first-child)',
        editor:  editor
      },
      select: {
        style:    'os',
        selector: 'td:first-child',
        blurable: true
      }
      // select: true
      } );
      // Display the buttons
      new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor },
              {
                  extend: 'collection',
                  text: 'Export',
                  buttons: [
                      'copy',
                      'excel',
                      'csv',
                      'pdf',
                      'print'
                  ]
              }
      ] );
      table.buttons().container()
      .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
      } );

Node.js -

let db = require('../config/sqldb');
let router = require('express').Router();
let {
    Editor,
    Field,
    Validate,
    Format,
    Options,
    Mjoin
} = require("datatables.net-editor-server");

router.all('/api/member', async function(req, res) {
    let editor = new Editor(db, 'Member', 'MemberID')
        .fields(
            new Field("Member.MemberID"),
            new Field("Member.Title"),
            new Field("Member.Forename"),
            new Field("Member.Surname"),
            new Field("Member.Address1"),
            new Field("Member.Address2"),
            new Field("Member.Address3"),
            new Field("Member.Town"),
            new Field("Member.Postcode"),
            new Field("Member.TelephoneNumber"),
            new Field("Member.DateOfBirth")
                .getFormatter(Format.sqlDateToFormat("YYYY-MM-DD"))
                .setFormatter(Format.formatToSqlDate("YYYY-MM-DD")),

<snipped for brevity>

            new Field('Member.BranchID').options(
                new Options().table('Branch').value('BranchID').label('BranchName')
            ),
            new Field('Branch.BranchName')
        )
        .leftJoin('Branch', 'Branch.BranchID', '=', 'Member.BranchID')

    await editor.process(req.body);
    res.json(editor.data());

});

allan

Could you try:

new Field("Member.MemberID").set( false )

please?

Thanks,
Allan

grapefruitmoon

Hi Allan - I just tried that and I get the same error. The MemberID column is an Identity column on that table. I'm wondering if I'm doing something wrong with the aliasing that is causing the erroneous table name to appear.

grapefruitmoon

I've cut the code down to a very simple example with just a few columns and still getting that error. If I remove the join it works fine, but when I add it in and pre-pend the column names with the relevant table name the insert fails. Stumped! Is there another way of joining without having to use the table name?

allan

I'm with you now. This is an error in the Editor NodeJS libraries. I've used Knex as the database abstraction layer there and it doesn't automatically handle this case, so I've committed a fix for it now.

If you update to the master version from that repo that should resolve the issue!

Regards,
Allan

allan

Sorry - this is the fixing commit - realised I committed a test change first...

Allan

grapefruitmoon

Allan - amazing, this works perfectly! Thank you so much.

I'll take this opportunity to also say how much I'm loving DataTables and Editor, great product.