How to retrieve current autoincrement value

[Redsz]

I am having problems with autoincrementing fields and data buffering in VFP8 SP1. I am getting a data type mismatch error when inserting into a buffered table.

I found a tidbit of information in the vfp 8 help file but unfortunatly do not know how to use it. I want to know how to get the current value of an autoincrement field as per the following.

The following is copied from the VFP 8 help file.


When table buffering is enabled, you can move away from the current record in the following CursorAdapter events:
BeforeInsert
AfterInsert
BeforeUpdate
AfterUpdate
BeforeDelete
AfterDelete

You can also modify data in the cursor. This functionality supports scenarios such as retrieving the autoincrement value from the base table and inserting it into the cursor. When this scenario occurs, the CursorAdapter object should automatically return to the record whose changes are about to be committed after the event has occurred and commit the changes.


Does anybody know how to get the value of the auto inc field and then insert it into the cursor?

 

[Mike Lewis]

Redsz,

The stuff you posted from the Help file is probably not relevant. It is talking about cursor adapters. You didn't say anything about using cursor adapters in your message.

The best thing would be if you could post the code you are using to insert the record.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Redsz]

The table that i am trying to insert to has an autoincrement field. When data buffering is enabled i am getting a data type mismatch error on the insert statement.

I was hoping i could somehow retrieve the current value of the autoincrement field and then use that value in my insert statement!

	INSERT INTO disphead ;
		(cdispno,cdinvno, ;
		ccomplete,linside,cstationid, ;
		nnumitems, ;
		nsubtotl, ntax1, ;
		ntax2, ntotal, ndisc, ;
		crecstatus,tcr8stamp) ;
		VALUES ;
		(pdispno, temp_salesdetail.cinvno, ;
		"I", temp_saleshead.lInside, pcWsID, ;
		lnNumItem, ;
		temp_saleshead.ntotsale, temp_saleshead.ntax1, ;
		temp_saleshead.ntax2, temp_saleshead.ntotretail, temp_saleshead.ntotdisc, ;
		"A", DATETIME())

 

[Redsz]

I guess what im asking is how to retrieve the nextvalue of the auto increment field from the table?

 

[BPeisch]

Why are you assuming your "Data type mismatch" error has anything to do with the autoincrement field?


-BP (Barbara Peisch)

http://www.peisch.com

 

[BPeisch]

Thinking on this further, which is your autoinc field? Have you included it in your INSERT INTO command? Don't do that!


-BP (Barbara Peisch)

http://www.peisch.com

 

[rgbean]

Redz,
The VFP help files states:

Record Locking
When you turn on autoincrementing for a field, the start and incremental values, Next Value and Step, are stored in the table (.dbf) header in the unused or reserved portion of the field subrecord for the specified field. Next Value is stored as a 4-byte integer. The Step value is stored as a 1-byte integer with a maximum value of 255. The value actually used to increment the field value is the sum of the value stored in the .dbf header and the incremental value. The operation sequence occurs as follows:
[ul]
[li]Perform an insert or append operation[/li]
[li]Lock header. [/li]
[li]Increase the currently stored increment value by the Step value and apply to the field. [/li]
[li]Store the new value, which is the value of the newly added record, in the .dbf file header. [/li]
[li]Unlock header when insert or append operation completes. The header contains the last incremented value.[/li]
[/ul]

You could obviously use Low-Level IO to retrive the values, but unless you plan on doing your updateing using LL IO, I think you're better off just letting VFP handle this.

Rick

 

[Mike Lewis]

Redsz,

I guess what im asking is how to retrieve the nextvalue of the auto increment field from the table?

If you mean you want to retrieve the next value before you insert the record, you can't do that. The reason should be obvious. How could you guarantee that no-one else has grabbed that value between the moment you retrieve it and the moment you insert your record?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Redsz]

Thanks for your replies. I have just about given up on this problem. If i use an append blank followed by a replace statement everything works correctly and i am not noticing a performance difference.

It just irks me that i cant figure out why this datatype mismatch error is occuring.