# Objectives

This describes database connectivity with ADO (ActiveX Data Objects) and how you can use this to communicate and manage a database server. You will learn how to open a connection to a database, run INSERT, UPDATE, DELETE, and SELECT statements as well as execute a stored procedure.

# Content

Active Server Pages, being a scripted language, is a stateless language. By this, we mean that it doesn't preserve the state of the application between page loads. Nearly all ASP hosting solutions will include some sort of database hosting. Most common is MySQL, followed by Microsoft SQL Server. No matter what database you are using, ADO will allow you to manage the database through Active Server Pages.
While it's true that you can store persistant data in the Application object. This is basically just stored in a memory cache that will be flushed whenever IIS or the server is restarted. It also causes problems when trying to create an application which will be used in a server cluster (where each cluster has its own version of the Application cache.)

## Connecting to a Database

Here is a sample showing how to open a connection to a database. To do this, we call the Server.CreateObject to create an instance of the built-in ADODB Connection object. Not only is this used to create the initial connection to the database, it will also be used to create the Recordset and Command Objects later.
You will need to replace the text mysqluser, mysqlpass, and mysqldbname with the correct values for your database. Note that this is accessing a MySQL server on the local server (localhost) using ODBC.
Note that you could wrap this statement with an On Error block to trap any exceptions that may occur.

## INSERT, UPDATE, and DELETE

The following will use the ADODB.Connection object to run an INSERT query on the database. The code is the same whether you are doing INSERT, UPDATE, or DELETE. You only need to change the sQuery variable with your modified query.
The constants adCmdText and adExecuteNoRecords are described in the section ADO Constants.

## SELECT

The following code can be used to run a SELECT statement against a database. You must first open a connection to a database using the code described in the section Connecting to a Database.
* 1. When sQuery in above code snippet is set as example 1. it will fetch records with companyid having value 1, in this SQL statement WHERE clause is used to define that criteria.
The constants adOpenKeySet, adLockReadOnly, and adCmdText are described in the section ADO Constants. Now that you have opened a recordset, what can you do with it?

### Retrieving the Recordset

The following code will show you how to use the recordset object (rs) described in the SELECT section to retrieve values from your database query.

## Closing a Database Connection

You should always close your database connection when you are done accessing the database. It is not required, but it is always a good idea.

## ADO Constants

| Variable | Purpose |
| 'adOpenKeySet | Open a keyset Const adOpenKeySet = 1 |
| adLockReadOnly | Create a database lock for read only access to a table. This is a forward-only cursor which provides the most efficient method for retrieving results. Const adLockReadOnly = 1 |
| adLockPessimistic | This locking method will lock a database record as soon as edits have been made to the database. (Not really used in Active Server Pages) Const adLockPessimistic = 2 |
| adLockOptimistic | Use optimistic record locking on the database records. Meaning a record will only be locked when records have been modified and edits are committed back to the database. (Not really used in Active Server Pages) Const adLockOptimistic = 3 |
| adCmdText | Indicates the SQL command being passed is text (an SQL statement) Const adCmdText = 1 |
| adCmdTable | Indicates the SQL command being passed is the name of a database table to open (all rows and fields) Const adCmdTable = 2 |
| adCmdStoredProc | Indicates the SQL command being passed is the name of a stored procedure to execute. When using this call, the parameters must be defined separately. Const adCmdStoredProc = 4 |
| adCmdUnknown | Indicates the SQL command being passed is unknown - the ADO library will do it's best to interpret what type of command was intended. Const adCmdUnknown = 8 |
| adStateOpen | Indicates the current state of the result set. This indicates the recordset is open and data retrieval operations can be performed. Const adStateOpen = 1 |
| adStateClosed | Indicates the current state of the result set. This indicates the recordset is closed and data retrieval are forbidden. Const adStateClosed = 0 |
| adExecuteNoRecords | Indicate that no recordset is returned. SQL command is stored procedure call, INSERT, UPDATE, or DELETE statement. Const adExecuteNoRecords = 128 |
| adParamInput | Stored procedure parameter is an input (this is the default) meaning we are just passing data into the procedure. Const adParamInput = 1 |
| adParamOutput | Stored procedure parameter is an output meaning that data will be returned by the procedure. Const adParamOutput = 2 |
| adParamInputOutput | Stored procedure parameter is both an input and an output. Data is passed into the procedure and then returned after execution is complete. Const adParamInputOutput = 3 |
| adParamReturnValue | Indicates that the variable holds the return value from a procedure Const adParamReturnValue = 4 |
| adVarChar | Variable character (string) SQL data type Const adVarChar = 200 |
| adChar | Fixed length character SQL data type Const adChar = 129 |
| adInteger | Integer SQL data type Const adInteger = 3 |
| adCurrency | Currency SQL data type Const adCurrency = 6 |

# Summary

Using ADODB is the ideal way to connect to a database through Active Server Pages. ADODB utilizes ODBC or OLE DB technologies to connect to a database depending on the connection string you use when using the ADODB.Connection::Open method. The most efficient method of access is OLE DB.
You may choose to create a database library code to simplify the task of working with a database. You can create a database class which manages all the tasks of communicating with a database. Combine this with database configuration settings stored in a global.asa file, and you will have a quick yet powerful way of executing database queries.
Make sure to make use of the ADO Constants to make the most efficient use of the ADO methods and your database server. The examples above should give you everything you need to work with a database.

# Review Questions

* What does ADO stand for?
* Which object is used to connect to a database?
* What method do you use to connect to a database?
* Which object do you need to retrieve results from SELECT?
* How do you retrieve the value of a field from the current record?
* How do you enumerate all fields from a record
* How do you close a connection to a database

# Exercises

* Write the code to run an INSERT, UPDATE, or DELETE query
* Write the code to execute a stored procedure?
* Write the code to execute a SELECT statement and read its results