One of the truly great features of ASP is the ability to utilize COM objects within the ASP page. This enables your application to use the power of external compiled code. These external objects supply a lot of functionality, as well as offering speed and transaction capabilities. Once you learn how to invoke and use objects in ASP, you can start looking at how to write your own objects and then how to take those objects and compile them to take advantage of the speed you can only get from compiled code.
The database we will be using is a simple music database that I use to keep track of my music collection (please no music critics). The database should be on the Web server machine but not in the Web directories with your ASP pages. Placing an Access database with your ASP pages can create a security problem and is never a good idea. The IUSR_yourmachinename account will need to have appropriate rights to the directory with the Access database so that it can read and update the database file. As you can see in Figure A, the structure of the Access database is fairly simple.
|Database structure for music database|
The next step is to create a subdirectory in your default Web directory named music for the music database application. In that directory, we’ll create the global.asa file as shown in this listing:
FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" —>
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
Application("musicConnection") = _
& "Data Source=c:\temp\Music Collection.mdb;"
The first line is something new. This metadata entry allows the declaration of a type library entry. By referencing a type library, we can use all its constants. In this case, we have an entry for the Active Data Object library. If you didn’t install to the C: drive you may have to find this file in order for this entry to work. The rest of the code simply uses the Application object covered in my previous article to store the connection string information. This connection string uses the OLEDB provider for the Jet database engine. The connection string specifies not only the type of database but also the location of that database. By changing the connection string, we can easily move the database or even change the database type to something else, like DB2, MS SQL, or Oracle. Here is an example of a SQL connection string:
“Provider=SQLOLEDB;Data Source=’mysqlserver’;” _
& “Initial Catalog=” _
& “mydatabase;User Id=login;Password=password;” _
& “Connect Timeout=3;network library=dbmssocn;”
Now we need to create a page that will use the database to retrieve a list of albums and return that data so that we can output it on the page. The formatting has been kept simple so that we can concentrate on the code for accessing the database. The first part of the musicList.asp file is simple and just declares the page. I’ve added the DOCTYPE declaration and some meta tags to indicate the type of file and control caching. This is how I normally head a page. With ASP, there is often concern over aggressive caching by browsers. If a browser caches a page that gets data from the database, it may not display the most recent data. By specifying the meta tags you can instruct the browser or proxy to not cache the page.
<%@ LANGUAGE="VBScript" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="-1">
<title>Music Title List</title>
Next, we dimension our variables and setup the SQL statement that this page will use to retrieve data from the database. The SQL statement uses a simple join and produces a list of albums ordered by artist name:
sqlText = "SELECT b.artist_name as Artist, "
sqlText = sqlText & "a.rec_title as Title, "
sqlText = sqlText & "a.rec_format as Format, "
sqlText = sqlText & "a.rec_numberoftracks as Tracks "
sqlText = sqlText & "FROM tbl_Recordings a LEFT JOIN tbl_Artists b "
sqlText = sqlText & "ON a.rec_artist = "
sqlText = sqlText & "b.artist_id "
sqlText = sqlText & "ORDER BY b.artist_name"
The next section of the code listed here is the most interesting part:
set dbConnection = Server.CreateObject("ADODB.Connection")
set dbCommand = Server.CreateObject("ADODB.Command")
set dbRecords = Server.CreateObject("ADODB.Recordset")
First in this code is the creation of the necessary database access objects. The Active Data Objects library supplies the necessary objects for creating a connection to the data source and for manipulating the data returned from the data source. The first object we create is the Connection Object. In ASP, you have to use the Server object in order to create objects for use on the page. The method CreateObject takes the name of the object to create and returns a reference to the newly created object. Also notice that when you are initializing object variables, you must use the set operator for the assignment. Nonobject variables simply need to be followed by the equal (=) sign, but object variables must use the set operator. There is another way to create object using the object tag, which you will see in an upcoming article.
We need three objects for our database connectivity. There are shortcuts that will implicitly create these objects, but for learning purposes, I think it is best to go through all the steps. The first object created is the Connection object. The Connection object supplies the necessary connectivity to the database and, as you will see, is simple to implement. We call a single method (Open) on the Connection object that establishes the connection using the connection string stored in the Application object. By using an identical string, we allow the OLEDB engine to perform connection pooling so that if our Web site has a lot of traffic, the connections to the database will be reused instead of reconnecting to the database for every request. Here is the remaining code for the Connection object.
The next object we use is the Command object. The Command object represents exactly what commands are being sent to the data source. First, the Connection object is referenced as the ActiveConnection. Next, the CommandType is set to represent the type of command being used. In this case, adCmdText is used, meaning that the command is contained in a text string. Typically, your commands will be either text strings with formatted SQL or calls to stored procedures. Setting the proper command type is important since the different types of calls involve different amounts of overhead. A stored procedure called with the adCmdText parameter would not be as efficient. The Command object supports adding parameters in order to pass arguments to stored procedures. You will see how that works as we put together our database object in the next article.
The last property set on the Command object is the actual text for the command, which is stored in the sqlText variable:
.ActiveConnection = dbConnection
.CommandType = adCmdText
.CommandText = sqlText
Finally, we’re down to the last object in our database code. The RecordSet object represents the data returned from the data source. First, we tell the RecordSet object that the RecordSet will exist on the client. This is really the only option for CursorLocation you should use with an Internet/intranet application. Next, a call to the Open method accepts the Command object as well as some configuration parameters. The parameters are important to optimize the call to the database. Since we are simply reading some records, set the type of Recordset to adOpenStatic and the locking to adLockReadOnly. This creates a very efficient call to the database for a read only operation. You will see additional options for these parameters in the database object we are going to create.
.CursorLocation = adUseClient
.Open dbCommand, , adOpenStatic, adLockReadOnly
The last piece of code frees up the database objects and makes sure that the RecordSet object is no longer maintaining a reference to the data source connection:
set dbCommand.ActiveConnection = nothing
set dbCommand = nothing
set dbConnection = nothing
set dbRecords.ActiveConnection = nothing
At this point, we still have a reference to the RecordSet with the dbRecords variable. It is important to set object references to nothing as soon as you have completed using them. The more quickly you release objects the better.
The only task left for this page is to output the data contained in the RecordSet object for the user:
Response.Write("<table border=""0"" width=""100%"">")
for each dbField in dbRecords.Fields
Response.Write("</b></td>" & vbcrlf)
if ( not dbRecords.eof ) then
Response.Write(dbRecords.GetString(adClipString, , "</td><td>", "</td></tr><tr><td>" & vbcrlf, " "))
Response.Write("No Records Found.")
set dbRecords = nothing
After the table is output, the first section of code uses a for loop to access the Fields collection of the RecordSet. The Fields collection contains a Field object for each data field returned from the data source. This loop simply uses the name property to output headers in bold across the first row of the table.
Now, we end that row and start a new row as well as starting the first column on that row. A quick check of the EOF property verifies whether there are any rows in the current RecordSet. If there are no rows, we can write out a message and close the table cell and row as well as the table itself. If there are records in the RecordSet, we can use a really neat method of the RecordSet object that will automatically loop through all the records. The GetString method returns the data from the RecordSet in a string that we send to the Response.Write method for output to the user.
The GetString method looks confusing, but it is very simple once you get the hang of how it uses the arguments. The first argument adClipString, tells the method to delimit rows by the RowDelimiter and columns by the ColumnDelimiter and to replace null values with the NullExpr. The second argument allows you to specify the number of rows to use in the conversion. Leaving this argument blank means to use all rows. The third argument is ColumnDelimiter. In this case, we used </td><td>, which stops the current table cell and starts the next table cell.
The fourth argument is RowDelimiter, which we use to stop the current cell and row followed by starting a new table row and cell. I added vbcrlf to the row delimiter so that the source in the browser would have some line breaks. Otherwise, the entire table would be one long line if you viewed the HTML source.
The last argument is what you should use in place of null values (NullExpr). Since empty table cells can be an issue with some browsers, substitutes a space for the null field values.
Well, that was a lot of code at one time. Using objects in ASP is not difficult if you know what object you need and how to use the object itself. As our series continues, you will see more and more objects used in ASP. The Active Data Objects are a great interface for getting data from various data sources, and the GetString method of the RecordSet object is a great shortcut for outputting your data in a table. GetString is limited, in that you can’t make different output choices for the various fields, but it is a very efficient shortcut for looping through all the records. Next time, I’ll present a database object you can use so that every page doesn’t have to have all the ADO objects in the page itself.