First you will need to have DTC setup and working on the web server and the SQL Server. Without DTC working you won’t be able to start a transaction on the web server and have it continue to the SQL Server.
This site covers the basics of handling a transaction.
The ConnectionObject.BeginTrans and ConnectionObject.Endtrans can be used for managing the SQLSERVER transactions in asp code. And if everything is fine, to commit the transaction use ConnectionObject.CommitTrans, otherwise, to rollback the operations use ConnectionObject.RollbackTrans.
To be able to use one transaction for more than one SQL statement, set the RecordSetObject.CursorType to adOpenDynamictype