Auto Increment And Data Buffering Problems

[Redsz]

Hey guys im back with a couple of questions. I will use table test for demonstration. This table has had a logical field "lsend" and an auto increment field "nid" added to it.

ALTER TABLE test ADD COLUMN nid i
replace ALL nid WITH RECNO()
	
CALCULATE MAX(nid) TO lnMax
lcMax = TRANSFORM(lnMax)
	 
ALTER TABLE test alter COLUMN nid i AUTOINC NEXTVALUE &lcMax + 1 STEP 1 
ALTER TABLE test ADD COLUMN lsend l DEFAULT .T.

The table has a validation rule of setsend() saved in a db stored procedure.

FUNCTION setsend()
    IF TYPE("lsend") = "L"
	    IF CURVAL("lSend") = lsend
	        replace lSend WITH .T.
	    ENDIF
    ENDIF 
ENDFUNC

When data buffering is enabled an insert into statment will not work!! Error message is data type mismatch!

However an append blank followed by a replace statment does.

This fails:

insert into test(cdesc);
 values("TEST")

This works:

select test
append blank
replace test.cdesc with "TEST"

If i remove the validation rule the statment still does not work which leads me to believe the auto increment field is the culprit!! Does anybody see a solution to this??

Thanks in advance!!

Note any type of data buffering (2-5) causes the above code to fail.

 

[Mike Lewis]

Redsz,

I suspect that the Autoinc field is not to blame (you can verify that by removing the Autoinc and testing it again).

I think it's more likely to be caused by the buffering. When you issue an SQL command against a buffered table, it actually works on the underlying table, rather than the table in the buffer. I'm not sure why that would give you a data type mismatch, but I think the problem is somewhere in that area.

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]

If the auto increment field is removed the insert statement works correctly.

Does anybody see a solution to this or a way around it as im 100% sure its related to data buffering.