SPECIFIC EXAMPLE FOR HANDLING THE INSERTION OF NEW RECORDS INTO A 
MS SQL 6.0 TABLE WHOSE INDEX CONTAINS AN IDENTITY COLUMN TYPE


When using an Identity column in MS Sql Server 6.0 you may encounter 
two different problems when inserting a new record.  In both cases 
the identity column is also the primary key of the table.

The BDE defaults to a primary key column requiring a value on inserted 
records.  Unless you are using the MS SQL Server 'set insert on' mode 
you will leave the identity field blank when inserting a record.  This 
will cause the BDE to raise an exception because, in this scenario, the 
identity field is the primary key.  The following steps to solve this problem 
assume your table component is named table1 and the identity column is called 
IDFIELD.


STEPS:
1) Right click on the Table1 component
2) Choose Fields editor...
3) Choose Add...
4) Choose OK  [This will allow all of the columns in the table to be seen]
5) Close  Table1's Fields editor dialog box
6) Using the Object selector of the Object Inspector choose Table1IDFIELD.
7) Change the Required Property, listed in the Object Inspector, from True 
to False.

8)Create a default exception handler (outlined below).

Information related to the BDE's use of optimistic locking (used for remote
server inserts, updates, and deletes) can be found in the BDE (installed with the
Borland Database Engine), the BDE32.HLP in your BDE32 directory (installed 
with Delphi 2.0) and the Delphi help under the UpdateMode Property topic.

9)Refreshing your "view" of the newly inserted record can be done by using the 
@@identity variable specific to your connection to the MS SQL 6.0 server.
@@Identity will return the last value inserted into the identity 
column for your session, exclusively.  Once you have the value use the findKey 
method to move to the newly inserted record.  

After detecting the 'Record/Key deleted.' error in your exception handler
(outlined below under "Exception handler and related code") the following 
code in the Table1AfterCancel procedure will refresh and move 
Table1's cursor to the last record you inserted.

After a sucessfull post the BDE will attempt to update the cursor position to 
match the record that has just been updates/inserted. In this example the record 
the BDE was sitting on had a 'blank' value in the primary key field. When the 
refresh occurs the 'blank' value no longer exists.  The BDE cannot stay focused 
on a record that has a primary key that does not match a record in the newly 
refreshed data set hence the use of the @@identity and table1.findkey.

procedure TForm1.Table1AfterCancel(DataSet: TDataSet);
begin
table1.disablecontrols;
query1.open;
table1.findkey([query1.fieldbyname('Column1').asstring]);
query1.close;
table1.enablecontrols;
end;

10)Query1 (against the remote server) contains the following string,

select @@identity

The above solution was tested on a 10 record and 100,000 record table where 
the identity field was the primary key.  No performance difference between the 
two tables was noticed by the testers.


-Exception handler and related code-

For a list of BDE error constants please see:
Delphi 1.0 \DOC\dbierrs.int
Delphi 2.0 \DOC\bde.int

type
    ...
    procedure DefaultException(Sender: TObject; E: Exception);
    ...

uses BDE;

procedure TForm1.DefaultException(Sender: TObject; E: Exception);
begin
  if E is EDBEngineError then
  begin
    if EDBEngineError(E).Errors[0].ErrorCode = DBIERR_KEYORRECDELETED then
    begin
      try
        table1.cancel;
(*	
if you wish to see the text of the error you may use a label or statusbar (Delphi 2.0)
statusbar1.Panels.Items[0].Text := 
'New record posted/refreshed ('+EDBEngineError(E).Errors[0].message+')';
*)
      except
        raise Exception.Create('Cancel Failed: ' + 
	Exception(ExceptObject).message);
      end;
    end
    else
      Application.ShowException(E);
  end
  else
    Application.ShowException(E);
end;

{initialize your exception handler in the FormCreate}
procedure TForm1.FormCreate(Sender: TObject);
begin
application.showhint:=true;
Application.OnException := DefaultException;
end;


ID:CS1018

BEC, CJ
3.18.96