Connection Object Methods

Previous sections of this guide have explored the properties of the Connection Object. It is through these properties that the Connection methods are affected. The Connection Object contains the following methods:

Connection Object Methods

BeginTrans: When you call this method inside the Connection Object, a new transaction begins at the top or nested level. This method returns an integer indicating the nesting level of a transaction that has just started. Transactions are protected access to the data. As illustrated in the Connection methods, you can set the mode and isolation properties to define how the transaction will work. An example of a helpful transaction would be using a database provider that does not allow you to have an auto incrementing number as a data type. You will need to determine the maximum number a column can hold, increment that number by one, and insert it with the current record. This would produce an auto incrementing number.

If you do not protect this with a transaction, another concurrent user could insert the same value number, which would violate the primary key when you insert the record. This would, in turn, return an error to the client. Due to this problem, you should keep the above steps in a transaction, but note that not all service providers offer transactions. Microsoft Access does not provide transactions, but Microsoft SQL Server 7.0 does.

Close: The Close method can be used to break the Connection Object’s link. This can be helpful to close a connection to the current database and open the Connection object to another. If you forget to call the close method at the end of your scripts, ASP automatically closes any database connections that you may have open on the script exit of the page. Any open Recordset associated with the recently closed connection will become disconnected. If there is any data inside of those record sets, you will not be able to update the data back to the datasource until you reopen the datasource by calling the open method.

CommitTrans: When the CommitTrans method is called, the current transaction is committed to the database. Any changes inside the transaction are written to the database. With nested transactions, only the outermost transaction is committed.

Execute: Execute is the method that you will call to execute a query against the datasource. The execute method returns a Recordset. If the query that you submit does not return data, then the execute method returns an empty Recordset. You can either assign the returned Recordset to a variable or ignore the returned value by not assigning it to anything. The following example creates an object called "rs" that will be assigned a Recordset that contains the data returned from executing the Query.

Set rs = Connection.Execute Query, Count, Options

When you call the Execute method, you must pass the Query parameter, which contains a standard SQL query as discussed in previous chapters.

The other parameters, Count and Options, are optional. The Count Parameter contains the number of records affected by that query. This parameter may be helpful if you want to know how many records are in a table, or how many records are needed to loop through in order to display all of the records in the Recordset. The below table displays the optional parameters that you can pass.

Connection Options

RollbackTrans: RollbackTrans cancels any changes made during the current transaction and ends the transaction. Calling RollbackTrans affects only the most recently opened transaction. In order to rollback a higher level transaction, you must rollback or cancel all of the lower level transactions that may be open. Calling this method when there is no open transaction generates an error. When the Connection object's Attributes property is set to adXactAbortRetaining, the RollbackTrans method automatically starts a new transaction.

Note: The RollbackTrans method is not available on a client-side Connection object.

Open: Using the Open method on a Connection Object establishes a physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process results. Therefore, this is the most important method for accessing data from your datasources.

Again, you can use the optional ConnectionString argument to specify the connection string. You can override this property by specifying the connection string as a parameter to the open call. This is the most widely used method for opening a connection.

Once finished with this connection, it is important that you call the Close method to close the connection to your datasource. If you forget to call the Close method, ASP will automatically close the database connection when the server reaches the end of the ASP page.