I want to insert a record with and the SQL insert statement. This work fine,
but I want to retrieve the value of an AutoIncrement field. I need this
value after the insertion of the new record. I use Access 97 with the BDE.
The following code works:

    wwTableUser.Append;

    wwTableUserfullName.AsString := user.fullName;
    wwTableUserLoginName.AsString := user.loginName;
    wwTableUserPassword.AsString := user.password;
    wwTableUserActive.AsBoolean := user.active;

    try
      wwTableUser.Post;
      result := SPCDataModule.wwTableUseruserId.AsInteger; // Return UserId
(autoincrement field)
    except
      raise EStorable.Create('Error:  post failed');
    end;

Do you know how retreive the value of the autoincrement field (userId) using
an SQL insert statement? Off cource I can search for the record just
inserted and then retrieve the value of the autoincrement field, but is
there also another way?

Thanks in advance.

If there is no need for multi-user operation, a SELECT
MAX(autoincfield) FROM table will get the value just created.
(Hopefully ;)
If you want to know more about the separate table method, let me know.

HTH,
Dan

CREATE OR REPLACE PROCEDURE InsertProc
        (vXx1 IN CHAR, vXx2 IN CHAR, vResult OUT CHAR)
AS
BEGIN
 INSERT INTO BrParc
 VALUES
   (vXx1,vXx2)
 RETURNING XX1 INTO vResult;
END;

So, call that proc from Delphi and it will return field from inserted
rec.

Thanks for you answer. I can not use the search max method, because it is a
multi-user program. Therefore I would ask you to give me some more
information on using the separate table method you mentioned.

Thanks in advance

Best regards,
 Arjan

1. create a table called 'Counter.db' [I'm assuming Paradox for
illustration only]. Give it an integer field for each different
counter you need, for example 'OrderNo' and 'CustNo'. Open the table
and set each field to your starting values; e.g. 1000. Use Database
Desktop, or Paradox, or ?? to make this table.

2. drop a query on a datamodule, say dm1, named qCounter. Set the SQL
property to SELECT * FROM "Counter.db"
RequestLive = true

3. put the following function in dm1:

function Tdm1.NextNum(typ: string): integer;
var
  nn: integer;
begin
  with qNumber do
  begin
    close;
    try
    open;   // this locks the record to other users
    edit;
    nn := FieldByName(typ).AsInteger;  // read current max value
    inc(nn);  // add one to it
    FieldByName(typ).AsInteger := nn;  // write the new value
    post;    // store the new value in the database
    result := nn;  // send back new value
    except
    result := -1;  // send back error code
    end;   // end of try-except block
   close;  // release the lock on the table
  end;
end;

4. Typical calling procedure:

Thanks for you explanation on using the separate table method. This will
help me a lot!