ADO Display

From http://www.w3schools.com (Copyright Refsnes Data)

# ADO Display

The most common way to display data from a recordset, is to display the data in an HTML table.

## Examples

Display records
How to first create a database connection, then a recordset, and then display the data in HTML.

Display records in an HTML table
How to display the data from the database table in an HTML table.

Add headers to the HTML table
How to add headers to the HTML table to make it more readable.

Add colors to the HTML table
How to add colors to the HTML table to make it look nice.

## Display the Field Names and Field Values

We have a database named "Northwind" and we want to display the data from the "Customers" table (remember to save the file with an .asp extension):

    <html>
    <body>

    <%
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "c:/webdata/northwind.mdb"

    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open "SELECT * FROM Customers", conn

    do until rs.EOF
      for each x in rs.Fields
        Response.Write(x.name)
        Response.Write(" = ")
        Response.Write(x.value & "<br />")
      next
      Response.Write("<br />")
      rs.MoveNext
    loop

    rs.close
    conn.close
    %>

    </body>
    </html>

Here is the result:

CustomerID = ALFKI
CompanyName = Alfreds Futterkiste
ContactName = Maria Anders
ContactTitle = Sales Representative
Address = Obere Str. 57
City = Berlin
PostalCode = 12209
Country = Germany

CustomerID = BERGS
CompanyName = Berglunds snabbköp
ContactName = Christina Berglund
ContactTitle = Order Administrator
Address = Berguvsvägen 8
City = Luleĺ
PostalCode = S-958 22
Country = Sweden

CustomerID = CENTC
CompanyName = Centro comercial Moctezuma
ContactName = Francisco Chang
ContactTitle = Marketing Manager
Address = Sierras de Granada 9993
City = México D.F.
PostalCode = 05022
Country = Mexico

....
....
....

## Display the Field Names and Field Values in an HTML Table

We can also display the data from the "Customers" table inside an HTML table with the following lines (remember to save the file with an .asp extension):

    <html>
    <body>

    <%
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "c:/webdata/northwind.mdb"

    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open "SELECT Companyname, Contactname FROM Customers", conn
    %>

    <table border="1" width="100%">
    <%do until rs.EOF%>
       <tr>
       <%for each x in rs.Fields%>
          <td><%Response.Write(x.value)%></td>
       <%next
       rs.MoveNext%>
       </tr>
    <%loop
    rs.close
    conn.close
    %>
    </table>

    </body>
    </html>

Here is the result:

Alfreds Futterkiste | Maria Anders
Berglunds snabbköp | Christina Berglund
Centro comercial Moctezuma | Francisco Chang
Ernst Handel | Roland Mendel
FISSA Fabrica Inter. Salchichas S.A. | Diego Roel
Galería del gastrónomo | Eduardo Saavedra
Island Trading | Helen Bennett
Königlich Essen | Philip Cramer
Laughing Bacchus Wine Cellars | Yoshi Tannamuri
Magazzini Alimentari Riuniti | Giovanni Rovelli
North/South | Simon Crowther
Paris spécialités | Marie Bertrand
Rattlesnake Canyon Grocery | Paula Wilson
Simons bistro | Jytte Petersen
The Big Cheese | Liz Nixon
Vaffeljernet | Palle Ibsen
Wolski Zajazd | Zbyszek Piestrzeniewicz

## Add Headers to the HTML Table

We want to add headers to the HTML table to make it more readable (remember to save the file with an .asp extension):

    <html>
    <body>

    <%
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "c:/webdata/northwind.mdb"

    set rs = Server.CreateObject("ADODB.recordset")
    sql="SELECT Companyname, Contactname FROM Customers"
    rs.Open sql, conn
    %>

    <table border="1" width="100%">
      <tr>
      <%for each x in rs.Fields
        response.write("<th>" & x.name & "</th>")
      next%>
      </tr>
      <%do until rs.EOF%>
        <tr>
        <%for each x in rs.Fields%>
          <td><%Response.Write(x.value)%></td>
        <%next
        rs.MoveNext%>
        </tr>
      <%loop
      rs.close
      conn.close
      %>
    </table>

    </body>
    </html>

Here is the result:

Companyname | Contactname
--- | ---
Alfreds Futterkiste | Maria Anders
Berglunds snabbköp | Christina Berglund
Centro comercial Moctezuma | Francisco Chang
Ernst Handel | Roland Mendel
FISSA Fabrica Inter. Salchichas S.A. | Diego Roel
Galería del gastrónomo | Eduardo Saavedra
Island Trading | Helen Bennett
Königlich Essen | Philip Cramer
Laughing Bacchus Wine Cellars | Yoshi Tannamuri
Magazzini Alimentari Riuniti | Giovanni Rovelli
North/South | Simon Crowther
Paris spécialités | Marie Bertrand
Rattlesnake Canyon Grocery | Paula Wilson
Simons bistro | Jytte Petersen
The Big Cheese | Liz Nixon
Vaffeljernet | Palle Ibsen
Wolski Zajazd | Zbyszek Piestrzeniewicz

From http://www.w3schools.com (Copyright Refsnes Data)