Auto Incrementing Fields

[Redsz]

I have added an auto incrementing field (nid) to several tables.

ALTER TABLE dispdetail ADD COLUMN nid i AUTOINC NEXTVALUE RECCOUNT() +1 DEFAULT RECNO()

When inserting into this table now i am getting data type mismatch error. All fields are correct in the insert sql statement however when you check the table the auto increment field is empty.

With the append blank and replace statment's everything works correctly and the new auto increment id is correct.

There is table buffering of 5 in place.

Does anybody have any idea of what piece of the puzzle i am missing?? Thank you for your response.

SELECT dispdetail
APPEND BLANK

replace dispdetail.cdinvno WITH temp_salesdetail.cinvno, ;
dispdetail.cdispno WITH pdispno, ;
dispdetail.cilinekey WITH temp_salesdetail.cilinekey,;
dispdetail.cqty WITH lcQty, ;
dispdetail.nprice WITH temp_salesdetail.ntotretail, ;
dispdetail.cdesc2 WITH temp_salesdetail.cdesc, ;
dispdetail.cdetailid WITH temp_salesdetail.cdetailid, ;
dispdetail.cstationid with pcWsID


INSERT INTO dispdetail ;
(cdinvno,cdispno, ;
cilinekey,cqty, ;
nprice, ;
cdesc2,cdetailid, ;
crecstatus,tcr8stamp, ;
cstationid) ;
VALUES ;
(temp_salesdetail.cinvno, pdispno, ;
temp_salesdetail.cilinekey, lcQty, ;
temp_salesdetail.ntotretail, ;
temp_salesdetail.cdesc, temp_salesdetail.cdetailid, ;
"A", DATETIME(), ;
pcWsID)

 

[BPeisch]

Use STEP 1 instead of DEFAULT RECNO() in your ALTER TABLE command.


-BP (Barbara Peisch)

http://www.peisch.com

 

[Redsz]

Do you mean use step 1 as the next value?

 

[Redsz]

I am convinced that this has something to do with table buffering? If i disable table buffering the insert statement works correctly. With buffering turned on everything except the auto increment field is inserted?

Anybody see a cause / solution to this?

Thank you

 

[Mike Lewis]

Redsx,

AUTOINC NEXTVALUE RECCOUNT() +1 DEFAULT RECNO()

That doesn't look right to me. I would set the next value to the actual value of the next key at the time that you run ALTER TABLE, and leave out the DEFAULT clause out (it doesn't do anything with an autoinc field).

Bear in mind that the next key could be less than the record count, even if you used consecutive keys before you added the autoinc field. That would happen, for example, if you deleted some records earlier. Best not to rely on RECCOUNT().

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 mike. The reason i had added the recno() is that when adding an auto increment field to a table that allready has records the field is populated with zero's. To get around this i added the default of recno() which populates correctly. So i have had several suggestions to remove the recno entirely i have made some alterations and ended up with the following.

ALTER TABLE dispdetail ADD COLUMN nid i
replace ALL nid WITH RECNO()
ALTER TABLE dispdetail alter COLUMN nid i AUTOINC

look ok?

 

[Mike Lewis]

Redsz,

No, that won't work either.

The first two lines look OK. But in the third line, when you add the AutoInc field, you still need to specify the NEXT VALUE. If you don't, it will insert the first AutoInc as 1, which is already present, you will either get a duplicate value or a index violation error.

I would suggest something like this:

ALTER TABLE dispdetail ADD COLUMN nid i
replace ALL nid WITH RECNO()
CALCULATE MAX(nid) TO lnMax
lcMax = TRANSFORM(lnMax)
ALTER TABLE dispdetail alter COLUMN nid i AUTOINC NEXT VALUE &lcMax

I haven't tested that, but it should work, I think.

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

 

[jroldan]

I have a table with auto increment on with a start value already up to 33, and it steps up by 1. When I go to update a record and decide to cancel it, the table still updates the auto increment value by 1, which throws off my count since now the value is at 34.
Is there a way to set the auto increment back to 33 programmatically?

I'm using VFP8

Thanks,
Jerry

 

[Mike Lewis]

Jerry,

This is the normal behaviour and is fully documented. In fact, the Help is quite clear that you should not rely on Autoincs to be consecutive.

As far as I know, the only workaround would be to change the Autoinc to a normal integer, change the 34 to 33, and then change the field back to Autoinc. You would need exclusive use throughout the process.

Personally, I would live with it the way it is.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[chpicker]

Curiosity question...in which version of VFP did they add the AUTOINC ability? I don't think it's available in 7, which is what I'm still using.

Ian

 

[Mike Lewis]

Ian,

The answer is 8.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[chpicker]

Heh...that figures. Thanks!

 

[jroldan]

Thanks Mike, I just needed to make sure there wasn't a easier way. Right now I'm changing the field to an integer manually through the command Windows, and running this simple code:
x=1
Do while !eof()
replace file_cnt with x
skip 1
x=x+1
enddo
I just thought there was a way to do that within the program I wrote. You would think it would be as simple as making AUTOINC readable and replacing each line value accordingly.

 

[TamarGranor]

If having the numbers in sequence is important to you, then you probably don't really want an AUTOINC field. AUTOINC is really intended for surrogate primary keys, fields use to uniquely identify records to which users are never given access.

Tamar

 

[jroldan]

I was attempting to do some clean up and repair on table.
I tried writing code for it and came to this issue so I figured I'd ask and make sure I wasn't doing anything wrong. I appreciate everyones feedback on this.

Thanks,
Jerry

 

[billarnold]

To Mike Lewis ... thanks for the snippet. Comes in handy because I'm starting to use autoinc for new tables and had it on my dolist to code a utility to handle adding this field automatically, taking into account existing records. I made some slight changes and thought I'd post the tested revision:

PARAMETERS cTabFQFN
ALTER TABLE (cTabFQFN) ADD COLUMN nFileno i
SELECT (JUSTSTEM(cTabFQFN))
replace ALL nFileno WITH RECNO()
CALCULATE MAX(nFileno) TO lnMax
lcMax = TRANSFORM(lnMax + 1)
ALTER TABLE (cTabFQFN) alter COLUMN nFileno i AUTOINC NEXTVALUE &lcMax
return

to call: oUtils.add_fileno("c:\temp\tfileno.dbf")


Bill Arnold

 

[Nifrabar]

Maybe this may help as well.
Here's my way of 'synchronising' the autoinc. value of the dbc.

FUNCTION UpdateAutoIncID
LPARAMETERS tcTableName, tcIDfieldName
LOCAL lnAantalRecords, lnLastID, lnNextID
USE (tcTableName) IN 0
SET DELETED OFF 
* xx = # of records of table	
SELECT count(*) as xx FROM &tcTableName INTO CURSOR tmp
lnAantalRecords = xx
select MAX(&tcIDfieldName) as xx FROM &tcTableName INTO CURSOR tmp
lnLastID = xx
lnNextID = MAX(lnAantalRecords,lnLastID) + 1
USE IN &tcTableName
ALTER TABLE &tcTableName ALTER COLUMN &tcIDfieldName I NOT NULL AUTOINC NEXTVALUE lnNextID STEP 1
USE IN &tcTableName

-Bart