Editor Create - No Immediate Reload - Needs Refresh

iqvct

I've searched the forums and found similar issues, but can't seem to apply these responses to a resolution:

https://datatables.net/forums/discussion/60278/editor-not-updating-the-table-after-a-new-record-is-posted
https://datatables.net/forums/discussion/40960/table-does-not-update-on-create
https://datatables.net/forums/discussion/43796/new-records-not-showing-up-using-editor-extension

Our use case involves:

• .NET Framework.
• SQL Server 2019
• Table with auto-incrementing primary key.

On edit and delete, the table automatically reloads and reflects the changes.

On create, the table does not display the new record until a manual refresh is done.

I have added debugger and see the following returned after a create:

Debug

{draw: null, data: Array(0), recordsTotal: null, recordsFiltered: null, error: '', …}
cancelled: []
data: []
debug: null
draw: null
error: ""
fieldErrors: []
files: {}
id: null
meta: {}
options: {}
recordsFiltered: null
recordsTotal: null
upload: {id: null}
[[Prototype]]: Object

Model

  public class LoginCountryAccessModel
    {
        public int LoginCountryAccessId { get; set; }
        public int LoginId { get; set; }
        public string Email { get; set; }
        public string CountryName { get; set; }
   
    }

Controller

public class LoginCountryAccessController : ApiController
    {
        [Route("api/LoginCountryAccess")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult LoginCountryAccess()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "LoginCountryAccess", "LoginCountryAccessId")
                    .Model<LoginCountryAccessModel>("LoginCountryAccess")
                    //.Field(new Field("LoginCountryAccess.LoginCountryAccessId").Set(false))

                    //.Debug(true)
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }

Javascript

(function ($) {

    $(document).ready(function () {

        var editor = new $.fn.dataTable.Editor({
            ajax: '/api/LoginCountryAccess',
            table: '#LoginCountryAccess',
            
            fields: [
                {
                    "label": "Email:",
                    "name": "LoginCountryAccess.Email"
                },
                {
                    "label": "CountryName:",
                    "name": "LoginCountryAccess.CountryName"
                }
            ]
        });


        var table = $('#LoginCountryAccess').DataTable({

            ajax: {
                url: '/api/LoginCountryAccess',
                type: 'POST'
            },
            dom: 'Bfrtip',
            "order": [0, "desc"],
            
            columns: [
                {
                    "data": "LoginCountryAccess.Email"
                },
                {
                    "data": "LoginCountryAccess.CountryName"
                }
                
            ],
            select: true,
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit", editor: editor },
                { extend: "remove", editor: editor }
            ]
        });

        editor.on('submitSuccess', function (e, json, data) {
            console.log(json)
        });


    });

}(jQuery));

From my reading it seems that the issue is related to the auto-incremented record and upon submit the front end is not aware of the primary key so it is not able to publish the data.

This is in contrast to an edit/delete where the primary key is already established.

Any thoughts on what is missing in our solution would be appreciated.

allan

I think you'll need to comment this back in:

//.Field(new Field("LoginCountryAccess.LoginCountryAccessId").Set(false))

Because you have the primary key in your model, Editor will set it up as a field. Adding the above line back in will make sure that nothing attempts to set it.

If that doesn't work, can you show me the request headers that are sent to the server for the create action please?

Thanks,
Allan

iqvct

Hi Allan

Thanks for the quick reply.

The commented line was pieced together from this post:

https://datatables.net/forums/discussion/60278/editor-not-updating-the-table-after-a-new-record-is-posted

The reason why this line was commented out was because it unfortunately did not change the final result. Create was able to post to the database, but only reflected on the front end on a reload.

I added the line back, but same result.

Below is what I'm seeing as the request headers for the create action:

Request Headers

:authority: localhost:44308
:method: POST
:path: /api/LoginCountryAccess
:scheme: https
accept: application/json, text/javascript, */*; q=0.01
accept-encoding: gzip, deflate, br
accept-language: en-US,en;q=0.9
content-length: 146
content-type: application/x-www-form-urlencoded; charset=UTF-8
cookie: __utmz=111872281.1638277320.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); __utmc=111872281; __utma=111872281.126771722.1638277320.1639700715.1640025248.27; __utmt=1; __utmb=111872281.6.10.1640025248; arp_scroll_position=0
origin: https://localhost:44308
referer: https://localhost:44308/Home/LoginCountryAccess
sec-ch-ua: " Not A;Brand";v="99", "Chromium";v="96", "Google Chrome";v="96"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
sec-fetch-dest: empty
sec-fetch-mode: cors
sec-fetch-site: same-origin
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36
x-requested-with: XMLHttpRequest

I also see this in the console:

Console

{
    "draw": null,
    "data": [],
    "recordsTotal": null,
    "recordsFiltered": null,
    "error": "",
    "fieldErrors": [],
    "id": null,
    "meta": {},
    "options": {},
    "files": {},
    "upload": {
        "id": null
    },
    "debug": [
        {
            "Query": "DECLARE @T TABLE ( insert_id smallint ); INSERT INTO  [LoginCountryAccess]  ( [Email], [CountryName] ) OUTPUT INSERTED.LoginCountryAccessId as insert_id INTO @T VALUES (  @Email,  @CountryName ); SELECT insert_id FROM @T",
            "Bindings": [
                {
                    "Name": "@Email",
                    "Value": "test@test.com",
                    "Type": null
                },
                {
                    "Name": "@CountryName",
                    "Value": "test",
                    "Type": null
                }
            ]
        },
        {
            "Query": "SELECT  [LoginCountryAccessId] as 'LoginCountryAccessId', [LoginCountryAccess].[LoginCountryAccessId] as 'LoginCountryAccess.LoginCountryAccessId', [LoginCountryAccess].[LoginId] as 'LoginCountryAccess.LoginId', [LoginCountryAccess].[Email] as 'LoginCountryAccess.Email', [LoginCountryAccess].[CountryName] as 'LoginCountryAccess.CountryName' FROM  [LoginCountryAccess] WHERE [LoginCountryAccessId] = @where_0 ",
            "Bindings": [
                {
                    "Name": "@where_0",
                    "Value": "0",
                    "Type": null
                }
            ]
        }
    ],
    "cancelled": []
}

allan

Many thanks for the new JSON. It is showing that the Editor libraries are trying to get the data from LoginCountryAccess where LoginCountryAccessId = 0.

That is obviously nonsense and results in what you are seeing - the response from the server doesn't include the newly inserted data.

Two things:

1. What is LoginCountryAccessId in the database? Can you give me a dump of the SQL for the LoginCountryAccess (without the data - just the structure is fine) so I can attempt to recreate the issue?
2. What version of the Editor dll are you using?

Thanks,
Allan

iqvct

Thanks again Allan,

I think I have figured out the issue. I have a workaround, but no direct resolution.

Let me first answer your questions.

1. LoginCountryAccessId is the primary key of the table. This is the auto-incremented key.

2. I am using 1.9.2 at the moment.

To level set, I have two tables. The one we've been working with, and an associated table called Login. This table has a row for each user and data related to that user. LoginCountryAccess is a table that takes each user and assigns them to one or more country.

I believe the issue lies somewhere in the trigger associated with LoginCountryAccess . The design of the table is that a user would enter an email address and country. Upon insert, a trigger kicks off which will retrieves a final field called LoginId. This field is a foreign key, and the primary key of Login.

This may be a sub-optimal design, but the logic was that a user will have access to someone's email, but certainly not the surrogate key representing them in a table.

A workaround that I tested was to remove the trigger. I could then simply make EmailAddress the primary key in the Login table. I'm hesitant to do this however as emails can change and I would prefer a surrogate key for Login.

The below is the design of the LoginCountryAccess, the one with the create record issue:

SQL TABLE CREATE

CREATE TABLE [dbo].[LoginCountryAccess](
    [LoginCountryAccessId] [smallint] IDENTITY(1,1) NOT NULL,
    [LoginId] [int] NOT NULL,
    [CountryName] [varchar](30) NOT NULL,
    [Email] [varchar](60) NULL,
 CONSTRAINT [PK_LoginCountryAccess] PRIMARY KEY CLUSTERED 
(
    [LoginCountryAccessId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_LoginCountryAccess_LoginId_CountryName] UNIQUE NONCLUSTERED 
(
    [LoginId] ASC,
    [CountryName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoginCountryAccess]  WITH CHECK ADD  CONSTRAINT [FK_LoginCountryAccess_LoginId] FOREIGN KEY([LoginId])
REFERENCES [dbo].[Login] ([LoginId])
GO

ALTER TABLE [dbo].[LoginCountryAccess] CHECK CONSTRAINT [FK_LoginCountryAccess_LoginId]
GO

And here is the associated trigger:

Trigger

CREATE TRIGGER [dbo].[TR_LoginCountryAccess_Insert] ON [dbo].[LoginCountryAccess] 
INSTEAD OF INSERT
AS BEGIN

SET NOCOUNT ON;

INSERT INTO LoginCountryAccess(
       [LoginId]
      ,[CountryName]
      ,[Email]
    
    )
    SELECT
      Login.[LoginId]
      ,i.[CountryName]
      ,i.[Email]

FROM inserted i
LEFT JOIN Login ON i.Email = Login.Email

END
GO

ALTER TABLE [dbo].[LoginCountryAccess] ENABLE TRIGGER [TR_LoginCountryAccess_Insert]
GO

If there is an obvious solution, or if this is a limitation to creating records, no problem.

If not, I can just modify the Login table, set the email as the primary key, and remove the trigger.

allan

That's really interesting - thank you. I actually haven't encountered the "INSTEAD OF" trigger action before, and it looks really interesting. It isn't immediately clear to me why that would stop the pkey auto incrementing being read back as a value though. Assuming the trigger runs in the same transaction (it must), and the action is synchronous to the C# call that executes it, then it should just look like a regular insert from Editor's point of view.

I'll try to recreate that with an INSTEAD OF trigger here.

I'm wondering if it would work successfully with an "AFTER INSERT" trigger which would do basically the same thing - looking and then write the additional information into the table as part of the NEW row? I've used that approach myself before successfully.

Allan

iqvct

@allan It appears you have detected the culprit!

The reason why INSTEAD OF was used was so we could insert into a NOT NULL field.

With AFTER INSERT you get the error of:

Cannot insert the value NULL into column 'Created', table 'Category'; column does not allow nulls. INSERT fails.

To test I re-created the table and allowed NULL on the LoginId field. Then I modified the insert to be AFTER INSERT. The front end then appropriately reflects the data upon insert.

I can likely set a constraint elsewhere which would maintain data integrity, but also allow me to keep LoginId as a NULL field by design even though it will always receive a value.

Thanks for pointing me in the right direction.

iqvct

Quick edit on the message above, the error message was one I copied from Stack Overflow, but for this specific instance would have read:

Cannot insert the value NULL into column 'LoginId', table 'LoginCountryAccess'; column does not allow nulls. INSERT fails.

allan

That is very interesting. Could you use a BEFORE INSERT trigger and look the value up and set it into NEW there? That would prevent the null error occurring.

Allan

iqvct

@allan Unless I'm mistaken, SQL Server does not have a BEFORE INSERT trigger so it either needs to be AFTER INSERT or the one I was initially using which was INSTEAD OF INSERT

iqvct

@allan

In my infinite wisdom, I never reviewed the database to make sure everything worked appropriately. I was so hell bent on the front end appearing correct that I failed to follow up with the actual data that was being inserted.

Unfortunately AFTER INSERT did not work with the current trigger construct because all this did was create an entirely new record. Now we have the original record that the front end user creates, and then an additional record that is triggered in with certain fields.

This was the benefit of using INSTEAD OF INSERT as it did not insert a record but rather modified the record as it was being inserted the first time.

I will have to try to reconstruct the AFTER INSERT trigger and rather than have a INSERT INTO use some type of UPDATE followed by a JOIN which will then only update the most recent record.

I don't know where this solution stands in terms of efficiency, but the only way I can see this working at the moment on both the front end and the back end at the same time if the INSTEAD OF INSERT trigger isn't going to properly retrieve an auto incremented primary key.

allan

I've just been Googling around this for a bit, and it seems like a bit of a gap in SQL Server to me. You are absolutely correct, there is not BEFORE INSERT in SQL Server, apologies for that. I found a number of discussions about trying to get the inserted row id from the INSTEAD OF trigger (e.g. this one was fairly good) - and I'd wondered if one could just use an OUTPUT from the trigger, but there doesn't seem to be a good way to do it. Getting the row id from the inserted row is such an obvious requirement, I'm surprised that SQL Server doesn't seem to handle this case elegantly.

The best I can really suggest at the moment is that you move the logic that was being done in the trigger into the application layer - e.g. do the look up and set the value to be written. The PreCreate event would be ideal for that.

Allan

iqvct

@allan

Thanks for looking so deeply into this. I'll take a look at the link you sent.

What I ultimately did was take your original advice of pivoting to AFTER INSERT, but then changed the logic inside of the trigger to appropriately update the field:

CREATE TRIGGER [dbo].[TR_LoginCountryAccess_Insert] ON [dbo].[LoginCountryAccess]
AFTER INSERT
AS BEGIN

UPDATE L
SET
LoginId = Login.LoginId,
CountryName = i.CountryName,
Email = i.Email

FROM LoginCountryAccess L
JOIN inserted i on L.LoginCountryAccessId = i.LoginCountryAccessId
LEFT JOIN Login ON i.Email = Login.Email

END
GO

ALTER TABLE [dbo].[LoginCountryAccess] ENABLE TRIGGER [TR_LoginCountryAccess_Insert]
GO

Now after the data is inserted into the table an update occurs to bring in the foreign key LoginId. But since we're using AFTER INSERT rather than INSTEAD OF INSERT, the auto-incremented primary key is now being read back and is immediately reflected in record creation.