Did you find this useful?
Socialize it today.


ADO Connection Object

Thursday, September 20, 2012

tags ADO

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 a 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 record set 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. More about recordsets in the upcoming articles in this series. 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. Its also just good programming practice.

<%
oConn.close
Set oConn=nothing
%>

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

ADO Programmers Reference ADO Programming for Dummies ADO: ActiveX Data Objects