I use a microsoft access database within my asp script with the OLEDB Driver for Jet 4.0. Whenever I insert a new record and the insert fails (e.g. because the data is to big to fit into the field), an error is displayed and the script terminates. That is pretty normal. But then, if I try to insert more records after this error has occured, an error message is displayed that the record is locked by user "Admin" on machine "...". The problem persists until I restart IIS. I think this is because after the first error my script terminates unexpectedly before the database connection is closed properly and so the locked records are never unlocked again. Does anyone know of any possibility to work around this without using error trapping (i.e. "on error resume next") ?
Originally posted by fsai
(e.g. because the data is to big to fit into the field), an error is displayed and the script terminates.
The real cause of the problem was that the first error occured after a transaction was begun with Conn.BeginTrans and the error caused the script to end before CommitTrans or RollbackTrans could be called.
It is interesting that afterwards no records could be inserted, not even into a table that was not affected during the transaction. Seems that all tables are locked during a transaction. Seems also that open transactions are never rolled back automatically. So if a transaction was opened and for some reason it is not closed, you are not able to add any records to any table in the access database until you reset IIS.
Of course, the best solution is to trap the error and to do a Rollback.
Do ASP scripts always run to the end or are they terminated as soon as the client navigates to another page or closes his browser window ? If they are prematurely terminated, the script will not be able to do a rollback any more and the same situation will arise.
Client scripts to check user input are user friendly, but you should not rely on them, because they may have been disabled in the client browser. In general, you should not trust any data you get from the client without having checked it on the server ;-)
Thank you for your help, I appreciate it very much.