Auto Increment primary key field in a sql database when adding a new record on a delphi form

You do not mention which SQL database you are using, but this is what I use with Microsoft SQL Server.  I have a stored procedure I call that creates a new Orders record:

CREATE PROCEDURE CF0010_NewOrder
AS
set nocount on;
declare @MaxOrder Integer;
select @MaxOrder = IsNull(Max(OrderNum),110000)
  from orders;
set @MaxOrder = @MaxOrder + 1;
Insert
  Into Orders (OrderNum, SubOrderNum)
       VALUES (@MaxOrder, 0) ;
select OrderNum = @MaxOrder ;
set nocount off;

The key to this is that the OrderNum and SubOrderNum are the key fields for the Orders table.  This procedure insures that the table is initialized if starting from scratch, inserts the new next-order into the table and then returns the OrderNum as an integer to the calling procedure.  No transaction processing is done at the stored procedure level as I handle all of that in Delphi code myself.

I call this stored procedure like this:

var
  rs_tmp : TADODataset;
...
          with RS_Tmp do
            begin
              Close;
              // connection info is stored in the properties on the TAdoDataset but could be defined here
              //connection := ...
              CommandType := cmdStoredProc;
              CommandText := 'CF0010_NewOrder;1';
              Active := True;
              OrderNum := (RS_Tmp.FieldByName('OrderNum').AsInteger);
              Close;
            end;

The new OrderNum is returned and then you can prepend  your "OTY/" to the returned integer for whatever you need it to do.  

John

Most DB-Systems are providing a internal autoincement field schema

maybe your db too

just tell us, what database u use

meikl ;-)

in case it is firebird/interbase (no autoincrement) use a generator

Hi,
First i agree with Mr.kretzschmar  that most DataBases are providing a internal autoincement field schema, But if u care to do this by code u can use a SQL Statements .. as follow :

1- Prepare a (Query) that connects with ur ADOConnection
2- Let we suppose PrimaryField is F1 and another Field is F2
3- Make this procedure

Procedure GetNewNo ;
Var
PrimaryNo : integer;
Qty            : String;
begin
   Query.Close;
   Query.SQLClear;
   Query.SQL.Add ('  SELECT  (FieldName F1) = IsNull (Max(FieldName F1) +1 ,1) ' + #13 +
                               '  FROM     YourTable' );
   Query.Open;
   PrimaryNo:= Query.Fields[0].asInteger;
   Qty :=  'your string Part here' + IntToStr(PrimaryNo)  ;

   Query.Close;
   Query.SQLClear;
   Query.SQL.Add ('  INSERT INTO YourTable  (F1, F2)   ' + #13 +
                               '  Set  Values (:v1, :v2)                     ' );
  Query.Parameters[0].value:= PrimaryNo;
  Query.Parameters[1].value:= Qty;
  Query.ExecSQL;
end;

Now .. Call this Procedure in OnNewRecord Event ..
just try it ;)

/Romio