We are starting to see inconsistency with trying to insert a record into a sql server database that returns a key. sometimes the row is insert and a key is returned and other times the row is inserted and the following error is received leaving the database in a inconsistent state because it does not finish running the rest of the script.
yesterday this was happening on a different screen and after checking the box for no count in the default settings, restarting all the connections then changing the no count check box back to unchecked after finding out that caused all the inserts to fail the problem went away. Now today on a different window that uses the fpmi.db.runPrepStmtGetKey or we tried changing to system.db.runPrepUpdate(getKey=true) we get the same message that a result set was generated for update.
We have modified the code to use a transaction so that when the insert is performed and the error message is received the new row that is inserted is rolled back. we have found that if we click the button to insert the new record and it fails we can just keep pressing the button until the record is inserted and a key is returned successfully.
why does the statement sometimes fail to return a key and other times it returns a key just fine?
Thank you,