Computers & ProgrammingADOBackend Development

ADO Recordset Object

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top