The key to allow transactions lies in the command “.BeginTrans” that you should declare immediately after making the database connection, as seen in the example below.
option explicit dim dbType, dbHost, dbName, dbUser, dbPass dbHost = "hostname" ' Hostname of the Oracle database server dbName = "database" ' Name of the database/SID dbUser = "username" ' Name of the user dbPass = "password" ' Password of the above-named user dim conn set conn = CreateObject("ADODB.connection") conn.ConnectionTimeout = 30 conn.CommandTimeout = 30 conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False") conn.BeginTrans conn.execute("update order set order_total=(select qty * unit_price from order_detail where order_id=123) where order_id=123") dim checkOrderTotal set checkOrderTotal = conn.execute("select order_total from order where order_id=123") if (checkOrderTotal("order_total") < 0) then conn.RollbackTrans ' TODO : Send an email to accounting department to check it out else conn.CommitTrans end if conn.close set conn = nothing
Notice the if-else clause near the bottom. If the order total somehow ended up a negative number, we rollback the transaction (and presumably would notify someone to check it out at this point), we roll back the transaction. Otherwise, as seen in the "else" clause, we proceed with committing the Oracle transaction.
It is important that you must issue either ".RollbackTrans" or ".CommitTrans" before the connection is terminated, otherwise you might get the VBScript ADODB connection error 800A0CAE, "Connection object cannot be explicitly closed while in transaction."