How To Process Data Transactions from an ASP Page (299637)

SUMMARY

This step-by-step procedure demonstrates how to process data transactions from an Active Server Pages (ASP) page if the transactions use ActiveX Data Objects (ADO) to connect to a database.

back to the top

Prerequisites

This list outlines the recommended hardware, software, network infrastructure, and service packs that you will need:

Microsoft Active Server Pages
Microsoft Internet Information Server

This article assumes that you are familiar with the following topics:

ASP terminology and syntax
Data Access technologies (Microsoft ActiveX Data Objects)

back to the top

Processing Data Transactions from ASP

In Notepad, create a new ASP page named DataTran.asp, and paste the following code:

Note You must change UID=<username> and pwd=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

<%@ TRANSACTION=Required%>
<%
	Option Explicit
        On Error Resume Next
	Dim oConn, oRS
		
	Set oConn = Server.CreateObject("ADODB.Connection")
	oConn.Open "Provider=SQLOleDB;server=servername;Initial Catalog=pubs;uid=<username>;pwd=<strong password>"	
	if err.Number <> 0 Then
	    Response.Write "<BR>Error Occurred Opening Connection...<BR>"
	    Response.Write "<BR>Error Description: " & err.Description & "...<BR>"
            ObjectContext.SetAbort
	    Response.End
	else
	    Response.Write "Connection Opened Successfully...<BR>"
	    ObjectContext.SetComplete
	End If

	
   	oConn.Execute "Select * from Authors"
	if err.Number <> 0 Then
	    Response.Write "<BR>Error Occurred Executing Query...<BR>"
	    Response.Write "<BR>Error Description: " & err.Description & "...<BR>"
	    oConn.Close
	    Set oConn = Nothing
            ObjectContext.SetAbort
            Response.End
	else
	    Response.Write "<BR>Query Completed Successfully...<BR>"
	    ObjectContext.SetComplete
	End If

	oConn.Close
	Response.Write "<BR>Connection Closed Successfully...<BR>"
	set oConn = Nothing
	Response.Write "<BR>Test Completed Successfully...<BR>"

    Sub OnTransactionCommit()
        	Response.Write "<p><b>The Transaction just committed</b>." 
	Response.Write "This message came from the "
        	Response.Write "OnTransactionCommit() event handler."
    End Sub

    Sub OnTransactionAbort()
        	Response.Write "<p><b>The Transaction just aborted</b>." 
        	Response.Write "This message came from the "
        	Response.Write "OnTransactionAbort() event handler."
    End Sub

%>

The above code sample tries to connect to the Microsoft SQL Server default PUBS database. If you do not have SQL Server installed, you must change the connection string and the SQL statement.

Also, make sure that you change the connection string in the following line of code to include the correct server name, User Id (UID) and Password (PWD):
```
	oConn.Open "Provider=SQLOleDB;server=servername;Initial Catalog=pubs;uid=<username>;pwd=<strong password>"
					
```
On the File menu, click Save. In the Save in drop-down list box, click the C:\Inetpub\Wwwroot folder. In the Save as type drop-down list box, click All Files. In the File name list box, type DataTran.asp.
In your Web browser, run the ASP page (for example, http://localhost/DataTran.asp).

If the OLE DB connection string and query are valid, the following output is returned:

Connection Opened Successfully...

Query Completed Successfully...

Connection Closed Successfully...

Test Completed Successfully...

The Transaction just committed.This message came from the 
OnTransactionCommit() event handler.

If the OLE DB connection string is invalid, or if your query is incorrect, you receive an error message, and the OnTransactionAbort event is fired. The following output is returned:

Connection Opened Successfully...

Error Occurred Executing Query...

Error Description: Invalid object name 'Author'....

The Transaction just aborted.This message came from the 
OnTransactionAbort() event handler.