Board index » delphi » IndexError with AutoInc field

IndexError with AutoInc field

Using Delphi 6, BDE, Paradox:

We have a table with a lot of records. From time to time we get an IndexError. When the error appears, we cant add new records to this
table. When I delete the primary and secundary index and add a new record, I get an already existing value in the AutoInc field.

For example:

AutoInc field

7080637 ...
7080638 <<<<<<<<<<<<<<<<<
7080639 ....
7080640 ...
7080641 ...
7080638 <<<<<<<<<<<<<<<<< ERROR

Knows anybody a solution for this problem ???

Thanks in advance

Joerg

 

Re:IndexError with AutoInc field


Restructure the table and change the primary key field type to long integer.
Restructure again and change the field type back to autoinc. If this is a
multi-user app make sure the BDE is configured as described in
http://community.borland.com/article/0,1410,15247,00.html. Also see
http://community.borland.com/article/0,1410,15209,00.html. If you want to
stop using autoinc fields and generate your own autoinc number I can send
you a multi-user safe routine to do so.

--
Bill
(TeamB cannot answer questions received via email)

Re:IndexError with AutoInc field


Hello Bill !!

Thank you for your quieck answer in the BORLAND COMMUNITY (137940 /
137945).

We discussed about stop using AutoInc in our project team and decided to
generate our own autoinc number. You wrote in your answer, you can send
us a multi-user safe routine to do so. Please send us this routine ...

Thanks in advance !!!

Joerg

"Bill Todd \(TeamB\)" <bill_nos...@dbginc.com> wrote:

Quote
>Restructure the table and change the primary key field type to long integer.>Restructure again and change the field type back to autoinc. If this is a
>multi-user app make sure the BDE is configured as described in
>http://community.borland.com/article/0,1410,15247,00.html. Also see
>http://community.borland.com/article/0,1410,15209,00.html. If you want to
>stop using autoinc fields and generate your own autoinc number I can send
>you a multi-user safe routine to do so.

>--
>Bill
>(TeamB cannot answer questions received via email)

Re:IndexError with AutoInc field


Create a single field single record table to hold the last number that
has been used. When you need the next number for a new record call the
following routine. Normally you would do this in the OnNewRecord event
handler of your dataset.

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; //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; //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 (TeamB)
(TeamB cannot respond to questions received via email)

On 2 May 2002 00:15:25 -0700, "Joerg Hasselbusch"

Quote
<Joerg.Hasselbu...@Alcatel-ke.de> wrote:

>Hello Bill !!

>Thank you for your quieck answer in the BORLAND COMMUNITY (137940 /
>137945).

>We discussed about stop using AutoInc in our project team and decided to
>generate our own autoinc number. You wrote in your answer, you can send
>us a multi-user safe routine to do so. Please send us this routine ...

>Thanks in advance !!!

>Joerg

>"Bill Todd \(TeamB\)" <bill_nos...@dbginc.com> wrote:
>>Restructure the table and change the primary key field type to long integer.>Restructure again and change the field type back to autoinc. If this is a
>>multi-user app make sure the BDE is configured as described in
>>http://community.borland.com/article/0,1410,15247,00.html. Also see
>>http://community.borland.com/article/0,1410,15209,00.html. If you want to
>>stop using autoinc fields and generate your own autoinc number I can send
>>you a multi-user safe routine to do so.

>>--
>>Bill
>>(TeamB cannot answer questions received via email)

Other Threads