ADO for the Beginner - How to Display Data
(Page 5 of 5 )
To display your data, do the following (remember to use the asp extension when naming your file):
<html>
<body><%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open " c:/website/chucknorrisvictims.mdb "
set rs =Server.CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM Victims", 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>
The above code creates a connection to the database, opens the chucknorrisvictims.mdb, and selects all of the data from it. It then creates a loop that runs through the file, writing all of the data inside and inserting a page break in between each set of data. It continues looping until it reaches the end of the data file (EOF). Finally it closes the connection to the database. Here is a sample of what you would see (using theoretical data; I don't really have a list of Chuck Norris's victims. There is no database that could contain such a massive amount of data).
VictimName: Ralph Macchio
VictimInjury: Broken foot
HowItHappened: Ralph Macchio tried his crane kick on Chuck. When it connected to Chuck's powerful chest hairs, his foot shattered.
VictimName: Jackie Chan
VictimInjury: Forced to work with Chris Tucker the rest of his life
HowItHappened: Chuck Norris punched Jackie Chan so hard he went from an A-list actor to a B-list actor
VictimName: Death
VictimInjury: Granted eternal life
HowItHappened: Death came for one of Chuck's victims before he was finished with him, so Chuck did a roundhouse kick and hit Death so hard not only did he become a living being, but he gained immortality as well.
And so forth.
As you can see it isn't the prettiest data in the world. To give it more uniformity and make it easier on the eyes we could always place the data into an HTML table instead, like so:
<html>
<body><%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open " c:/webdata/chucknorrisvictims.mdb "
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT VictimName, VictimInjury, HowItHappened FROM Victims", 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>
This works the same way our previous sample did, except now it places the data in a table (it also selects three columns from the table and not technically the whole table).
Ralph Macchio | Broken Foot | Ralph Macchio tried his crane kick on Chuck. When it connected to Chuck's powerful chest hairs, his foot shattered. |
Jackie Chan | Forced to work with Chris Tucker the rest of his life | Chuck Norris punched Jackie Chan so hard he went from an A-list actor to a B-list actor |
Death | Granted Eternal Life | Death came for one of Chuck's victims before he was finished with him, so Chuck did a roundhouse kick and hit Death so hard not only did he become a living being, but he gained immortality as well. |
Much better, but you will notice that there are no column headers. We are going to remedy that with the following code:
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/chucknorrisvictims.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT VictimName, VictimInjury, HowItHappened FROM Victims"
rs.Open sql, conn
%>
<table border="1" width="100%" bgcolor="BLUE">
<tr>
<%for each x in rs.Fields
response.write("<th align='left' bgcolor='Yellow'>" & 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>
Our table will now appear this way:
VictimName | VictimInjury | HowItHappened |
Ralph Macchio | Broken Foot | Ralph Macchio tried his crane kick on Chuck. When it connected to Chuck's powerful chest hairs, his foot shattered. |
Jackie Chan | Forced to work with Chris Tucker the rest of his life | Chuck Norris punched Jackie Chan so hard he went from an A-list actor to a B-list actor |
Death | Granted Eternal Life | Death came for one of Chuck's victims before he was finished with him, so Chuck did a roundhouse kick and hit Death so hard not only did he become a living being, but he gained immortality as well. |
As you can see we also added color to the table. Viola!
That's it for this tutorial. In our next article we will discuss working with queries in ADO.
Till then...
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 27 
