Hi,

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") ?

Thank you in advance for your help.

ai

You need to handle the error in your own script, by rolling back the transaction.

Web servers commonly keep "persistent" connections to a database to avoid the overhead of constantly opening and closing them.

Don't rely upon a web server to "know what to do" because, to them, "one program's very like another one."

Why not just check the data BEFORE doing the insert, to prevent the error in the first place. You can create a short function to either truncate the data so that you know it will always fit, or just not do the insert and redirect back to the input page with a message that the data was too long. Better yet, do a client-side check in JavaScript.

Tim

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.

ai