ADO Connection Object

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

DSN-less

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

System DSN

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

OLE DB

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

MySQL

DSN-less

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

System DSN

<%
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.

<!DOCTYPE html>
<html>
<head>
    <title>My Page</title>
</head>
<body>
    <% 
    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
    %> 
    <table style="width:500px;">
        <tr>
            <%for each x in oRS.Fields
            Response.Write("<th>" & x.name & "</th>")
            next%>
        </tr>
        <%do until oRS.EOF%>
        <tr>
            <%for each x in oRS.Fields%>
            <td><%Response.Write(x.value)%></td>
            <%next
            oRS.MoveNext%>
        </tr>
        <%loop
        oRS.close
        oConn.close
        Set oRS=nothing
        Set oConn=nothing
        %>
    </table>
</body>
</html>

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.