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>
