Hi guys, I'm sorry if this question has been asked before, but I'd
really appreciate it if someone could help me out here.

My Delphi form fails to insert new records into my database table!!!  

We're running MS SQLServer 6.0 thru ODBC, and the table in question
has an IDENTITY (i.e. auto counter) field, which is also the primary
key.  The Delphi form is your basic no-frills data entry form.

Evidently Delphi expects a value for the primary key (of course!) but
it is unaware that the server will supply one automatically.  How do I
resolve this problem??  

Many, many thanks in advance for all replies,

Simon.

BTW -- The error message I get goes like this:

        Project IV_MTRAN.EXE raised exception class EDatabaseError with
        message 'Field 'TranID' must have a value'. Process stopped. Use
        Step or Run to continue.

Simon,
  Had this same problem the first time I used identity properties.  What you
  should do is this:

  At some point after the table has been activated, set the primary key
  fields Required property to False.  For example,

  tblCustomer.FieldByName('ID').Required := False;

  That should do it.

--
Jasen M. Fici                  Sequoia Software
Product Manager                5457 Twin Knolls Rd.
                               Suite 310
ja...@sequoiasw.com            Columbia, MD 21045

Give up the identiy field now!!!!!  Save yourself tons of pain and
suffering.  The BDE does not like idenity or autoinc fields. I know
this sounds like a pain in the butt.  But it will work for all SQL
backends so if you ever migrate to ORACLE or another server you wont
have to rewrite your delphi app.  IDENTITY is unique to MS SQL.  Once
you get it set up you only need to call a storeprocedure to get a
unique number.  The other problem with IDENITY is that you don't know
what it is untill you post it.  We had a problem of how to create
child records if we did not know the IDENTIY yet?  The MS SQL stupid
solution was to post the customer then select the customer so we would
know the IDENTITY then use that number for the customers.

We spent weeks trying to get around the problem by using

Table1.IdentiyField.Required = False;
Table1.IdentiyField.ReadOnly = True;
Updatewhere = Changed;

This worked fine for simple applications.  However in complex
application where you use the "current row" as the master of a master
detail somewhere along the lines delphi would set the Modified propery
to true even when it did not change and would try to write the
IdentiyField back to the server.  Then SQL server would barf saying
someting like "cannot insert data into an identiy field".

This is the solution that I found on the net on on compuserve.

Use a table called SYSID to store the autoincrementing numbers

SYSID

TABLE_NAME        NEXT_ID
customers            4282
invoice                    39482
etc.....

Then have  a stored procedure that you pass the TABLE_NAME and it will
increment the NEXT_ID and return the unique number to delphi.

Then in all your tables "customers", "inovice" change the identiy
field to a int or someting.

When your app goes to create a new customer.  The first thing you do
is call the stored procedure to return a unique value.  Pop that
number into the correct field. and away you go.  

This is the readme I got with the Identiy VCL that scott wrote.  I did
not use the VCL I just used the stored proc and his theory.

I'll email you scotts zip file.

-----------------------------------------------------------------------------------------------------------------------------------
The files contained in this zip file are for providing a workaround to
the
problem with generating auto-id values using MS SQL Server 6.0 and
Delphi.
They are listed as follows: GENID.TAB, GENID.PRO, GENID.TRI, and
GENID.PAS.

[THE PROBLEM]
The 6.0 version of SQL Server provided a new column type called an
IDENTITY
column. It was intended to use this column type as a primary key for
any
table that needed a unique id automatically generated when a row was
inserted.

For some reason however, the BDE used by Delphi, refuses to
acknowledge the
value that is generated by IDENTITY columns. When inserting a row from
within
a Delphi application, a "Record/Key Deleted" message will be received.
I am
unclear as to what exactly is going on here, as the row of data
actually is
inserted. However, it will not become visible to the BDE(Delphi app),
until
the table is closed and reopened.

It appears that the BDE wants to be "responsible" for all primary key
values.
I am currently investigating this behavior.

[THE SOLUTION]
Through the generous help of another fellow Delphi/SQL 6.0 developer,
I
received the approach described here. I have embellished it somewhat,
but I
cannot claim ownership of most of the code here. The only parts I have
added,
are a trigger, which will be described later.

* Step #1
Run the GENID.TAB script file to create a table called SYSID. This
table
will be used for generating the auto-id values. The concept used here
is
that for each table that requires an auto-id value, there will be a
row
with the table's name in the SYSID table. The SYSID table also
contains
a column that is incremented via a stored procedure.

* Step #2
Run the GENID.PRO script file to create the stored procedure required
to
operate on the SYSID table. This procedure will be executed from
within
any Delphi application that uses an auto-id table.

* Step #3
This is the part I have added to this approach. My concern was that
although
this approach worked just great, I was limited to being able to insert
rows
into my auto-id tables with Delphi only. This was unacceptable, as my
environment requires me to provide multiple insert paths for my
tables.

That's when I got the idea of creating a trigger for each auto-id
table,
which would check to see if an auto-id had been generated. If it
hadn't,
as would be the case if an insert occurred outside of Delphi, then the
trigger would call the stored procedure, thereby generating the id
appropriately.

A generic script for this trigger is provided in GENID.TRI. Before
running
it, just replace the values as instructed in the script file. If you
are
only going to be maintaining your tables from Delphi apps, you can
skip
this step altogether.

* Step #4
Add the TIDTable component to your Delphi library. The code is located
in
GENID.PAS. The result will be a component that is inherited from the
TTable
class, which automatically calls the stored procedure when a new row
is
inserted into any table that requires an auto-id. You will use this
component
for these tables instead of the standard TTable component. It is
provided
merely as a convenience, so you won't have to always add a TStoreProc
component everywhere.

[SUMMARY]
My intent is to further expand the TIDTable component to provide more
flexibility and extension of features. I am also continuing to
investigate
the behavior of BDE, and its inability to work with IDENTITY columns.
I
will be glad to continue to share my findings with anyone interested.

If you have any questions or comments, please email me at 74403,1376.
Thanks
for your interest and good luck.

W. Scott Morgan
November 30th, 1995

Goodness me, that just what i'm doing.  
ODBC is so god dam slow though, anyway I am currently trying to wrap
my form edits/posts around a transaction.  I think this is the only
way to go?
I've managed to stop Delphi complaining by explicity posting and
refreshing (close/open) the query.  

But in my case everything is tied together relationally and I don't
really want to post anything without the user pressing the 'save'
button.  Hence I am using explicit transactions and the SQLPassthough
NOT SHARED mode, since they should not interfere with the delphi
implicit transactions.

If anyone has an alternative to using transactions, please let me
know.

Mark.

Thanks, Troy and everybody for your suggestions.  Now I've finally
managed to get everything working just like it was supposed to. ;-)

Simon Ee.