Forum > Databases

IBUpdateSQL - primary key

(1/1)

Nicole:
There is a table with a primary key which is generated within Firebird by a generator.
Works fine.

If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

What can I do, that my DBGrid / DBNavigator just writes new rows into my database and let the generator within firebird do his job to generate a new ID?

rvk:

--- Quote from: Nicole on February 09, 2024, 03:03:20 pm ---If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

What can I do, that my DBGrid / DBNavigator just writes new rows into my database and let the generator within firebird do his job to generate a new ID?

--- End quote ---
If you want the ID to be generated automatically, then don't put that field in the TDBGrid.
When the ID isn't used, it will be automatically generated.

But depending on your generator you could also let the new value generate on NULL or 0.
For NULL you would need to set the required property of the field to false.
That all depends on how your trigger looks.

Mine is this for LAND. So it will create a new ID for NULL but also for 0.
Anything else will just be accepted.

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TRIGGER TR_LAND FOR LAND ACTIVE BEFORE INSERT POSITION 0 ASBEGIN  IF ((NEW.LINKNUMMER IS NULL) OR (NEW.LINKNUMMER = 0)) THEN      NEW.LINKNUMMER = GEN_ID(GEN_LAND, 1);END^
For NULL I also need to do this if I have the primary key in the SELECT and INSERT:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---IBQuery1.FieldByName('LINKNUMMER').Required := falseotherwise you get an error that the field is NULL and needs to have a value because it's a primary key.


--- Quote from: Nicole on February 09, 2024, 03:03:20 pm ---If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

--- End quote ---
BTW. PLEASE don't mention errors like "something like".
ALWAYS state the EXACT error message !!!!

Nicole:
Thank you for the answer. This code, where to write it?


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---    CREATE TRIGGER TR_LAND FOR LAND ACTIVE BEFORE INSERT POSITION 0 AS    BEGIN      IF ((NEW.LINKNUMMER IS NULL) OR (NEW.LINKNUMMER = 0)) THEN          NEW.LINKNUMMER = GEN_ID(GEN_LAND, 1);    END^
This id - I need it for changing rows = asset-fields.

So one part of my form are the group of panels allowing to edit the values of one row.
Then there is a dynmicDBGrid, which allows to find items and to load them into the panel-system.
I have vage ideas, that the user can click at lines in the Grid, I will find the id by adressing the field in the query and passing the id back to the panel-group, which can be reloaded for the very id = chosen asset in grid

rvk:

--- Quote from: Nicole on February 09, 2024, 05:04:27 pm ---Thank you for the answer. This code, where to write it?

--- End quote ---
This is an example of a trigger in my database.

You have similar triggers in yours.
But since I have no idea about your database I can't say if it's correct.

And you didn't mention the exact error message so it was just a guess as to what the problem is.

tonyw:

--- Quote from: Nicole on February 09, 2024, 03:03:20 pm ---There is a table with a primary key which is generated within Firebird by a generator.
Works fine.

If I want to insert one row by the DBNavigaor "+" sign and a DBGrid, - I see an error message, because something like "invalid primary key".

What can I do, that my DBGrid / DBNavigator just writes new rows into my database and let the generator within firebird do his job to generate a new ID?

--- End quote ---

You should add a "returning" clause to the TIBUpdateSQL insert statement. You should specify that the returned field is the primary key. When you post the update, the primary key field is then automatically set to the value generated by the trigger.

If that doesn't work i.e. you need the primary key value before calling post, then you should not use a trigger. Instead, use the generatorfield property of TIBQuery. IBX will then invoke your generator as soon you click on the navigator's + button.

Navigation

[0] Message Index

Go to full version