SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Adding SQL Server transactional queries to an old ASP app

    I wrote an app several years ago that has only just begun intermittently failing on a particular stage in a series of sql queries. When I wrote it I wasn't familiar with transactions and so didn't bother implimenting them. Since then I've moved from ASP to PHP and have done several applications that used the ADOdb library's transactional features. Now going back to ASP I'm having a hard time finding any information on how to actually impliment a transaction from an ASP page for a set of queries that are not part of any stored procedures.

    For instance, how am I to know when an error occurs and that I need to roll back? Do I need to set OnErrorGoTo = 0, then check for an error at the end of the sequence and roll back if one occurred?

    If anyone could post a small example of ASP code that includes a few queries executed with an adodb connection object, wrapped in a SQL transaction, that would be most beneficial.

    I've searched the Internerd a few times and, surprisingly, have not found a good example of this otherwise...

    Thanks!
    Chris Bloom

    PS: The app is written in classic ASP, not that new fangled .NET stuff.
    Chris Bloom
    Web Application Developer

  2. #2
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very simply:
    Code:
    On Error Resume Next
    
    Set db = Server.CreateObject("ADODB.Connection")
    db.Open myConnectString
    
    bAllOK = True
    db.BeginTrans
    
    db.Execute "INSERT INTO someTable(someField) VALUES('thing')"
    If Err <> 0 Then
    	bAllOK = False
    End If
    
    If bAllOK Then
    	db.Execute "UPDATE someTable SET someField='anotherThing' WHERE Id=1"
    	If Err <> 0 Then
    		bAllOK = False
    	End If
    End If
    
    If bAllOK Then
    	db.CommitTrans
    Else
    	db.RollbackTrans
    End If

  3. #3
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much! That was just what I needed. I just finished testing the new transactional support in my DB class and everything appears to be working great
    Chris Bloom
    Web Application Developer


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •