Title: Get an unused number from Table
Question: How to to find a hole in database records, get an unused number , generate next available number
Answer:
The GenMaxNo generates the usable number from the given table without using any external table. It has a facility to get Max number or Missed+Max number of any given table.
Eg: If EmpNo in the Employee table has got 1,2,4,6,8,9,10 ... This function will return 3 as the next number.
This has been tested (P4-3.8GHz, 512MB RAM, WinXP) with 10000 records and by deleting all records with the incremental of 100. With this result is quite quick.
Code Follows:
Declare:-
type
GenSeqType = (stNull, stSequence, stReuseSequence);
//stSequence = Only Sequential Number
//stReuseSequence = Reuse as well as Sequential Number
Define:-
function TDataModule.GenMaxNo(SetSeqType: GenSeqType; TblNm, FldNm: String): Integer;
var
TotRec, {MinRec,} MaxRec, i: Integer;
begin
Result := 0;
with QryExecSql do begin
Close;
Sql.Clear;
Sql.Add('select Count(*) TotRec, Min('+FldNm+') MinRec, Max('+FldNm+') MaxRec from '+TblNm);
Open;
TotRec := FieldByName('TotRec').AsInteger;
//MinRec := FieldByName('MinRec').AsInteger;
MaxRec := FieldByName('MaxRec').AsInteger;
if (SetSeqType = stSequence) then begin
Result := MaxRec+1;
end else if (SetSeqType = stReuseSequence) then begin
if (TotRec=MaxRec) then Result := MaxRec+1
else begin
Close;
Sql.Clear;
Sql.Add('Select '+FldNm+' from '+TblNm+' Order By '+FldNm);
Open;
First;
i := 1; //Set the start sequence number here
while not EOF do begin
if (i=FieldByName(FldNm).AsInteger) then begin
Inc(i);
Next;
end else begin
Result := i;
Exit;
end;
end;
end;
Close;
end;
end;
end;
Use as:
procedure TDataModule.QryEmployeeAfterInsert(DataSet: TDataSet);
begin
qryEmployeeEmpNo.AsInteger := GenMaxNo(stReuseSequence, 'Employee', 'EmpNo');
end;
Another technique is creating an external table that contains a sequential number as high as you require.
Use this table to find the minimum value that doesnt currently match a row in your current table.
The above function is more versatile than handling through table. If you know any other better method please let me know.
Adesh Jain N
Bangalore India