Subject Re: autoincrement problem
From Mervyn Bick <invalid@invalid.invalid>
Date Tue, 17 Oct 2023 09:55:46 +0200
Newsgroups dbase.getting-started
Attachment(s) test_beginappend.wfm

On 2023/10/17 01:10, Charlie wrote:
> Hi..  I am using autoincrement field type.  Seems to work ok if I append from a table.  But when I try to add using beginappend() instead of increasing the number in the field it goes back to 1.  I tried alter table but couldn't get that to work either.  I have the field indexed.  Not sure if that could be causing the problem.
For quite some time before I switched from .dbf tables to Firebird I
used an autoinc field in all my .dbf files even though this field was
never used in my programs.  This was because when the tables were moved
to Firebird they would need a primary key.

I never once had a problem with using the rowset's beginAppend() method
to add records.  The correct value was always placed in the autoinc
field which can't be edited by hand.

A little example form is attached.

The only time an autoinc field should ever revert to 1 is if the table
is dropped or deleted and then rebuilt from scratch.  Even if one ZAPs
and PACKs a table to remove all existing records the autoinc value will
continue from where it left off.

We need to see some code to be able to work out what is causing the
autoinc value to revert to 1.

Mervyn.




if file('test_beginappend.dbf')
// drop table test_beginappend
endif

if not file('test_beginappend.dbf')
   create table test_beginappend  (id autoinc,data character(15))
   use test_beginappend exclusive
   index on id tag id
   use
endif

** END HEADER -- do not remove this line
//
// Generated on 2023-10-17
//
parameter bModal
local f
f = new test_beginappendForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class test_beginappendForm of FORM
   with (this)
      height = 16.0
      left = 13.7143
      top = 7.2273
      width = 58.1429
      text = ""
   endwith

   this.TEST_BEGINAPPEND1 = new QUERY(this)
   with (this.TEST_BEGINAPPEND1)
      left = 2.0
      width = 14.0
      height = 1.0
      sql = 'select * from "D:\Examples\Plus2019\test_beginappend.DBF"'
      active = true
   endwith

   with (this.TEST_BEGINAPPEND1.rowset)
      indexName = "ID"
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.test_beginappend1.rowset
      height = 11.1818
      left = 8.5714
      top = 0.6364
      width = 42.2857
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::PUSHBUTTON1_ONCLICK
      height = 1.0909
      left = 3.7143
      top = 13.2273
      width = 15.2857
      text = "Add 3 records"
   endwith

   this.PUSHBUTTON2 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON2)
      onClick = class::PUSHBUTTON2_ONCLICK
      height = 1.0909
      left = 22.5714
      top = 13.2273
      width = 15.2857
      text = "Drop table"
   endwith

   this.PUSHBUTTON3 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON3)
      onClick = class::PUSHBUTTON3_ONCLICK
      height = 1.0909
      left = 41.4286
      top = 13.2273
      width = 15.2857
      text = "ZAP table"
   endwith

   this.rowset = this.test_beginappend1.rowset

   function PUSHBUTTON1_onClick()
      for n = 1 to 3
        form.rowset.beginappend()
        form.rowset.fields['data'].value = ''+n
        form.rowset.save()
      next
      return

   function PUSHBUTTON2_onClick()
      form.test_beginappend1.active = false
      drop table test_beginappend
      create table test_beginappend  (id autoinc,data character(15))
      use test_beginappend exclusive
      index on id tag id
      use
      form.test_beginappend1.active = true
      return

   function PUSHBUTTON3_onClick()
      cSafety = set('safety')
      set safety off
      form.test_beginappend1.active = false
      use test_beginappend exclusive
      zap
      pack
      use
      set safety &cSafety
      form.test_beginappend1.active = true
      return

endclass