>This is the syntax I used to create the table:
>
>CREATE TABLE BASI.PRESIDENTS(KeyID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,LoginID CHAR(8), ...
>
>DB2 does the Key generation, I can't tell you much more than that. In VFP8 I use "SELECT IDENTITY_VAL_LOCAL() as NewIDValue FROM SYSIBM.SYSDUMMY1" To return a cursor with the last Key generated.
For the InsertCmdRefreshCmd, try that SQL Select instead --
SELECT IDENTITY_VAL_LOCAL() as KeyID FROM SYSIBM.SYSDUMMY1
FWIW, I did not do the testing using ODBC. I used ADO instead. I also tried selecting the current value of my generated PK from Oracle with no success [same error message as you got]. So, I am not holding out any hope that selecting the IDENTITY value will work in DB2 right now using ODBC or ADO. It does work in SQL Server though, and selecting the KEYID value from the source table in Oracle does work.
Does DB2 have the CAST() function where you might try:
SELECT CAST(IDENTITY_VAL_LOCAL() AS Integer) as KeyID FROM SYSIBM.SYSDUMMY1
... or cast it as whatever numeric data type is available in DB2 (e.g., NUMBER(8,0) or some such).
Don't get too discouraged, as the VFP Team is aware of this issue with non-SQL Server DBs and are working to correct it.
Mark McCasland
Midlothian, TX USA