Board index » delphi » Resetting AutoInc fields in Paradox

Resetting AutoInc fields in Paradox

Aside from using the Database Desktop to copy the structure of a paradox
table to a new one, is there a way or a utility to reset a Paradox AutoInc
to one (for any empty table) or to the next number after the maximum value
for the field?
 

Re:Resetting AutoInc fields in Paradox


You would have to restructure the table and change the field type to long
integer then restructure the table and change the field type back to
autoinc. An alternative is to generate your own autoinc value. Create a
single field single record table to hold the last number used then use the
following code to get the next value.

function dgGetUniqueNumber(LastNumberTbl: TTable): LongInt;
{Gets the next value from a one field one record
 table which stores the last used value in its first
 field.  The parameter LastNumberTbl is the table
 that contains the last used number.}
const
  ntMaxTries = 100;
var
  I,
  WaitCount,
  Tries:         Integer;
  RecordLocked:  Boolean;
  ErrorMsg:      String;
begin
  Result := 0;
  Tries := 0;
  with LastNumberTbl do
  begin
    {Make sure the table contains a record.  If not, add
     one and set the first field to zero.}
    if RecordCount = 0 then
    begin
      Insert;
      Fields[0].AsInteger := 0;
      Post;
    end; file://if
    {Try to put the table that holds the last used
     number into edit mode. If calling Edit raises
     an exception wait a random period and try again.}
    Randomize;
    while Tries < ntMaxTries do
      try
        Inc(Tries);
        Edit;
        Break;
      except
        on E: EDBEngineError do
          {The call to Edit failed because the record
           could not be locked.}
          begin
            {See if the lock failed because the record is locked by
             another user.}
            RecordLocked := False;
            for I := 0 to Pred(E.ErrorCount) do
              if E.Errors[I].ErrorCode = 10241 then RecordLocked := True;
            if RecordLocked then
            begin
              {Wait for a random period and try again.}
              WaitCount := Random(20);
              for I := 1 to WaitCount do
                Application.ProcessMessages;
              Continue;
            end else
            begin
              {The record lock failed for some reason other than another
               user has the record locked. Display the BDE error stack
               and exit.}
              ErrorMsg := '';
              for I := 0 to Pred(E.ErrorCount) do
                ErrorMsg := ErrorMsg + E.Errors[I].Message +
                  ' (' + IntToStr(E.Errors[I].ErrorCode) + '). ';
              MessageDlg(ErrorMsg, mtError, [mbOK], 0);
              Exit;
            end; file://if
          end;
      end; {try}
    if State = dsEdit then
    begin
      Result := Fields[0].AsInteger + 1;
      Fields[0].AsInteger := Result;
      Post;
    end
    else
      {If the record could not be locked after the
       specified number of tries raise an exception.}
      raise Exception.Create('Cannot get next unique number.
(dgGetUniqueNumber)');
  end;
end;

--
Bill

Bill Todd (TeamB)
(TeamB cannot respond to questions received via email)

Other Threads