The ADO Connection object provides a way to open a connection to a data source. Once the connection is established, you can then access and manipulate data from your data source. One of the most common tasks when access a data source such as a database is querying data.

If you want to access a database multiple times, you should establish a connection using the Connection object. You can also make a connection to a database by passing a connection string via a Command or Recordset object. However, connecting in this manner is only appropriate for one single query.

Syntax

<% Set oConn=Server.CreateObject(“ADODB.connection”) %>

Connection Strings

ADO offers several ways to connect to and open a data source. The following examples are for demonstration purposes only. The connection string that you choose will depend on your data source.

Some of the connection strings below may work for the same data source. You should note that you must change elements such as database name, server name, database location, Data Source Name (DSN), etc…

Microsoft Access

DSN-less

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\mydatabase.mdb” %>

System DSN

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “DSNname” %>

OLE DB

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:\mydatabase.mdb” %>

MS SQL

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=mydatabase” %>

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “DSN=MyDSN;UID=user;PWD=password;DATABASE=mydatabase” %>

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase” %>

MySQL

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “Driver={MySQL ODBC 3.51 Driver};SERVER=ServerName;DATABASE=mydatabase;UID=username;PWD=password” %>

<% Set oConn = Server.CreateObject(“ADODB.Connection”) oConn.open “DSN=MyDSN” %>

Example

In this example, we are going to connect to a table in a MySQL database called “Employees”. We will use a DNS-less connection. We will run a SQL Select Query and capture the results in a recordset. Finally, extract the information from the recordset and display the results in a table.

My Page<% Dim oConn, oRS, datasource, sql Set oConn=Server.CreateObject("ADODB.Connection") Set oRS = Server.CreateObject("ADODB.recordset") datasource = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password" sql = "SELECT empName, empTitle FROM employees" oConn.Open datasource oRS.Open sql, oConn %><%for each x in oRS.Fields Response.Write("") next%><%do until oRS.EOF%><%for each x in oRS.Fields%><%next oRS.MoveNext%><%loop oRS.close oConn.close Set oRS=nothing Set oConn=nothing %>
" & x.name & "
<%Response.Write(x.value)%>

Cleaning Up

After you create an instance of the Connection object and open the connection, you access the data source and collect the information into a recordset. After you are finished working with the data, you should close the active connection and clean up your objects.

Open ADO objects just before they are needed, and close them right after you are done. This frees resources while other logic is processing. It’s also just good programming practice.

<% oConn.close Set oConn=nothing %>