After a recordset is opened, we can display the data from the
recordset on an HTML page.
Suppose we have a database named "Northwind", we can display the
data from the "Customer" table with the following lines:
<%
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
rs.MoveNext
loop
rs.close
conn.close
%>
Display Records in a Table
We can also display the data from the "Customer" table inside an
HTML table with the following lines:
<%
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
%>
<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>
Examples
1. Display Records
This example demonstrates how to first create a database
connection, then create a recordset, and then display the data
on an HTML page.
Coding
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("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>
Output
CustomerID = ALFKI
CompanyName = Alfreds Futterkiste
ContactName = Maria Anders
ContactTitle = Sales Representative
Address = Obere Str. 57
City = Berlin
PostalCode = 12209
Country = Germany
CustomerID = ANTON
CompanyName = Antonio Moreno Taquerķa
ContactName = Antonio Moreno
ContactTitle = Owner
Address = Mataderos 2312
City = México D.F.
PostalCode = 05023
Country = Mexico
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 = BOTTM
CompanyName = Bottom-Dollar Markets
ContactName = Elizabeth Lincoln
ContactTitle = Accounting Manager
Address = 23 Tsawassen Blvd.
City = Tsawassen
PostalCode = T2F 8M4
Country = Canada
2. Display Records in a Table
This example demonstrates how to display the data from the
database table in an HTML table.
Coding
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers"
rs.Open sql, 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>
Output
Alfreds Futterkiste Maria Anders
Antonio Moreno Taquerķa Antonio Moreno
Berglunds snabbköp Christina Berglund
Bottom-Dollar Markets Elizabeth Lincoln
Centro comercial Moctezuma Francisco Chang
Drachenblut Delikatessen Sven Ottlieb
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 CellarsYoshi Tannamuri
3. Add Headers to the Table
This example demonstrates how to add headers to the HTML table
to make it more readable.
Coding
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("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>
Output
Companyname Contactname
Alfreds Futterkiste Maria Anders
Antonio Moreno Taquerķa Antonio Moreno
Berglunds snabbköp Christina Berglund
Bottom-Dollar Markets Elizabeth Lincoln
Centro comercial Moctezuma Francisco Chang