Connection Object

The connection object of ActiveX Data Object component can also be used to manipulate the transaction of a connection session with a data source.

ConnectionObject.BeginTrans Method

ConnectionObect.BeginTrans method for connection object is a method used to begin a new transaction.

Syntax:

ConnectionObectName.BeginTrans;

 Or

nestlevel = ConnectionObectName.BeginTrans();

 Or in VBScript. Imply

ConnectionObectName.BeginTrans

 Or

nestlevel = ConnectionObectName.BeginTrans()

 Or in JScript. Imply

ConnectionObectName.BeginTrans;

 Or

nestlevel = ConnectionObectName.BeginTrans();

Parameters:

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

nestlevel

The parameter "nestlevel" is used to assign the name of the nesting level of the transaction returned from the Connection Object related to.

Return:

Long value

When the ConnectionObect.BeginTrans method is used as a function call, the method returns a Long value indicating the nesting level of the transaction of the specified Connection Object.

Remarks:

The ConnectionObect.BeginTrans method is a method used to indicate the beginning of a series of operatons with the specified Connection object as one transaction, that is to save or cancel a series of changes made to the source data as a single unit. For example, to transfer money between accounts or to perform a sale transaction,  a series of changes should be made to the source data in order to complete the transaction. If one of these changes fails, every changes made since the start of the transaction should be ignored. Therefore when making these changes within an open transaction can ensure that either all or none of the changes go through.

If the provider does not support transactions, calling ConnectionObect.BeginTrans method will return an error. Since not all providers support transactions, the transaction property of the OLE DB provider can be determined by the value of the provider-defined property "Transaction DDL" in the Connection object's Properties collection whether the provider supports transactions.

Transaction DDL value	Description
0	Transactions are not supported
1	Transactions can only contain DML statements. DDL statements within a transaction cause an error
2	Transactions can only contain DML statements. DDL statements within a transaction cause the transaction to be committed
4	Transactions can only contain DML statements. DDL statements within a transaction are ignored
8	Transactions can contain DDL and DML statements in any order
16	Transactions can contain both DML and DDL statements that modify tables or indexes, but modifying a table or index within a transaction causes the table or index to be locked until the transaction completes. This means you won't be able to execute additional statements that affect the tables or indexes once they are locked

After the calling of the BeginTrans method, the provider will no longer commit changes instantaneously, all changes will either be committed by calling the CommitTrans method to end the transaction or be discarded by calling  you make until you call  or be RollbackTrans to end the transaction.

For providers that support nested transactions, calling the BeginTrans method within an open transaction starts a new, nested transaction. The return value indicates the level of nesting: a return value of "1" indicates a top-level transaction have been opened and the transaction is not nested within another transaction, "2" indicates that a second-level transaction  have been opened and the transaction nested within a top-level transaction, and so forth. Calling CommitTrans or RollbackTrans method affects only the most recently opened transaction. The current transaction must be closed or rolled back before any higher-level transaction  can be resolved.

 The BeginTrans method are not available on a client-side Connection object for Remote Data Service .

ConnectionObject.CommitTrans Method

ConnectionObect.CommitTrans method for connection object is a method used to save any changes and to end the current open transaction. Depending on the Attributes property, the provider may automatically starts a new transaction after a CommitTrans call.

Syntax:

ConnectionObectName.CommitTrans;

 Or in VBScript. Imply

ConnectionObectName.CommitTrans

 Or in JScript. Imply

ConnectionObectName.CommitTrans;

Parameters:

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

Remarks:

The ConnectionObect.CommitTrans method is a method used to save any changes and to end the current open transaction. For providers that support nested transactions,  the Calling CommitTrans or RollbackTrans only affects only the most recently opened transaction if one or more new, nested transaction are started within an open transaction. The current transaction must be closed or rolled back before any higher-level transaction  can be resolved.

Calling the CommitTrans method saves changes made within an open transaction on the connection and ends the transaction. Calling the CommitTrans method method when there is no open transaction generates an error.

Depending on the Connection object's Attributes property, calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction. If the Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call. If the Attributes property is set to adXactAbortRetaining, the provider automatically starts a new transaction after a RollbackTrans call.

Since the BeginTrans method are not available on a client-side Connection object for Remote Data Service, the CommitTrans method are not available on a client-side Connection object for Remote Data Service also.

ConnectionObject.RollbackTrans Method

ConnectionObect.RollbackTrans method for connection object is a method used to cancel any changes made during the current transaction and to end the current open transaction. Depending on the Attributes property, the provider may automatically starts a new transaction after a RollbackTrans call.

Syntax:

ConnectionObectName.RollbackTrans;

 Or in VBScript. Imply

ConnectionObectName.RollbackTrans

 Or in JScript. Imply

ConnectionObectName.RollbackTrans;

Parameters:

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

Remarks:

The ConnectionObect.RollbackTrans method is a method used to cancel any changes made during the current transaction and to end the current open transaction. For providers that support nested transactions,  the Calling CommitTrans or RollbackTrans only affects only the most recently opened transaction if one or more new, nested transaction are started within an open transaction. The current transaction must be closed or rolled back before any higher-level transaction  can be resolved.

Examples:

• Example of using the BeginTrans method and CommitTrans method to update a specified data source.

  ASP VBScript command:

  <script runat="server" language="VBScript">
  Dim connObjName, connStr, RecordSetObjectName
  Set connObjName = CreateObject("ADODB.Connection")
  connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
  connObjName.Open connStr
  Set RecordSetObjectName=Server.CreateObject("ADODB.Recordset")
  RecordSetObjectName.Open "Select a from Table1",connObjName,3,3
  Response.Write "initial database value:" & "<br />"
  RecordSetObjectName.MoveFirst
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.MoveNext
  Response.Write RecordSetObjectName(0) & "<br />"
  Response.Write "level " & connObjName.BeginTrans & " altered recordset value: " & "<br />"
  RecordSetObjectName.MoveFirst
  RecordSetObjectName(0).value="abc"
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.MoveNext
  Response.Write RecordSetObjectName(0) & "<br />"
  connObjName.CommitTrans
  Response.Write "database value after CommitTrans:" & "<br />"
  RecordSetObjectName.MoveFirst
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.MoveNext
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.Close
  connObjName.Close
  </script>

  HTML web page output

  initial database value:
  test
  xyz
  level 1 altered recordset value:
  abc
  xyz
  database value after CommitTrans:
  abc
  xyz

• Example of using the BeginTrans method and CommitTrans method to update a specified data source.

  ASP JScript command:

  <script runat="server" language="JScript">
  var connObjName, connStr, RecordSetObjectName;
  connObjName = Server.CreateObject("ADODB.Connection")
  connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
  connObjName.Open (connStr);
  RecordSetObjectName=Server.CreateObject("ADODB.Recordset");
  RecordSetObjectName.Open ("Select a from Table1",connObjName,3,3);
  Response.Write ("initial database value:" + "<br />");
  RecordSetObjectName.MoveFirst;
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.MoveNext;
  Response.Write (RecordSetObjectName(0) + "<br />");
  Response.Write ("level " + connObjName.BeginTrans + " altered recordset value: " + "<br />");
  RecordSetObjectName.MoveFirst;
  RecordSetObjectName(0).value="abc";
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.MoveNext;
  Response.Write (RecordSetObjectName(0) + "<br />");
  connObjName.CommitTrans;
  Response.Write ("database value after CommitTrans:" + "<br />");
  RecordSetObjectName.MoveFirst;
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.MoveNext;
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.Close;
  connObjName.Close;
  </script>

  HTML web page output

  initial database value:
  test
  xyz
  level 1 altered recordset value:
  abc
  xyz
  database value after CommitTrans:
  abc
  xyz

• Example of using the BeginTrans method and RollbackTrans method to cancel the update of a specified data source.

  ASP VBScript command::

  <script runat="server" language="VBScript">
  Dim connObjName, connStr, RecordSetObjectName
  Set connObjName = CreateObject("ADODB.Connection")
  connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
  connObjName.Open connStr
  Set RecordSetObjectName=Server.CreateObject("ADODB.Recordset")
  RecordSetObjectName.Open "Select a from Table1",connObjName,3,3
  Response.Write "initial database value:" & "<br />"
  RecordSetObjectName.MoveFirst
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.MoveNext
  Response.Write RecordSetObjectName(0) & "<br />"
  Response.Write "level " & connObjName.BeginTrans & " altered recordset value: " & "<br />"
  RecordSetObjectName.MoveFirst
  RecordSetObjectName(0).value="abc"
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.MoveNext
  Response.Write RecordSetObjectName(0) & "<br />"
  connObjName.RollbackTrans
  Response.Write "database value after CommitTrans:" & "<br />"
  RecordSetObjectName.MoveFirst
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.MoveNext
  Response.Write RecordSetObjectName(0) & "<br />"
  RecordSetObjectName.Close
  connObjName.Close
  </script>

  HTML web page output

  initial database value:
  test
  xyz
  level 1 altered recordset value:
  abc
  xyz
  database value after CommitTrans:
  test
  xyzz

• Example of using the BeginTrans method and RollbackTrans method to update a specified data source.

  ASP JScript command::

  <script runat="server" language="JScript">
  var connObjName, connStr, RecordSetObjectName;
  connObjName = Server.CreateObject("ADODB.Connection")
  connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
  connObjName.Open (connStr);
  RecordSetObjectName=Server.CreateObject("ADODB.Recordset");
  RecordSetObjectName.Open ("Select a from Table1",connObjName,3,3);
  Response.Write ("initial database value:" + "<br />");
  RecordSetObjectName.MoveFirst;
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.MoveNext;
  Response.Write (RecordSetObjectName(0) + "<br />");
  Response.Write ("level " + connObjName.BeginTrans + " altered recordset value: " + "<br />");
  RecordSetObjectName.MoveFirst;
  RecordSetObjectName(0).value="abc";
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.MoveNext;
  Response.Write (RecordSetObjectName(0) + "<br />");
  connObjName.RollbackTrans;
  Response.Write ("database value after CommitTrans:" + "<br />");
  RecordSetObjectName.MoveFirst;
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.MoveNext;
  Response.Write (RecordSetObjectName(0) + "<br />");
  RecordSetObjectName.Close;
  connObjName.Close;
  </script>

  HTML web page output

  initial database value:
  test
  xyz
  level 1 altered recordset value:
  abc
  xyz
  database value after CommitTrans:
  test
  xyzz