Help!

I'm using D2 to access an MS SQL 6.5 server via SQL links.  I've used D1
for plenty of prior projects, but this is my first crack at a database app
and I'm getting an error that might be a piece of cake for an experienced
Delphi DB programmer (I grovel in your omnipotent presence), but it's
kicking the hell out of me.

I am working on a simple data entry form.  I used the Database Form Expert
to generate the form and I used TTable, not TQuery as my dataset for
accessing the table on the SQL server. So far, so good -- I can view, edit
and delete records with impunity.  

The problem happens when I try to insert a record.  I get "EDBEngineError:
'Record/key deleted'".  If I try to insert a record under Access, no
problem, to auto-filled fields (described below) get filled nicely and no
error messages.  I think the problem relates to these auto-filled fields
that the server hits during the insert.

I have the SQL server auto-filling a total of 3 different fields, all set
to read-only on the form:

**ONE**
_InternalTransactionNumber_, the primary key, an auto-incrementing
("identity" (aka "generator" on InterBase)) field.

**TWO**
_EntryDateTime_, filled with the SQL server's date and time

**THREE*
_EntryBy_, filled with the SQL client (user name of the person) that
performed the insertion

I believe the problem has to do with Delphi not liking the idea that the
SQL server is modifying the record after it has so carefully inserted it.
Delphi must be doing some sort of comparison of the before and after image
and not liking what it sees.  The record DOES get inserted correctly.  If
I do two or three of these "botched" inserts in my program, and then
cruise out to MS Access to browse the table, I see all of the new records,
the auto-filled and user-filled fields existing in complete harmony.

I guess the real problem here is that Delphi is raising an exception when
it really doesn't need to.  A proper fix would be to deal with the problem
correctly.  A quicker one would be to trap the exception before it reaches
the desktop and quash it. Something like if (operation=Insert) and
(exception type=EDBEngineError) and (message="Record/key deleted") then
just live with it.

Can anyone offer help on either approach?  Any advice is appreciated.
Thanks in advance.

- Kevin Pierce

: Help!

: I'm using D2 to access an MS SQL 6.5 server via SQL links.  I've used D1
: for plenty of prior projects, but this is my first crack at a database app
: and I'm getting an error that might be a piece of cake for an experienced
: Delphi DB programmer (I grovel in your omnipotent presence), but it's
: kicking the hell out of me.

: I am working on a simple data entry form.  I used the Database Form Expert
: to generate the form and I used TTable, not TQuery as my dataset for
: accessing the table on the SQL server. So far, so good -- I can view, edit
: and delete records with impunity.  

: The problem happens when I try to insert a record.  I get "EDBEngineError:
: 'Record/key deleted'".  If I try to insert a record under Access, no
: problem, to auto-filled fields (described below) get filled nicely and no
: error messages.  I think the problem relates to these auto-filled fields
: that the server hits during the insert.

: I have the SQL server auto-filling a total of 3 different fields, all set
: to read-only on the form:

: **ONE**
: _InternalTransactionNumber_, the primary key, an auto-incrementing
: ("identity" (aka "generator" on InterBase)) field.

: **TWO**
: _EntryDateTime_, filled with the SQL server's date and time

: **THREE*
: _EntryBy_, filled with the SQL client (user name of the person) that
: performed the insertion

: I believe the problem has to do with Delphi not liking the idea that the
: SQL server is modifying the record after it has so carefully inserted it.
: Delphi must be doing some sort of comparison of the before and after image
: and not liking what it sees.  The record DOES get inserted correctly.  If
: I do two or three of these "botched" inserts in my program, and then
: cruise out to MS Access to browse the table, I see all of the new records,
: the auto-filled and user-filled fields existing in complete harmony.

: I guess the real problem here is that Delphi is raising an exception when
: it really doesn't need to.  A proper fix would be to deal with the problem
: correctly.  A quicker one would be to trap the exception before it reaches
: the desktop and quash it. Something like if (operation=Insert) and
: (exception type=EDBEngineError) and (message="Record/key deleted") then
: just live with it.

: Can anyone offer help on either approach?  Any advice is appreciated.
: Thanks in advance.

: - Kevin Pierce

I had the same problem using Interbase. The only advice can give is to
keep away from mechanisms that modify records other than triggers. I spent
hours with the above error inserting records into an Interbase table from a
Delphi app. As soon as I removed all "default" directives in the defining
sql-file, the problem was gone. Nevertheless, I would love to hear that
somebody else has solved the problem.
Regards,

Andy

This is an important thread and I hope it gets some airplay.  I was wondering
idly if you could tell Delphi to immediately re-read the record after it was
inserted, so that Delphi would have a fresh picture of what the server record
contains...?

/mr/

If I remember correctly I used the second approach ("ignoring" the
incorrect exception) on a Delphi 1.0 <-> MS SGLServer 6.0 application
(SQL Links), and it worked well (at least to my knowledge...).
(I'm very found of "Counter/Autoincrement" fields).

Does anyone know whether the new/coming D2 Client Server Update will
finally behave as "expected" regarding Delphi + MS SQLServer 6.x
development?

Regards,

Jarle stabell

Classic problem with SQL tables.
Background: you post some data and the server changes it, but BDE
matches the record with the posted values, but can't find it, because
the server changed it.

Here are two of some possible solutions:
1. if the server does not change the Key field: set the Indexfieldnames
property to the key field(s) and UpdateMode to UpdateWherekeyonly
(something like).
2. General: use Refresh method or Close/Open after posting record.

Hope this helps!

BTW, another thing. This error may occur if posting to a query (view)
that excludes the inserted data and the record disappears after
refreshing (possibly confusing the user). Just a thought.

--
-------------------------------------
Robert Cerny - application designer & developer
Neosys Ltd. Ljubljana
Email: robert.ce...@neosys.xrs.si

I described the original problem.  This is a reply to a reply. - Kevin

In article <4q665a$1...@www.univie.ac.at>, atroe...@nelly.mat.univie.ac.at

(* SNIP - see the earlier posts *)

Andy:

That is one way to handle it, but as I see it, it is the job of the server
to enforce the rules that I have set up, like autoincrementing certain
fields, or date/time stamping others.  That's what separates it from being
just a big hard-drive.  I mean, if I want to do everything myself I'd use
Btrieve or Paradox files.  The idea of having a database server is that I
can tell it to do things on its own.  I can assign it certain tasks, and I
KNOW that they will get done, as opposed to placing the burden of
enforcing these rules on the application developer.  

This database will have many different applications accessing it,
performing both reads and writes.  Many different programs (running on
different hardware platforms) will be performing uploads of data into it.
I need the SQL server, not the apps, to bear the responsibility of
numbering, date/time, and user-name stamping the records as they are
inserted.

By the way, the above behaviors are just the tip of the iceberg.  I intend
to use all the bells and whistles, especially the referential integrity
features.  I mean, Microsoft is selling the hell out of it (they've got
some of my bucks), and they claim to have all these great features.  You
don't buy a fast car to drive in the slow lane.

- Kevin  

: I described the original problem.  This is a reply to a reply. - Kevin

: In article <4q665a$1...@www.univie.ac.at>, atroe...@nelly.mat.univie.ac.at

: >Subject:  Re: D2: EDBEngineError: 'Record/key deleted'
: >From:     atroe...@nelly.mat.univie.ac.at (Andreas Troester)
: >Date:     18 Jun 1996 12:07:06 GMT
: >
: >: Help!
: >
: >: I'm using D2 to access an MS SQL 6.5 server via SQL links.  I've used
: D1
: >: for plenty of prior projects, but this is my first crack at a database
: app
: >: and I'm getting an error that might be a piece of cake for an
: experienced
: >: Delphi DB programmer (I grovel in your omnipotent presence), but it's
: >: kicking the hell out of me.

: (* SNIP - see the earlier posts *)

: >: Thanks in advance.
: >
: >: - Kevin Pierce
: >
: >I had the same problem using Interbase. The only advice can give is to
: >keep away from mechanisms that modify records other than triggers. I
: spent
: >hours with the above error inserting records into an Interbase table from
: a
: >Delphi app. As soon as I removed all "default" directives in the defining
: >sql-file, the problem was gone. Nevertheless, I would love to hear that
: >somebody else has solved the problem.
: >Regards,
: >
: >Andy

: Andy:

: That is one way to handle it, but as I see it, it is the job of the server
: to enforce the rules that I have set up, like autoincrementing certain
: fields, or date/time stamping others.  That's what separates it from being
: just a big hard-drive.  I mean, if I want to do everything myself I'd use
: Btrieve or Paradox files.  The idea of having a database server is that I
: can tell it to do things on its own.  I can assign it certain tasks, and I
: KNOW that they will get done, as opposed to placing the burden of
: enforcing these rules on the application developer.  

: This database will have many different applications accessing it,
: performing both reads and writes.  Many different programs (running on
: different hardware platforms) will be performing uploads of data into it.
: I need the SQL server, not the apps, to bear the responsibility of
: numbering, date/time, and user-name stamping the records as they are
: inserted.

: By the way, the above behaviors are just the tip of the iceberg.  I intend
: to use all the bells and whistles, especially the referential integrity
: features.  I mean, Microsoft is selling the hell out of it (they've got
: some of my bucks), and they claim to have all these great features.  You
: don't buy a fast car to drive in the slow lane.

: - Kevin  

Hi Kevin!

I fully agree with you. I just wanted to report my particular "solution" to
you - of course, the advice not to make use of an important feature of a
server (like DEFAULT directives) can hardly be considered a solution.
THe same applies (in my opinion) to other ideas like ignoring the exception
or closing and opening the dataset.
Also  wanted to emphasize that there are problems similar to yours using
Interbase (look at the other articles in this thread) instead of MS
SQL-Server. Therefore one is led to think that this is a general problem of
Delphi (or the BDE) interacting with database servers. I have mailed a
similar report to Steve Koterski half a year ago, but somehow he was unable
to reproduce the error (unfortunately it seems that he doesn't follow this
newsgroup anymore - ?).
What I do not fully understand is the following:

*********************************************************************
What is the difference between modifying data through BEFORE INSERT
triggers (this obviously works) and DEFAULT directives (this doesn't)?
It seems that DEFAULT directives fire after inserts!
(*********************************************************************

The idea of changing UpdateMode to WhereKeyOnly might be worth considering,
but I havent tried it yet, and I also imagine that there are scenarios where
you do not want to do that.
I am  definitely not an expert on the subject, but to me this continues to be
an open problem.
Just a few thoughts from your partner in crime,

Andy

In article <4q4uit$...@newsbf02.news.aol.com>, ke...@aol.com (Kev1n)
writes:

Yes.

Great C/S SQL Link Tips:
http://www.borland.com/TechInfo/sqllinks/tips.html

The answer to my specific problem:
http://www.borland.com/TechInfo/sqllinks/text/idtykey.txt

I'd like to thank everyone that contributed.  Even though I eventually
found the answer on Borland's web page, the insights offered by everyone
else certainly helped me.

Make sure to check out the first site.  It's an excellent resource, and
it's done directly by Borland.

- Kevin