Go Premium for a chance to win a PS4. Enter to Win


Interbase problem. Record/Key deleted.

Posted on 1998-11-19
Medium Priority
Last Modified: 2012-06-22
I have Delphi4 installed with Interbase 5.0. Here is the probelm:
I have a database and a table in it. This table is created this way
ID_STOCKGROUP         INT              NOT NULL,
Name                  char(96),
Code                  char(32),
MarkUpValue           float            DEFAULT 0,
MarkUpIsPercent       FT               DEFAULT "T",
MinAmount             float            DEFAULT 0,
MaxAmount             float            DEFAULT 0,
MinMaxWanted          FT               DEFAULT "T",
CassGroup             int              DEFAULT 1,
ID_UserCreate         int,
ID_UserEdit           int
The first field has to be Autoincrement field. But Interbase does not have native support for autoincrement fields. So i made a generator and a triger. The triger fires before post in the database. When i try to post a NEW record i get the BDE error "Record/Key Deleted". After this when i press the refresh button, i see that the record has been posted. But i don't want to get this error. What might be the problem, what does this error meen, and what is the solution?
Question by:pripkam
  • 5
  • 4
  • 2
  • +1

Expert Comment

ID: 1347291

I can't give you an exact answer as I don't use Interbase, but I can give you some good pointers.  However the problem is a standard SQL problem when using auto increment fields in the primary key.  

The problem is caused by the fact that you post a record with out a primary key, the DB then gives it a primary key - fine.  However delphi then tries to reposition itself onto that row - probably using the primary key (which as far as delphi is concerned is NULL) and hence cannot find the row. EXCEPTION!

As for a work around - Are you using TTables?  - If so don't.... Use TQuery's.  Also do not request a live result set from the TQuery - use TUpdateQuery to update records.

This should remove the error from the post.  However if you try and edit that row you will probably get the same error.  This may or may not be important.

Should you need to be able to edit a newly inserted record, you have 2 choices - firstly you can refresh the dataset (you may have to close & reopen the TQuery to achive this) - this is fine if the dataset is small.  Alternativly if there is some way of getting the last value of the generator back from interbase then you can put this into the dataset by something like:

Query1.FieldByName('ID_STOCKGROUP').Value := myIdentity;

In MS SQL server after you have issued an insert statement that inserts a row with an identity field you can refer to the value of the variable @@identity which contains the value inserted.

ie you can issue a SELECT @@IDENTITY

You can then put this value into the identity field in your recordset and avoid all complications....

Unfortunately there is no cure all because of the way SQL was defined.

If you want any further assistance, just let me know.

Good Luck



Author Comment

ID: 1347292
Sorry, I have forgotten to say that ID_STOCKGROUP has a Primary key, so it seems that this is not the problem.
I use only TTables (not TQuery-s).
About "Query1.FieldByName('ID_STOCKGROUP').Value := myIdentity":
I have already done something like this, and it works fine, but the problem is that i want to move the bussiness ligin in the server. I capture the event lokaly (in the "OnNewRecord" of the TTable), then i call a StoredProcedure which in turn calls the generator, and the proc returns value. That is fine but i want to make this from the server. That is how i will become application-independedable. Do you ged it?


Expert Comment

ID: 1347293

Yes, I assumed it was the primary key as I stated in my answer....  You wouldn't get the error otherwise....

What I suggest is that do everything server side, but you then need to get the value that got inserted into the primary key.  As I said under MS SQL you can use SELECT @@IDENTITY - I assume you can get in effect generator - 1 - or perhaps return the value of the primary key from your insert stored proc (my prefered route).  

You then need to put this value into your local recordset manually as delphi doesn't know about this because it happened server side.  Hence the code sample above.

I think you misunderstood my answer - Basically you need to insert the value for the primary key into the delphi field *AFTER POST* so that the delphi cached recordset reflects that of the live data.  Otherwise you get the dreaded record deleted error which is cause by delphi realising that its cached record and the one on the server are different (not surprising - the server added a field...)

Unfortunately there is no *nice* work around - this is as good as it gets.  You either refresh the dataset or you pick up the identity value and "kludge" it back into the cached dataset.

It isn't ideal but it is the main problem with the SQL standard.  As the changes are heppening remotely you have *NO* way of monitoring them - so you have to deal with it manually at the client or refresh the entire dataset (how can you refresh the row if you don't know its primary key?).

The solution I use under SQL server is to use a stored proc to do the insert and return a result set from the proc that contains the value of the indentity field I have just inserted.  I then force this back into the dataset.

Good luck,

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 1347294
It seems that i am missing something. PLEASE post some code here so that i can understand you. I  THINk i did what you said, but i am not shure.
Sorry about this INCONVENIENCE


Author Comment

ID: 1347295
It seems that i am missing something. PLEASE post some code here so that i can understand you. I  THINk i did what you said, but i am not shure.
Sorry about this INCONVENIENCE


Expert Comment

ID: 1347296

Unfortunately code might be a bit dificult - I don't use interbase - in fact I don't even use the BDE I use some TQuery descendants for direct MS SQL access however the same problem applies.

Okay here goes.....  This is a kludge but it should work.....

1) Declare a boolean variable (called insert in this example).
In you AfterInsert event set insert := True (obviously in  oncreate set it to false).

In the AfterPost event write something like this...

  if insert then
    insert := false;
    SQLQuery1['p_campaign'] := GetIdentityValue;

This code works here with SQL....  However it does assume that your update SQL objects update query does not touch the identity field.

The GetIdentityValue function returns the value that was inserted into the identity field from the DB.

What the above code does it force delphi into putting the correct value of the identity field into the cached copy of the identity field.  

You will find that you can also now edit a newly inserted field etc.

If you want any further information just let me know

Good luck



Author Comment

ID: 1347297
Sorry for the delay. (problems with the ISP)
So here is what happens. I desided to reorganise everything. First i started using TTables. From the "Client/server programming with Delphi3" i took a table that is absolutly the same as mine table(if you want i can mail the tables to you). It has triger like mine, generator like mine, primary keys and so on.... Here comes the intereting part: It works with their table but doesn't with mine, that is when i try to post a new record, the record is been posted and updated in the database, but i get the error "Record/key deleted" again. I don't get this error with thier table. I  comapred the two tables field, by field, property by property...
Absolutly strange.
Sorry about the reeling out :)

Expert Comment

ID: 1347298

Well lets put it this way the SQL standard states that this should *NOT* work.  I have given you the answers above which is the accepted method of getting the identity for a table after insertion.  Unless borlands example performs a refresh (or uses some cunning interbase feature) it should not work.  SQL servers will not work in this context as explained earlier.  According to the SQL standard when you perform an insert statement the server does not tell you the value of the identity inserted therefore your program does not know what the identity value is - it has *NO* way of knowing what it will be.  Consequently when you access the table you get an error due to inconsistancies between the cached record set and the one on the server.....

As a note, if you are using a database server you should be using TQuery objects, not TTable objects.

The last answer I gave is a valid work around, and is the only portable method I know of.  I have had it working with differing databases without a problem.  If you are looking for a miracle solution that involves no coding then unfortunately I cannot help you.




Accepted Solution

EmmDieh earned 600 total points
ID: 1347299
The Record-Deleted-Problem is a well known when using the BDE with an SQL-Server. It happens when the SQL-Server modifies the primary key within the before insert trigger but the BDE cannot know the new value so it thinks the record has been deleted.
also see: http://www.mers.com/interbasereckeydel.html
AFAIK, no SQL Sever has a datatype like Autoincrement, because it is not needed. They have triggers and generators instead.
So your first approach was totally correct. Use TQueries with SQL-Servers.

Author Comment

ID: 1347300
Here go the points...
But why should i use TQuery instead of TTable?
Is seem that my database was currupted, because when i rebuild it(entirely new one, but the same as first one) it is working ok with TTable.

Expert Comment

ID: 1347301
tTable's are not designed to work with SQL-Servers. They hide operation from the user (like select * and other nasty things).
If you go from local to remote you'll see that you'll have to minimize network traffic.

Expert Comment

ID: 32707689
I have had this problem for years with no solution with Interbase/Delphi/BDE. What Zac and EmmDieh make sense if you are modifying the primary key. But what if you are not?

I have an interbase table where I'm changing a non PK/non indexed value on an exisitng record (i.e. *NOT* a record that I recently inserted). For many years this record was modifyable now: RECORD/KEY DELETED.

Anybody got any ideas?

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline
Suggested Courses

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question