I am having difficulty updating, deleting, and inserting records in multiple SQL tables with an auto-incrementing identity field as the primary key. Below I have included the .mas, .acx and the update/delete logic. The SQL error returned is 0, but focerror is either 1 or -22.

.mas
FILENAME=OFFENDEREDVRI, SUFFIX=SQLMSS , $
SEGMENT=OFFENDEREDVRI, SEGTYPE=S0, $
FIELDNAME=OFFENDEREDVRIID, ALIAS=OffenderEdVRIID, USAGE=I11, ACTUAL=I4, ,MISSING = OFF, FIELDTYPE=R, $
FIELDNAME=DOCID, ALIAS=DocID, USAGE=A11, ACTUAL=A11, $
FIELDNAME=VRIRANK, ALIAS=VRIRank, USAGE=I6, ACTUAL=I2, $
FIELDNAME=LUVRIID, ALIAS=luVRIID, USAGE=I11, ACTUAL=I4, $
FIELDNAME=CREATEDDATE, ALIAS=CreatedDate, USAGE=HYYMDs, ACTUAL=HYYMDs, $
FIELDNAME=CREATEDBY, ALIAS=CreatedBy, USAGE=A7, ACTUAL=A7, $
FIELDNAME=MODIFIEDDATE, ALIAS=ModifiedDate, USAGE=HYYMDs, ACTUAL=HYYMDs, $
FIELDNAME=MODIFIEDBY, ALIAS=ModifiedBy, USAGE=A7, ACTUAL=A7, $

.acx
SEGNAME=OFFENDEREDVRI, TABLENAME=databaseName.dbo.OffenderEdVRI,
CONNECTION=dbConection, KEYS=1, $
FIELD=OFFENDEREDVRIID, AUTOINCREMENT=YES, START=1, INCREMENT=1, $
FOREIGN_KEY=FK_OffenderEdVRI_DCSOFNDR01,
PRIMARY_KEY_TABLE=databaseName.dbo.DCSOFNDR01, FOREIGN_KEY_COLUMN=DocID,
PRIMARY_KEY_COLUMN=DOC_ID, $
FOREIGN_KEY=FK_OffenderEdVRI_DCSSECUR01_CreatedBy,
PRIMARY_KEY_TABLE=databaseName.dbo.DCSSECUR01, FOREIGN_KEY_COLUMN=CreatedBy,
PRIMARY_KEY_COLUMN=USER_ID, $
FOREIGN_KEY=FK_OffenderEdVRI_DCSSECUR01_ModifiedBy,
PRIMARY_KEY_TABLE=databaseName.dbo.DCSSECUR01,
FOREIGN_KEY_COLUMN=ModifiedBy, PRIMARY_KEY_COLUMN=USER_ID, $
FOREIGN_KEY=FK_OffenderEdVRI_luVRI, PRIMARY_KEY_TABLE=databaseName.dbo.luVRI,
FOREIGN_KEY_COLUMN=luVRIID, PRIMARY_KEY_COLUMN=luVRIID, $

.mnt code
FOR ALL DELETE offenderedvri.OFFENDEREDVRI.DOCID FROM StkOffenderVRIDelete;

FOR ALL INCLUDE offenderedvri.OFFENDEREDVRI.DOCID
offenderedvri.OFFENDEREDVRI.VRIRank
offenderedvri.OFFENDEREDVRI.luVRIID
offenderedvri.OFFENDEREDVRI.CreatedDate
offenderedvri.OFFENDEREDVRI.CreatedBy
offenderedvri.OFFENDEREDVRI.ModifiedDate
offenderedvri.OFFENDEREDVRI.ModifiedBy
FROM StkOffenderVRIInclude(1);

FOR ALL UPDATE offenderedvri.OFFENDEREDVRI.DOCID
offenderedvri.OFFENDEREDVRI.VRIRank
offenderedvri.OFFENDEREDVRI.luVRIID
offenderedvri.OFFENDEREDVRI.CreatedDate
offenderedvri.OFFENDEREDVRI.CreatedBy
offenderedvri.OFFENDEREDVRI.ModifiedDate
offenderedvri.OFFENDEREDVRI.ModifiedBy
FROM StkOffenderVRIUpdate(1);

All 3 consistently fail the first time I change the information on the form and click 'update'. After the update, I refresh the form with the information from the database whether the changes were successful or not and I verify the actual data saved in the db. The data is not changed. Most of the time, if I make the same changes again, the modifications are saved to the db. For the include and update, I have also tried just using 1 REVISE statement, with the same issues.

As I stated, the SQL error returned from the db is 0, but the focerror is 1 or -22.


Thanks for any assistance,
Deana

This message has been edited. Last edited by: Deana,

Deana
When using an Auto-increment field, that field is read only. You should not list that field on the Include / Update / Delete lines. Always list the field below it.

Include SHOULD work like it always does. You have to make sure that before you delete / update the record that you have the correct one. Remember, the auto-increment field is actually the key now. I usually do a retrieval against the database to see if the record exists. If it does I grab that key field and THEN update. If no record found then I include.

Remove the readonly field from the database statement and see if that helps. If not please open a case and I will get you a more detailed example.

Mark

Mark,

The identity field is OFFENDEREDVRIID and is not in the Include, Update or Delete statements.

Per your post I opened case 161128046.

Thank you,
Deana

I did some data comparisons between my development and production databases. To populate my table with the identity column correctly, I migrated records from another table in the same database. In development the identity field value for new records started at 14225. In production, new records were trying to be added with 0 for the identity field value. The first update worked with 0 for the identity field. The database then set the current internal identity value as 0. All subsequent insertions failed because the next identity field to be assigned would be 1 and a record with 1 as the identity value already existed.

In development, DBCC CHECKIDENT('tablename', NORESEED) = 14224 and in production DBCC CHECKIDENT('tablename', NORESEED) = 0, after the first new record was inserted. I had an older copy of the database after the data was migrated and before any new records were entered. DBCC CHECKIDENT('tablename', NORESEED) = NULL on the copy of the database.

As the customer was not able to enter new records, i reran my migration script in production. This time DBCC CHECKIDENT('tablename', NORESEED) = 14224.

From what I can tell, the first time records are migrated, the seed value of the identity field is not set. The reason I did not run into this error in development is because I had run the migration script multiple times while testing it to ensure the data migrated correctly. If I had only run the script once after creating the table in development, I would have run into the same issue.

Lesson learned: After migrating data to a SQL table with an identity field, execute this commend to ensure it is correct. If it is null or less than the maximum identity field value, reset the value with DBCC CHECKIDENT('tablename', RESEED, MaxIdentityFieldValue), in my case 14224. The identity field value for the next record to be inserted will be 14225.

Deana