Transaction processing in ASP

[Mayoor]

Hi...

I have 2 executes to the database, one is a delete and the other is an insert. The delete is first and the insert directly after.

I would like to put in some rollback feature which will allow any changes made to the db to be rolled back if something goes wrong.

'delete all user interests from the table
ParamNameArray=Array("RegisteredUserID")
ParamValueArray=Array(RegisteredUserID)
		
set UpdateRS = ExecuteSP (Conn, "thetin_web_deleteRegisteredInterest ", ParamNameArray, ParamValueArray)
		
'add in all the interests from the request object		
		If interest <> "NULL" then 		
			InterestArray = split (interest, ",")			
			
			For i = 0 to ubound(InterestArray)
				'response.write(InterestArray(i))
				'response.end
				
				ParamNameInterestArray=Array("RegisteredUserID", "InterestID")
				ParamValueInterestArray=Array(RegisteredUserID,InterestArray(i))
				Set RegInterestRS = ExecuteSP(Conn, "thetin_web_insertRegisteredInterest ", ParamNameInterestArray, ParamValueInterestArray)		
				
			Next
			
		End if

Can anyone help?

 

[TheCandyman]

You can use the Rollback statement.

http://databases.about.com/cs/administration/g/rollback.htm

First you delete, do a check to make sure it's deleted(i assume this is what your talking about) and if okay then Insert. I havn't have to do a rollback in years (knock on wood)

 

[Chopstik]

If you are using SQL Server, look at "Rollback Transaction" in Books OnLine. That should get you started and if you have additional questions after you have given it a shot, feel free to post back.

As an additional afterthought, you might consider putting your entire transaction into a stored procedure.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Mayoor]

I have to keep it on the ASP side!

 

[Chopstik]

Ok. If you have to keep it on the ASP side, then perhaps the SP is out. But you could simply code the SQL on the ASP side to accomplish the same thing. Both TheCandyman's and my advice in terms of the Rollback Transaction should be able to work for you. Are you having a specific problem with that?

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)