ActiveX Data Objects (ADO) are an easy-to-use yet extensible technology for adding database access to your Web pages. You can use ADO to write compact and scalable scripts for connecting to Open Database Connectivity (ODBC) compliant databases and OLE DB compliant data sources. If you are a scripter with a modest understanding of database connectivity, you will find ADO's command statements uncomplicated and approachable. Likewise, if you are an experienced database programmer you will appreciate ADO's advanced, language neutral connection and query manipulation features.
Before creating database scripts you need to provide a way for ADO to locate, identify, and communicate with your database. Database drivers – programs that pass information from your Web application to a database – use a Data Source Name (DSN) to locate and identify a particular ODBC compliant database. Typically, the DSN contains database configuration, user security, and location information, and can take the form of an entry in the Windows NT registry or a text file.
With ODBC you can choose the type of DSN you wish to create: User, System, or File. The User and System DSN reside in the Windows NT registry. The System DSN enables all users logged on to particular server to access a database, while the User DSN limits database connectivity to a specific user with appropriate security credentials. The File DSN, which takes the form of text file, provides access to multiple users and is easily transferable from one server to another by copying DSN files. For these reasons, the examples in this topic will use the File DSN.
You can create a file based DSN by opening Control Panel from the Windows Start menu. Double-click the ODBC icon, and then select the File DSN property sheet. Click Add, choose your database's driver, and then click Next. Follow the proceeding instructions for configuring a DSN for your particular database software.
To configure a Microsoft Access Database File DSNNote For performance and reliability reasons, it is strongly recommend that you use a client-server database engine for the deployment of data driven Web applications that require high-demand access from more than 10 concurrent users. Although Active Server Pages works with any ODBC-compliant database, it has been extensively tested and is designed to work with client server databases such as Microsoft ® SQL Server ™ or Oracle.
ASP supports shared file databases (Microsoft ® Access or Microsoft ® FoxPro) as valid data sources. Although some examples in the ASP documentation use a shared file database, it is recommended that these types of database engines be used only for development purposes or limited deployment scenarios. Shared file databases may not be as well suited as client-server databases for very high-demand, production-quality Web applications.
To configure a SQL Server Database File DSN
Note If the database resides on a remote server, contact the server administrator for additional configuration information; the following procedure uses the ODBC default settings for SQL Server, which may not work for your hardware configuration.
Note Typically, you should only use logging for debugging database access problems.
If you are developing an ASP database application intended to connect to a remote SQL Server database you should also be aware of the following issues:
Note To improve performance when connecting to a remote databases, use TCP/IP Sockets.
For more information about these issues, visit the Microsoft SQL Server Technical Support Home Page at http://www.microsoft.com/sqlsupport/.
To configure an Oracle Database File DSN
Make sure that the Oracle client software is correctly installed on the computer where you intend to create the DSN. Consult your server administrator and database software documentation for more information.
Note DSN files have a .dsn extension and reside in the \Programs\Common Files\ODBC\Data Sources directory.
For more information about creating a DSN file, visit the Microsoft ODBC Web site at http://microsoft.com/odbc/.
The very first step in accessing database information is establishing a connection with the database source. ADO provides the Connection object which you can use for establishing and managing connections between your applications and ODBC databases. The Connection object features a variety of properties and methods which you can use to open and close database connections, and to issue queries for updating information.
To establish a database connection, you first create an instance of the Connection object. For example, the following script instantiates the Connection object and proceeds to open a connection:
<%
'Create a connection object
Set cn = Server.CreateObject("ADODB.Connection")
'Open a connection; the string refers to the DSN
cn.Open "FILEDSN=MyDatabase.dsn"
%>
Note The DSN string does not contain spaces, either, before or after the equal sign (=).
In this case, the Connection object's Open method refers to the file based DSN, containing location and configuration information about the database. Optionally, you can also refer directly to an explicit provider, data source, user ID, and password, rather than to a DSN.
With the Connection object's Execute method, you can issue Structured Query Language (SQL) queries to the database source and retrieve results. SQL, an industry standard language for communicating with databases, has commands for retrieving and updating information.
The following script uses the Connection object's Execute method to issue a query in the form of a SQL INSERT command, which inserts data into a specific database table. In this case, the script block inserts the name Jose Lugo into a database table named Customers.
<%
'Define file based DSN
strDSN = "FILEDSN=MyDatabase.dsn"
'Instantiate the Connection object and open a database connection
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Define SQL SELECT statement
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Jose','Lugo')"
'Use the Execute method to issue a SQL query to database
cn.Execute(strSQL)
%>
Note The file based DSN path string should not include spaces before and after the equal sign (=).
In addition to the SQL INSERT command, you can use the SQL UPDATE and DELETE commands to change and remove database information.
With the SQL UPDATE command you can change the values of items in a database table. The following script uses the UPDATE command to change the Customers table's FirstName fields to Jeff for every LastName field containing the last name Smith.
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=MyDatabase.dsn"
cn.Execute "UPDATE Customers SET FirstName = 'Jeff' WHERE LastName = 'Smith' "
%>
To remove specific records from a database table, use the SQL DELETE command. The following script removes all rows from the Customers table where the last name is Smith:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=MyDatabase.dsn"
cn.Execute "DELETE FROM Customers WHERE LastName = 'Smith'"
%>
Note You must be careful when using the SQL DELETE command. A DELETE command without an accompanying WHERE clause will delete all rows from a table. Be sure to include a SQL WHERE clause, which specifies the exact rows to be deleted.
Although the Connection object simplifies the task of connecting to a database and issuing a query, the Connection object has limitations. Specifically, you cannot create scripts with the Connection object that retrieve and display data database information; rather, you must know exactly what changes you want to make to a database and then implement the changes through use of a query.
For retrieving data, examining results, and making changes to your database, ADO provides the Recordset object. As its name implies, the Recordset object has features that you can use, depending on your query constraints, for retrieving and displaying a set of database rows, or records. The Recordset object maintains the position of each record returned by a query, thus enabling you to "step" through results one item at a time.
Depending on how you configure the Recordset object's cursor type properties, you can scroll through and update records. Database cursors behave like pointers that let you locate a specific item in a set of records. Cursors are especially useful for retrieving and inspecting records, and then carrying out an operation based on those records. The Recordset object has properties that enable you to precisely control the behavior of cursors, improving your ability to inspect and update results. For example, you can use the CursorType and CursorLocation properties to configure a cursor type that returns a result set to your client application (results normally remain on the database server) and displays the latest changes to a database made by other users.
Successful database applications employ both the Connection object, to establish a link, and the Recordset object, to manipulate returned data. By "teaming up" the specialized functions of both objects you can develop database applications to carry out almost any data handling task. For example, the following server-side script uses the Recordset object to execute a SQL SELECT command. The SELECT command retrieves a specific set of information based on query constraints. The query also contains a SQL WHERE clause, used to narrow down a query to a specific criterion. In this example, the WHERE clause limits the query to all records containing the last name Smith from the Customers database table.
<%
'Establish a connection with data source
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate a Recordset object
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
'Open a recordset using the Open method
' and use the connection established by the Connection object
strSQL = "SELECT FirstName, LastName FROM Customers WHERE LastName = 'Smith' "
rsCustomers.Open strSQL, cn
'Cycle through record set and display the results
' and increment record position with MoveNext method
Set objFirstName = rsCustomers("FirstName")
Set objLastName = rsCustomers("LastName")
Do Until rsCustomers.EOF
Response.Write objFirstName & " " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
Notice that in the previous example, the Connection object established the database connection and the Recordset object used the same connection to retrieve results from the database. This method is advantageous when you need to precisely configure the way in which the link with the database is established. For example, if you needed to specify the time delay before a connection attempt aborts, you would need to use the Connection object to set this property. However, if you just wanted to establish a connection using ADO's default connection properties, you could use Recordset object's Open method to establish a link:
<%
strDSN = "FILEDSN=MyDatabase.dsn"
strSQL = "SELECT FirstName, LastName FROM Customers WHERE LastName = 'Smith' "
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
'Open a connection using the Open method
'and use the connection established by the Connection object
rsCustomers.Open strSQL, strDSN
'Cycle through the record set, display the results,
' and increment record position with MoveNext method
Set objFirstName = rsCustomers("FirstName")
Set objLastName = rsCustomers("LastName")
Do Until rsCustomers.EOF
Response.Write objFirstName & " " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
When you establish a connection using the Recordset object's Open method to establish a connection, you are implicitly using the Connection object to secure the link.
With the ADO Command object you can execute queries in the same way as queries executed with the Connection and Recordset object, except that with the Command object you can prepare, or compile, your query on the database source and then repeatedly reissue the query with a different set of values. The benefit of compiling queries in this manner is that you can vastly reduce the time required to reissue modifications to an existing query. In addition, you can leave your SQL queries partially undefined, with the option of altering portions of your queries just prior to execution.
The Command object's Parameters collection saves you the trouble of reconstructing your query each time you want to reissue your query. For example, if you need to regularly update supply and cost information in your Web based inventory system, you can predefine your query in the following way:
<%
'Open a connection using Connection object Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate Command object; use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO Inventory (Material, Quantity) VALUES (?, ?)"
'Save a prepared (or pre-compiled) version of the query specified in CommandText
'property before a Command object's first execution.
cm.Prepared = True
'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("material_type",200, ,255 )
cm.Parameters.Append cm.CreateParameter("quantity",200, ,255 )
'Define and execute first insert
cm("material_type") = "light bulbs"
cm("quantity") = "40"
cm.Execute
'Define and execute second insert
cm("material_type") = "fuses"
cm("quantity") = "600"
cm.Execute
%>
Examining the previous example, you will notice that the script repeatedly constructs and reissues a SQL query with different values, without having to redefine and resend the query to the database source. Compiling your queries with the Command object also offers you the advantage of avoiding problems that can arise from concatenating strings and variables to form SQL queries. In particular, by using the Command object's Parameter collection, you can avoid problems related to defining certain types of string, date, and time variables. For example, SQL query values containing apostrophes (') can cause a query to fail:
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Robert','O'Hara')"
Notice that the last name O'Hara contains an apostrophe which conflicts with the apostrophes used to denote data in the SQL VALUES keyword. By binding the query value as a Command object parameter, you avoid this type of problem.
Web pages containing HTML forms can enable users to remotely query a database and retrieve specific information. With ADO you can create surprisingly simple scripts that collect user form information, create a custom database query, and return information to the user. Using the ASP Request object, you can retrieve information entered into an HTML form and incorporate this information into your SQL statements. For example, the following script block inserts information supplied by an HTML form into a table. The script collects the user information with the Request object 's Form collection.
<%
'Open a connection using Connection object. The Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate Command object
'and use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO MySeedsTable (Type) VALUES (?)"
'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("type",200, ,255 )
'Define and execute insert
cm("type") = Request("SeedType")
cm.Execute
%>
For more information about forms and using the ASP Request object, see Working with HTML Forms.
One of the main challenges of designing a sophisticated Web database application, such as an online order entry application servicing thousands of customers, is properly managing database connections. Opening and maintaining database connections, even when no information is being transmitted, can severely strain a database server's resources and result in connectivity problems. Well designed Web database applications recycle database connections and compensate for delays due to network traffic.
A database server experiencing a sudden increase in activity can become backlogged, greatly increasing the time required to establish a database connection. As a result, excessive connection delays can reduce the performance of your database application.
With the Connection object's ConnectionTimeout you can limit the amount of time that your application waits before abandoning a connection attempt and issuing an error message. For example, the following script sets the ConnectionTimeout property to wait twenty seconds before cancelling the connection attempt:
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 20
cn.Open "FILEDSN=MyDatabase.dsn"
The default for the ConnectionTimeout property is 30 seconds.
Note Before incorporating the ConnectionTimeout property into you database applications, make sure that your connection provider and data source support this property.
Web database applications that frequently establish and terminate database connections can reduce database server performance. ASP supports efficient connection management by using the connection pooling feature of ODBC 3.5. Connection pooling maintains open database connections and manages connection sharing across different user requests to maintain performance and to reduce the number of idle connections. On each connection request, the connection pool first determines if there is an idle connection in the pool. If so, the connection pool returns that connection instead of making a new connection to the database.
If you want your ODBC driver to participate in connection pooling you must configure your specific database driver and then set the driver's CPTimeout property in the Windows NT registry. When ODBC disconnects a connection, the connection is saved in a pool, rather that disconnected. The CPTimeout property determines the length of time that a connection remains in the connection pool. If the connection remains in the pool longer than the duration set by CPTimeout, the connection is closed and removed from the pool. The default value for CPTimeout is 60 seconds.
You can selectively set the CPTimeout property to enable connection pooling for a specific ODBC database driver by creating a registry key with the following settings:
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\driver-name\CPTimeout = timeout (REG_SZ, units are in seconds)
For example, the following key sets the connection pool timeout to 180 seconds (3 minutes) for the SQL Server driver.
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Server\CPTimeout = 180
Note By default, your Web server activates connection pooling for SQL Server by setting CPTimeout to 60 seconds.
Although you can reuse a connection across multiple pages by storing the connection in ASP's Application object, doing so may unnecessarily keep open a connection open, defeating the advantages of using connection pooling. If you have many users that need to connect to the same ASP database application, a better approach is to reuse a database connection string across several Web pages by placing the string in ASP's Application object. For example, you can specify a connection string in the Global.asa file's Application_OnStart event procedure, such as in the following script:
Application.lock
Application("ConnectionString") = "FILEDSN=MyDatabase.dsn"
Application.unlock
Then in each ASP file that accesses the database, you can write:
<OBJECT RUNAT=Server ID=cn PROGID="ADODB.Connection"> </OBJECT>
to create an instance of the connection object for the page, and use the script:
cn.Open Application("ConnectionString")
to open the connection. At the end of the page, you close the connection with:
cn.CloseIn the case of an individual user who needs to reuse a connection across multiple Web pages, you may find it more advantageous to use the Session object rather than the Application object for storing the connection string.
To make the best use of connection pooling, explicitly close database connections as soon as possible. By default, a connection terminates after your script finishes execution. However, by explicitly closing a connection in your script after it is no longer needed, you reduce demand on the database server and make the connection available to other users.
You can use Connection object's Close method to explicitly terminate a connection between the Connection object and the database. The following script opens and closes a connection:
<% strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open
cn.Close
%>