SQL Server insert - erroneous table name in OUTPUT clause

SQL Server insert - erroneous table name in OUTPUT clause

grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

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!

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @grapefruitmoon ,

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

    Cheers,

    Colin

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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());
    
    });
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Could you try:

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

    please?

    Thanks,
    Allan

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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.

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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?

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

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

    Allan

  • grapefruitmoongrapefruitmoon Posts: 11Questions: 2Answers: 0

    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. :smile:

This discussion has been closed.