ASP Databases

The ADO Interface. Active Data Objects (ADO) is a general object-oriented interface to a wide variety of databases. It uses ODBC (Open Database Connectivity) interface and other interfaces as an underlying mechanism. The big advantage of ADO is that it morphs a variety of interfaces into the ASP mold.

The ADO Object Model ADO has just three main objects, Connection, Recordset, and Command. The Connection object provides a connection to the data source. The Command object provides a way to fetch or modify records using SQL (Structured Query Language). The Recordset object provides a way to access individual records fetched by the SQL command.

Each of these objects has a collection of properties. The Recordset object has a Fields collection which provides information about the individual fields in the record.

The Connnection Object

Once an instance of a connection object is established, it may be used to open a connection with an existing database using the method Open. To open a database, a Data Source Name (DSN) must be created using the ODBC Administrator program from as described in our April discussion of ASP. For example, suppose that we wish to connect to the pubs database using the DSN Books.

Set oC = Server.CreateObject("ADODB.Connection")
oC.Open "DATABASE=pubs;DSN=Books;UID=sa;Password=;"
oC.Execute "DELETE * FROM Contact WHERE State = 'LA'"
oC.Close
Set oC = Nothing

Notes:
The argument to the Open command is called the "Connection String" This string contains the information required to establish a connection through ODBC. In this case, we need the DSN, the user, and password required to connect to the database.
There are several properties, such as the ConnectionTimeout property which could be tailored to special circumstances. In this case, we use the defaults.
The last line is a Command that executes an SQL statement.
It is important to release the resources when we are finished. Here we call close to release the connection and set oC to nothing to release the object.

Fetching Records. SQL can also be used to fetch one or more records as shown in this example:

Set oC = Server.CreateObject("ADODB.Connection")
oC.Open "DATABASE=pubs;DSN=Books;UID=sa;Password=;"
sSQL = "SELECT * FROM Contact WHERE State = 'LA'"
Set oRs = oC.Execute(sSQL, lRecs, cmdText)
oRs.MoveFirst
Do While Not oRs.EOF
For Each oField in oRs.Fields
Response.Write oField.Value
Next
Ors.MoveNext
Loop
oRs.Close
Set oRs = Nothing
oC.Close
Set oC = Nothing

Notes:
The open and SQL are similar to the earlier example. The SELECT * fetches all of the fields for this table. In this case we only select those records where the state is "LA"
Then we fetch each record starting from first record in the recordset.
Then we print out all the field values for that record.
Be sure to release all of the objects and the connection when you are finished.

  Latest articles

DNS Lookup Tool

ASP 0131 Disallowed Parent Path