ADO Queries and Working with Recordsets
(Page 1 of 5 )
Back in late November of last year I wrote an article that covered ADO for the beginner. At that time I left off with ADO connections, recordsets, and how to display them. Finally returning to the topic after a spell, in this episode we will discuss queries, sorting, and a few other tricks with ADO.
If you'd like to review before we proceed, you can read the previous tutorial. Go ahead, I'll wait. Or if you want a quicker review, we'll pick up at the very end of it, where I showed you the Victims table from the chucknorrisvictims database:
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. |
Displaying Selected Records
We can use SQL to create queries in ADO when we want to see data that matches a certain criteria. Let’s say we are working with our chucknorrisvictims database and we want to extract everyone whose name begins with “J”:
<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")
sql="SELECT VictimName, VictimInjury FROM Victims
WHERE VictimName LIKE 'J%'"
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>
This would result in the following:
VictimName | VictimInjury |
Jackie Chan | Forced to work with Chris Tucker the rest of his life |
Next: Displaying Selected Records Using a Drop Down Menu >>
More BrainDump Articles
More By James Payne
