ADO Displaying Data in a Table

Sunday, September 23, 2012

tags ADO

Displaying Data from a Database is an integral part of any typical web application. In this tutorial, we will learn how to display the data that we retrieve from our database in a nicely organized table. The first example below displays the results of our query in a non-tabular manner, while the second example takes the same recordset and formats the results so that the data is structured in a table format.

Display Non-Tabular

<!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
do until oRS.EOF
  for each x in oRS.Fields
    Response.Write(x.name & "=" & x.value & "<br />")
  next
  Response.Write("<br />")
  oRS.MoveNext
loop
oRS.close
oConn.close
Set oRS=nothing
Set oConn=nothing
%>

</body>
</html>

The results are as follows:

empName=John Smith
empTitle=Sales Associate

empName=Jane White
empTitle=CEO

empName=Jim Bore
empTitle=Accountant


Display Results in a Table

<!DOCTYPE html>
<html>
<head>
    <title>My Page</title>
<style type="text/css">
table {border:1px solid #ABABAB;border-collapse:collapse;color:#4F4F4F;width:50%}
th {border:1px solid #ABABAB;background-color:#5F5F5F;color:#FFFFFF;padding:5px;}
tr {border:1px solid #ABABAB;}
td {border:1px solid #ABABAB;padding:5px;}
</style>
</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>
  <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>

As you can see here the format is quite different from the first example. With a bit of styling, you can easily present your data in a professional looking manner.

empNameempTitle
John SmithSales Associate
Jane WhiteCEO
Jim BoreAccountant

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

Recommended Books & Training Resources

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