Inserting a record without using an auto-increment primary key

sbsu

What is the best way to perform an insert to the database when the database primary key field is not configured to auto-increment?

I'm still trying to understand the documentation on what's possible regarding styling of the form / templating and consequently have been building my own input screens for a while because I have full control over the style of the modals. I've also been utilizing my own record input / insert screens because I can easily determine the next available primary key prior to insert, which is a requirement when the database isn't auto-incrementing.

I believe many of the examples I've seen in the documentation assume that the underlying database primary key field is an auto-incrementing primary key field and thus datatables happily inserts data without asking any questions.

In other cases (such as mine) however, there might be a requirement to perform an insert when the next available primary key value isn't automatically handled by the database. Does anyone care to comment on the best way to handle this scenario?

Note that the following input form UQID field does not contain the next available primary key.

Thus when performing an INSERT, the command fails and the error that is returned is: The given key was not present in the dictionary.

I am aware of the idSrc property, and while manipulating it would definitely allow me to define an ID source, this doesn't let me change the actual ID value.

I was considering hooking either the preCreate or the preSubmit Event to try and execute a method to simply add 1 to the UQID field but am not sure how to capture and manipulate the UQID field itself and wanted to see if there is already an accepted solution or other ideas that might point me in the right direction.

Thank you

sbsu

Perhaps the initSubmit event is a better event for my scenario....

sbsu

Ok, I've experimented wit the initSubmit and open events to populate the primary key field value. The open event seems a logical choice in which to populate the primary key field and this works great but submitting the form with the primary key form field populated still returns the error The given key was not present in the dictionary.

sbsu

I've also noticed that manipulating values in the open event doesn't just change the values when the input form is shown but also changes the values when performing inline cell edits....

allan

there might be a requirement to perform an insert when the next available primary key value isn't automatically handled by the database.

Where does it come from? If the end user is allowed to enter it, then Editor will cope with that (you'd obviously want to put the unique validator on it).

But does it come from somewhere else?

Allan

sbsu

I should have been more specific.

Probably the most common ways to issue unique keys are to configure the database table to auto increment (MSSQL has been known to create strange gaps in PK number ranges when auto incrementing) and another would be to have a method in software to determine the max integer value and then add 1 to it to get the next available unique number.

In this case, I would like to use an AJAX request to determine the next available number and load it into the insert form.

When doing this now, I get the aforementioned error message.

allan

In this case, I would like to use an AJAX request to determine the next available number and load it into the insert form.

There is a problem with that - if you have two people using the form at the same time - or you have it in two tabs even, then you might get a conflict or might get gaps. You can certainly make an Ajax request to get the next number - us initCreate and make an Ajax request then field().val() to fill in the value.

However, it might be that a database trigger to write in the value is a better option? Certainly, if user isn't isn't required for the primary key value (e.g. a stock number) then I would try to keep it server-side.

Allan

sbsu

Thanks Allan.

Sometime, on a single chart view, I'd like to see the entire chain of possible dt/editor events that can be hooked. I know they are many (thankfully!) and I am discovering them one by one but hooking the events are where the true power is on the UI side.

You are correct in your assessments. In other stacks that I use, I fetch and utilize the next available PK only when the user actually clicks the save/insert button. This has eliminated PK collisions.

I hoped to duplicate that behavior in dt editor but it appears, unless I am mistaken, that the PK value must get populated much earlier in chain of events in the dt editor framework.

allan

Sometime, on a single chart view, I'd like to see the entire chain of possible dt/editor events that can be hooked

For Editor we have that already . For DataTables we don't have the flow of the events, but all of the ones which can be used are documented here.

In other stacks that I use, I fetch and utilize the next available PK only when the user actually clicks the save/insert button

Can you do that server-side to make things a little quicker then? Are you using any of our libraries on the server-side for this? If so, you could use the field's setValue() method to set the value based on the value read from the database, which would save you an Ajax round trip and also prevent anyone from being sneaky and intercepting the Ajax request with the primary key value and changing it!

Allan

sbsu

Without having tried using setValue() yet, this does, at least in documentation, appear to be exactly what I am looking for. I will try and let you know.

Thank you for posting the event sequence link that I hadn't found previously!

sbsu

Ok, finally had a minute to resume looking into this part of your library and to experiment with the setValue() method. I am getting "The given key was not present in the dictionary" when trying to insert a record using the datatables built-in editor pop-up modal.

I stumbled upon a related post, which in a way, describes many of the exact same questions/issues I have or am experiencing.

Here is the code snippet on the server side code (.NET):

            .Field(new Field("0TestTable.TestTableId")
                .Set(Field.SetType.Create)
                .SetValue(pk)
            )

Here is a peek at the browser activity:

The intention is to have server side editor insert an integer into the database primary key field (not using auto-increment) when not provided on the user input form and only during a create operation.

Maybe I am missing some javascript on the UI side.

Being able to use the built in datatables modal pop ups to do record inserts would possibly help me save quite a bit of time with the create part of CRUD if I can make it work in a way in which I can lookup and populate the next primary key id value at the time of insert.

allan

How is the value of pk generated?

I'd say you don't want any client-side stuff to create the primary key - it would be too easy for a malicious actor to mess with your database by inserting their own primary key value.

Thanks,
Allan

sbsu

Exactly.

pk is an int value returned from a method that returns the maximum field value in a database table, incremented by 1. For testing purposes, I've also tried assigning the value of pk statically.

By way of testing, just now, I removed [TestTableId] field from the UI/js so that the input textbox no longer appears on the Create Modal. Now the form gets submitted without the pk field, as you can see. But I still get the error message that the given key was not present in the dictionary.

Shouldn't this code add the pk field as well as its value prior to database insert?

.Field(new Field("0TestTable.TestTableId")
    .Set(Field.SetType.Create)
    .SetValue(pk)
)

sbsu

Removed - duplicate post.

allan

Can you show me how you are setting the variable pk in your code? Or moe generally, just the whole controller might be useful.

That said:

pk is an int value returned from a method that returns the maximum field value in a database table, incremented by 1.

That sounds like a prime candidate for a database trigger to me. BEFORE INSERT could be used to run that method, set the value and that way it is entirely contained within the db. Otherwise, there can be risk of inserting a row from some other route other than Editor and it might end up with no id set. Doing it all within the db ensures data integrity.

Allan