Write to Data Store Entity Error

Question

Im getting the error when writing to data store: ... " with identifier value `1007` does not exist " . This is the value of my primary key I'm trying to insert into the data store. I already have the primary key from user input and I'm trying to write all of the info into an empty data store. (I would share screenshot but it's work information). This is occurring within my process model when I am using the write to data store smart service. Why is this happening?

Mike Schmitt · Answer

Please try to share a screenshot of your node configuration anyway. Plus a bit more of the whole error message you're getting. Leave out any work-related data, and use screenshot redacting tools to blur/hide/obscure any sensitive information, object names, branding, etc, that you don't want to put here publicly. The windows standard screenshotting tool has at least some of this functionality, and alternatively I use a tool called "greenshot" (freeware) that has built-in obfuscation capabilities... 
 Just some guesswork though based on the info you've provided - is your DB table set to "auto increment"? I haven't tried in a bit, but when a table is expecting to generate the next PK ID, and you try to provide one within the WTDS node, it might sometimes cause problems. Is the PK ID (or any other table column) used as a Foreign Key ID in some other table?

vimalkumars · Answer

Hi adamn696785 , 
 If you are you trying to insert a record with PK to the Table with auto increment in SQL Server DB, you may need to check on the IDENTITY INSERT Constraint for that table. As Mike mentioned can you please share the DB Server you are inserting into, and you can find additional details of the error in log files.

Shubham Aware · Answer

You&rsquo;re getting the error because system treats a write with a primary key value as an update, not an insert. Since no record with that key exists, it fails. To fix this, when inserting new records, leave the primary key field blank so Appian and the database can auto-generate it. Only provide the primary key when updating an existing record. Additionally, ensure your database table and data store entity are configured to auto-generate primary keys. Verify in your process model that the primary key field isn&rsquo;t populated during insert operations to avoid this error.

Shubham Aware · Answer

Your error happens because your database table's primary key is not set to auto-increment. When inserting, if you leave the primary key blank or null but the DB does Not auto-generate it, the insert fails - Appian shows an error with an empty identifier.

Shubham Aware · Answer

Appian cannot set up auto-increment for a primary key; this must be configured directly in your SQL Server database.

Harsha Sharma · Answer

My two cents here 
 The identifier should not be left with users to input. It should be primary, and auto generating values on inserting a new row. If user is entering this value make this column/field NOT the identifier but just another column . Create another column which does not depend on user as the primary key with auto increment enabled. 
 Since you already have a cdt and thus table in database i suggest you to alter the table & cdt structure following data structure design best practices. The current design you have does not follow standard design principles.