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.

<%for each x in oRS.Fields Response.Write("") next%><%do until oRS.EOF%><%for each x in oRS.Fields%><%next oRS.MoveNext%><%loop;%>
" & x.name & "
<%Response.Write(x.value)%>

Close the Connections and Clean Up

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

Finished Code

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