The above graphic displays all the objects that comprise the ADO hierarchy. There are three major objects that let you directly interface with your data: Connection, Command, and Recordset.

The Connection Object is the main backbone of your data. You can think of the Connection Object as the pathway to your data source. Without the Connection Object you would have no way to connect to your data source.

You do not need to set any of the above properties in order to open a connection to your database; however, they give you greater control of your connection. The brief descriptions relate the importance of each property. The following sections go into more detail about these properties.

Attributes

Because the Attributes Property is provider-dependent, you should check whether your provider supports either feature. Microsoft Access databases do not support transactions; therefore, this property does not apply to them.

The Attributes property can both be read and written to. This property can contain either the value of adXactCommitRetaining (131072) or adXactAbortRetaining (261544).

Note: For the values above, you can use either the name or the numeric value associated with it (as shown in parentheses). If you wish to use the text name of the property, you must make sure that you include the adovbs.inc file.

You can use these properties to control how transactions are re-engaged once they have been committed or aborted. If a connection is retained, it renews itself after an update or abort. adXactCommitRetaining will begin a new transaction automatically after a call to CommitTrans. adXactAbortRetaining will automatically begin a new transaction after the RollBackTransaction is called.

The Attributes property is settable at any time.

CommandTimeout

The CommandTimeout property allows both reading and writing. You can use this property to set the duration, in seconds, that a SQL query is allowed to execute. If the query takes longer then the specified time in seconds, the query is aborted and the execute method fails. This property is useful in slow network connections, backlogged servers, or large database access.

An example of large database problems would be when a Microsoft Access database grows to a file size of 50 Megs. Microsoft Access can support databases as large as 2 Gigabytes; however, file access begins to slow once the database size reaches 50 Megs. If your database grows as large as 50 Megs, or you anticipate it growing that large, it would be best to consider using Microsoft SQL Server 7.0.

ConnectionString

The ConnectionString Property allows you to specify the database you wish to connect to. This property can be both read and written to. It is through this property that we can connect either through ODBC or DSN-less connections to our database. We will cover both connection types later on in the examples of the properties.

The ConnectionString Property is made up of a series of associations, similar to pairs inside of Perl. Each pair within the association has a name and a value assigned to it. Semicolons separate each pair inside the ConnectionString.

The following is a list of the ADO recognized names inside the ConnectionString with their abbreviations in braces:

An example of the ConnectionString can be seen below.

<%
Dim cn
` Open a connection using the Microsoft ODBC provider.
Set cn = Server.CreateObject (“ADODB.Connection”)
cn.ConnectionString="driver={SQL Server};server=bigsmile;uid=sa;pwd="
cn.Open
cn.DefaultDatabase = "pubs"
%>

This example opens a connection to the pubs database on the bigsmile server using the username of sa and a blank password.

ConnectionTimeout

This property sets how long, in seconds, the open method will try to open the database. If the database does not open within the specified time, the operation is considered a failure. This property is both readable and writable.

CursorLocation

This property lets ADO know where the data from the Open command will be kept. The Connection Object does not use the value set in this property, but the Recordset Object inherits its value. Valid settings for this property are shown in the table below (Note that numeric values must be specified unless adovbs.inc is included in your ASP page):

From the table above we can deduce that there are three different locations for the data to be kept: the client, the server, and a client batch.

The first location, the Client, is a special cursor location. The client location will allow the data to be queried from the server and then transmitted back to the client. The client then makes the modifications to the data. Once the modifications are made, the modified set of data, or Recordset, is then transmitted back to the server for updating. The client batch is the next one of interest. The client batch is a method where the data is transmitted in batches back and forth to the server and client. The client may modify 20 records and then send back that Recordset for a batch update. Microsoft Internet Explorer is the only browser that supports both of these methods. For our development purposes, we will be using the server for the data location.

DefaultDatabase

The DefaultDatabase property can be both read and written to. This property tells the connection which database to connect to. When ODBC connections are made, we specify the default database to the one that was created for you, making this property unnecessary. When using a DSN-less connection, we can either use this property or specify the database in the Open method call.

IsolationLevel

The IsolationLevel property allows us to set the level of isolation before the transaction begins. The level of isolation determines if a change to the database will occur before or at the end of the transaction. It also determines what degree of visibility you currently have to data changed by other transactions. A fully isolated transaction has no overlap with another transaction occurring at the same time. The following phenomena are commonly used to characterize isolation levels:

Dirty Read: Transaction A changes a row. Transaction B reads the changed row before transaction A commits the change. If transaction A aborts the change, transaction B will have read a row that is considered to have never existed.

Non-repeatable read: Transaction A reads a row. Transaction B updates or deletes that row and commits this change. If transaction A attempts to reread the row, it will receive different row values or discover that the row has been deleted.

Phantom: Transaction A reads a set of rows that satisfy some search criteria. Transaction B inserts a row that matches the search criteria. If transaction A re-executes the statement that read the rows, it receives a different set of rows.

According to these phenomena, the isolation levels defined by OLE DB are as follows:

Chaos: All transactions can overwrite each other’s changes before the changes are committed. With this setting, it is like not having any transactions at all.

Read Uncommitted (Browse): A transaction operating at the Read Uncommitted level can see uncommitted changes made by other transactions. At this level of isolation, dirty reads, non-repeatable reads, and phantoms are all possible.

Read Committed (Cursor Stability): A transaction operating at the Read Committed level cannot see changes made by other transactions until those transactions are committed. At this level of isolation, dirty reads are not possible, but non-repeatable reads and phantoms are possible.

Repeatable Read: A transaction operating at the Repeatable Read level is guaranteed not to see any changes made by other transactions in values it has already read. At this level of isolation, dirty reads and non-repeatable reads are not possible, but phantoms are possible.

Serializable (Isolated): A transaction operating at the Serializable level guarantees that all concurrent transactions will interact only in ways that produce the same effect as if each transaction were entirely executed one after the other. At this isolation level, dirty reads, non-repeatable reads, and phantoms are not possible.

The table below shows the name of the value, the numeric value, and the related phenomena from above.

As you can see, some of the settings are the same, but just have different names. In such cases, you can use either setting.

Mode Property

The Mode property lets the connection know exactly how to lock the data that it is trying to access. This setting can be used to constrict concurrent access to the same data source, guaranteeing the accuracy of the data.

Provider

The Provider Property allows you to identify the datasource provider. This property can be both read and written to. The default value of the provider property is MSDASQL.1, which controls access to all current ODBC datasources.

Version

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 we have seen in the Connection’s methods we can set the mode and isolation properties to define how our transaction should work. An example of where a transaction would be helpful is when we are using a database provider that does not allow us to have an auto incrementing number as a data type. We would need to find out the maximum number that a column would contain, increment that number by one, and insert it with our current record. This would give us the effect of having an auto incrementing number. If we did not protect this with a transaction, another concurrent user could insert the same value number, which would violate our primary key when we went to insert this record, which would return an error to our client. This would not be an acceptable result to our clients, because of the high volume that our site receives. Due to this problem, we need to contain our above steps in a transaction. Please note that not all providers provide transactions. Microsoft Access does not provide us with transactions, but Microsoft SQL Server 7.0 does provide us with transactions.

CommitTrans

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

Close

We can use the Close method to break the Connection Object’s link. This can be helpful to close our connection to the current database and open the Connection object to another database. 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, we will not be able to update their data back to the datasource until we reopen our datasource by calling the open method.

Execute

Execute is the method that we call to execute a query against the datasource. The execute method returns a Recordset. If the query that we submit does not return any data, then the execute method returns an empty Recordset. We can either assign the returned Recordset to a variable or we can choose to just ignore the returned value by not assigning it to anything. Now we can look at the following example for some further clarification:

Set rs = Connection. Execute Query, Count, Options

The above example creates an object called "rs" that will be assigned a Recordset that contains the data returned from executing the Query. When we call the Execute method, we must pass the Query parameter, which contains a standard SQL query as discussed in Chapter 19, Database Basics.

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

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, we 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.

Remember that 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, we can use the optional ConnectionString argument to specify our connection string. We can override this property by specifying our connection string as a parameter to the open call. This is the most widely used method for opening a connection.

The first line that becomes important to us is line 7. Line 7 specifies the exact database that we will be using. This file assumes that it will be placed in our www/htdocs directory, and that our database is named vservers_demo.mdb and it is placed in our www/databases directory. In Line 10 we are actually creating our database connection object. Now we are able to access all of the Connection’s properties and methods through this new object, which we assign to the variable dn. In Line 13, we try to open the connection to our database. However, we do not do anything with this connection to our datasource. We will now look at an example that will use this data to perform some actions.

Accessing Data with the Connection Object

In the above section, Opening a Connection Explained, we opened a database connection. We will now expand on that example and the vservers_demo.mdb database we created in the SQL Boot Camp section.

The following code will allow us to show the contents of our employee_data table. This example loops through each record contained in the employee_data table and displays its results in a table. The examples below assume that they are placed in the same directory. The following code was used to perform this action:

Example 7 Access Test ASP

1. <!-- Begin test_Access_Master.asp -->
   
2. <!-- #include virtual="/examples/asp/accessmaster.asp"-->
   
3. <HTML>
   
4. <HEAD>
   
5. <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
   
6. </HEAD>
   
7. <BODY>
   
8. <P>The following code will allow us to show the contents of our employee_data table.&nbsp; This example loops through each record contained in the employee_data table and displays its results in a table.&nbsp; The following code was used to perform this action:</P> <P>The code produces the following results on our sample database <TABLE border=3 cellPadding=5 cellSpacing=5 width=80%> <tr> <TD>Employee<br>Number</TD> <TD>First Name</TD> <TD>Last Name</TD> <TD>Title</TD> </tr> &lt;% OpenDb Set rs=cn.Execute ("Select * from employee_data") while not rs.eof Response.Write "<tr><td>"& rs("Employee_Number") &_ "</td><td>" & rs("First_Name") &_ "</td><td>" & rs("Last_Name") & _ "</td><td>" & rs("title")&"</td></tr>" rs.movenext wend %&gt; </TABLE> <P>The code produces the following results on our sample database <TABLE border=3 cellPadding=5 cellSpacing=5 width="80%"> <tr> <TD>Employee<br>Number</TD> <TD>First Name</TD> <TD>Last Name</TD> <TD>Title</TD> </tr>
   
9. <%
   
10. OpenDb
    
11. Set rs=cn.Execute ("Select * from employee_data")
    
12. while not rs.eof
    
13. Response.Write "<tr><td>"& _
    rs("Employee_Number") & "</td><td>" &_
    rs("First_Name") & "</td><td>" & _
    rs("Last_Name") & "</td><td>" & _ rs("title") & "</td></tr>"
14. rs.movenext
    
15. wend
    
16. %>
    
17. </TABLE></P>
    
18. </BODY>
    
19. </HTML>
    
20. <!-- end test_Access_Master.asp -->

The results of the above output can be viewed at the following URL:
http://www.4domains.com/ntguide/test_access_master.asp

Line 2 uses a Server Side Include (SSI) to pull in the source of accessmaster.asp. This file will contain the information that we will need to connect to our database. In our example that file is located in the /examples/asp web directory.

Line 10 calls the subroutine that will open the database connection, and assigns a Connection Object to the cn variable.

Line 11 instructs our database connection to execute the query that will return all of the data from the employee_data table. Calling the Open method in the Connection Object returns a Recordset Object. The Recordset object is then assigned to the rs variable.

Line 12 begins a while loop. The Boolean condition of this loop will depend on whether the EOF property is true in our rs object. At the end of the Recordset, this property will be true and the while loop will end.

Line 13 writes out rows in our HTML table with each pass through the while loop. The value contained in the Employee_Number field of the current Recordset will be written to the first cell. This means that the first time through the while loop, the value would be 1. As shown in Table 20-8, the first row contains an Employee_Number of 1. The rest of the values in the Recordset are then written to each table cell.

Line 14 calls the Recordset’s MoveNext method. The MoveNext method is very important in a while loop. If we were to leave this line off, the server would go through an endless loop of the Recordset until the server timed the script out.

Line 15 ends the while loop. If there were no end for the while loop, the server would return an error.

ASP Code Source 20-1 Access Master Example

1. <%`Begin accessmaster.asp
   
2. `Find the current path of the web server
   
3. ServerPath =Server.MapPath (".")
   
4. `Declare a variable to hold our database file location
   
5. Dim CONST_DB_FILE
   
6. `Set the variable to the proper location
   
7. `Remember to specify the exact database
   
8. CONST_DB_FILE = ServerPath & "\..\ODBC\vservers_demo.mdb"
   
9. `Declare our main connection object
   
10. Dim cn
    
11. `The following Subroutine will setup cn as an object if it not already one
    
12. `The subroutine then opens the connection to our datasource
    
13. Sub OpenDb
    
14. if Not(IsObject(cn)) then ` if we have never opened the db before then open it
    
15. `Create an ADO database connection object
    
16. Set cn=Server.CreateObject ("ADODB.Connection")
    
17. `Open the database connection object
    
18. cn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" &_ CONST_DB_FILE & ";uid=;pwd=;"
    
19. Else `do nothing
    
20. End if
    
21. End sub
    
22. %>

The accessmaster.asp file is very similar to the Access Test ASP of Example 7, except for the presence of IF conditions and the fact that the Database Open call is now encased inside a function. The IF conditions help the server avoid the slow site performance associated with reopening a database connection that is already open.

The above example allows us to view the contents of our employee_data table. This example loops through each record contained in the employee_data table and displays its results in a table.

The output of the code above looks like the following:

In the above example we connected to a Microsoft Access database. If we wanted to connect to a SQL Server database, we would simply need to change one line in the test_access_Master.asp file to include a different file. Please see the lines below for the changes in the file:

1. <!-- Begin test_Access_Master.asp -->
   
2. <!-- #include virtual="/examples/asp/sqlmaster.asp"-->
   
3. <HTML>
   
4. <HEAD>

We would also have to create a new file named sqlmaster.asp in our www\htdocs\examples\asp directory. The file would have the following contents:

ASP Code Source 20-2 SQLMaster.asp

1. <%`Begin sqlmaster.asp
   
2. `Set up a constant that will hold the driver that we are going to use
   
3. Const CONST_DB_DRIVER = "SQL Server"
   
4. `Declare our main connection object
   
5. Dim cn
   
6. `The following Subroutine will setup cn as an object if it not already one
   
7. `The subroutine then opens the connection to our datasource
   
8. Sub OpenDb
   
9. if Not(IsObject(cn)) then ` if we have never opened the db before then open it
   
10. `Create an ADO database connection object
    
11. Set cn=Server.CreateObject("ADODB.Connection")
    
12. `Open the database connection object
    
13. cn.Open "driver=" & CONST_DB_DRIVER & _
    
14. ";server=;uid=vserverdemo;pwd=public;database=vserver_demo;"
    
15. Else `do nothing
    
16. End if
    
17. End sub
    
18. %>

The above code produces the same output as seen in Table 20-8. As you can see, both cn.Open calls use a similar connection string and both examples produce exactly the same output, independent of the database provider. Furthermore, the displaying of data was handled the same for these different database providers.

We have only added a few lines to the original example. In Line 15, we create a Recordset Object and assign it to the variable rs. The datasource connection is then opened as normal. In the next step, we open our datasource with a SQL statement and a connection object. At this point, our Recordset contains all of the data from the employee_data table.

The next example outputs the data in the same manner as before, but uses some of the Recordset’s properties to display more information about our Recordset.

1. <%`Begin recordset_access_master.asp
   
2. `Find the current path of the web server
   
3. ServerPath =Server.MapPath (".")
   
4. `Declare a variable to hold our database file location
   
5. Dim CONST_DB_FILE
   
6. `Set the variable to the proper location
   
7. `Remember to specify the exact database
   
8. CONST_DB_FILE = ServerPath & "\..\..\..\databases\vservers_demo.mdb"
   
9. `Declare our main connection object
   
10. Dim cn
    
11. `Declare our main Recordset Object
    
12. Dim rs
    
13. `The following Subroutine will setup cn as an object if it not already one
    
14. `The subroutine then opens the connection to our datasource
    
15. Sub OpenDb
    
16. if Not(IsObject(cn)) then ` if we have never opened the db before then open it
    
17. `Create an ADO database connection object
    
18. Set cn=Server.CreateObject ("ADODB.Connection")
    
19. `Open the database connection object
    
20. cn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" &_
    
21. CONST_DB_FILE & ";uid=;pwd=;"
    
22. Else `do nothing
    
23. End if
    
24. End sub
    
25. Sub OpenRs(SQLString)
    
26. Opendb
    
27. if Not(IsObject(rs)) then ` if we have never assigned rs an object
    
28. Set rs=Server.CreateObject("ADODB.RecordSet")
    
29. end if
    
30. rs.Open SQLString,cn,1,1
    
31. end sub
    
32. %>

We have modified our original access master file to now include some additional objects and functions. The OpenRs function allows us to open a Recordset by calling a function with a SQL String. In Line 26 we verify that our database is open before we try to access any data with our Recordset Object. In Line 27, we check whether the Recordset Object exists. If the object does not exist, meaning that it has never been assigned to an ADO Recordset Object, then we want to assign it to an ADO Recordset object.

In Line 30, we open our datasource connection. We open it with the SQL String that was passed into the function. The second parameter that we pass to the Recordset object is the actual datasource connection that we have already opened in Line 26. The third parameter allows us to open this Recordset with the cursor type as adKeySet. The fourth parameter sets the lock type to adLockReadOnly.

The combination of the third and fourth parameters allows us to take advantage of the Recordset’s additional features. Let’s look at the test example below:

1. <!-- Begin test_recordset_access_master.asp -->
   
2. <!--#INCLUDE VIRTUAL="/examples/asp/recordset_access_master.asp"-->
   
3. <html>
   
4. <head><title></title>
   
5. <body bgcolor=#ffffff>
   
6. <P>The code produces the following results on our sample database
   
7. <TABLE border=3 cellPadding=5 cellSpacing=5 width="80%" style="LEFT: 10px; TOP: 716px">
   
8. <tr>
   
9. <TD>Employee Number</TD>
   
10. <TD>First Name</TD>
    
11. <TD>Last Name</TD>
    
12. <TD>Title</TD>
    
13. </tr>
    
14. <%
    
15. Openrs "Select * from employee_data"
    
16. while not rs.EOF
    
17. Response.Write "<tr><td>"& rs("Employee_Number") & _
    
18. "</td><td>" & rs("First_Name") &_
    
19. "</td><td>" & rs("Last_Name") & _
    
20. "</td><td>" & rs("title") & "</td></tr>"
    
21. rs.movenext
    
22. wend
    
23. %>
    
24. </TABLE>
    
25. </P>
    
26. <P>This Recordset`s Record Count is: <%=rs.RecordCount%> Records.<BR>
    
27. This Recordset`s Page Count is: <%=rs.PageCount%> Pages.<BR>
    
28. The Recordset`s Page Size is: <%= rs.PageSize%> Records.
    
29. </P></BODY></HTML>

We see that not much of our code from Example 7 Access Test ASP has changed. We now just need to make a single function call to get our data into our Recordset, as seen in Line 15. We can then access the new Recordset in the same method that we previously did. We can now access some additional information about our Recordset. Some of this additional information is seen in Lines 26-29. We are able to show: how many records were returned; how many pages of records are returned; and how many records are in our providers pages.