Need example of update query and rollback

[thelordoftherings]

Hello,

Let's say I've opened this connection to the Database:

openstr ="myTest"
Set connection = Server.CreateObject("ADODB.connection")
connection.Open openstr, "", ""
Set rs = Server.CreateObject("ADODB.Recordset")

Can someone please show me a simple code example of update query and rollback handling in case the update fails?

 

[jemminger]

i don't use recordset objects for updates...less overhead...

openstr ="myTest"
Set connection = Server.CreateObject("ADODB.connection")
connection.Open openstr, "", ""
sql = "UPDATE foo SET bar = 'bar' WHERE id = 1"

rem turn on error trapping
on error resume next

rem begin a transaction
connection.beginTrans

rem attempt update
connection.execute(sql)

rem see if it worked
if (err.number <> 0) then
  rem failed, rollback
  connection.rollbackTrans
  err.clear
else 
  rem succeeded, commit
  connection.commitTrans
end if

rem turn off error trapping
on error goto 0

=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[thelordoftherings]

Does this rollback also works for delete queries?

 

[jemminger]

as far as i know...it should just implement the underlying database's transaction facilities


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[thelordoftherings]

Does the connection.rollbackTrans returns the updated row to it's original state?

 

[jemminger]

that's what transactions do...how about writing a test & try it ;-)


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[thelordoftherings]

That's what I figured but how does it actually do it? Does it save the old record before changing it? Does this rollback affective in Access DB as well?

 

[jemminger]

it's up to the database you're using. using Connection.beginTrans is only an interface to your database's transaction method. if your database does not support transactions, Connection.beginTrans will fail and/or throw an error.


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[thelordoftherings]

Do u know if an Access DB supports transactions?

 

[jemminger]

i don't know...try the access forum or google


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }