Hi, I have a process model where we are doing write to data store twice. First is insert and other should be update on same record. We have a primary key ID which is primary key and set to auto increment. First insert has ID as 1 and other insert Id gets incremented to 2 so 2 entries are coming. Is this because whenever we hit the database 'ID' will get incremented as it is set to auto increment? Should I set it to not be auto incremented? Or is there a way to update the same record and ID should have value as 1 only for second entry in db?
Discussion posts and replies are publicly visible
Hi there.
If you are writing and updating in the same process model, you need to be sure to save back the results from the first write to data store node so that the process variable now has the database generated PK. You can do this on the outputs tab of the write to data store node, by setting the stored values output to save over your process variable. Otherwise, the PV will still have null for the PK when you go to the second write to data store node, and will thus try and generate a new one.
Problem: An error occurred in executing an Activity Class.
Details: An error occurred while trying to write to the entity “Workflow_Data” [id=907c5e0b-7cc2-4e43-a5e4-766a0a809c48@35995, type=RPA_WF_Data (id=9822)] (data store: RPA_WF_Data). Details: org.hibernate.exception.SQLGrammarException: could not insert: [RPAWFDataDT16499]: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'rpawfdata' when IDENTITY_INSERT is set to OFF. Data: TypedValue[it=9822,v={2123899,Cust 155,Processing,,<null>,Administrator,2020-10-08,ms53494,,<null>,,,,,Initiated,,,1.0,,,ms53494,HK,APAC,1,<null>}]
If I am assigning some value to ID column and set auto increment to off then this error is coming. What does it mean?
Hi,
You don't need to turn auto increment off, in fact you shouldn't. This error is saying that the db table is not configured to allow you to insert manually into the identity column. You should have auto increment on, let the first write to data store entity node write to the table with a null in it's pk column, then save back the stored values in the output tab to overwrite the process variable you just wrote, then use that pv in the second write to data store note, now with a non null pk, so it will update that row.
it worked. Thanks a ton!
No problem!