ADO Displaying Data in a Table

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.

empName	empTitle
John Smith	Sales Associate
Jane White	CEO
Jim Bore	Accountant

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

Recommended Books & Training Resources