Did you find this useful?
Socialize it today.


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>

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