ADO Recordset Object

Saturday, September 22, 2012

tags ADO

After opening a connection with your data source, typically a database, your next step is to read from the data source and load the data into a recordset. To perform this operation, we simply need to create an ADO Recordset object. After the recordset object is created, you will open it and pass information to the data source. Typically this information would consist of the SQL query and the connection object.

Syntax

<%
set oRs=Server.CreateObject("ADODB.recordset")
oRs.Open "Data Requested", Connection Info
%>


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.

Connection and Recordset Object

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

Extract the Data

After a recordset is opened, we can extract data from recordset.

<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;%>
  </table>

Close the Connections and Clean Up

<% 
oRS.close
oConn.close
Set oRS=nothing
Set oConn=nothing
%>

Finished Code

<!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 %>
</table>
<%
oRS.close
oConn.close
Set oRS=nothing
Set oConn=nothing
%>

</body>
</html>

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

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