Did you find this useful?
Socialize it today.


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

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: ActiveX Data Objects ADO Programming for Dummies