HomeBrainDump ADO Queries and Working with Recordsets
ADO Queries and Working with Recordsets
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.
Contributed by James Payne Rating: / 4 January 14, 2008
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
One way that you can allow users to select data from your database is through the use of drop-down lists or menus. In the following program we will create a drop-down list containing names that appear in our VictimName column:
<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 DISTINCT VictimName FROM Victims" rs.Open sql,conn
victimname=request.form("victimname")
%>
<form method="post"> Please Select a Name from the List <select name="victimname"> <% do until rs.EOF response.write("<option") if rs.fields("victimname")=victimname then response.write(" selected") end if response.write(">") response.write(rs.fields("victimname")) rs.MoveNext loop rs.Close set rs=Nothing %> </select> <input type="submit" value="Click for Victim Information"> </form>
<% if victimname<>"" then sql="SELECT VictimName,VictimInjury,HowItHappened FROM Victims WHERE victimname='" & victimname & "'" set rs=Server.CreateObject("ADODB.Recordset") rs.Open sql,conn %> <table width="100%" cellspacing="0" cellpadding="2" border="1"> <tr> <th>VictimName</th> <th>VictimInjury</th> <th>HowItHappened</th> </tr> <% do until rs.EOF response.write("<tr>") response.write("<td>" & rs.fields("victimname") & "</td>") response.write("<td>" & rs.fields("victiminjury") & "</td>") response.write("<td>" & rs.fields("howithappened") & "</td>") response.write("</tr>") rs.MoveNext loop rs.close conn.Close set rs=Nothing set conn=Nothing%> </table> <% end if %>
</body> </html>
This program will create a drop down menu with a list of names from the Victims database, and a submit button. When the user selects a name from the list and clicks on the button, it will return that record and any data pertaining to it. Let's say the user selects the name Ralph Macchio. Here is the result:
Ralph Macchio
Broken Foot
Ralph Macchio tried his crane kick on Chuck. When it connected to Chuck's powerful chest hairs, his foot shattered.
It is possible to sort recordsets in ADO by using the SQL ORDER BY function. Here, we will sort our data by VictimName in ascending order:
<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 FROM Victims ORDER BY VictimName" 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>
In the above code we tell the program to select all of the data in the VictimName column and sort in ascending order by VictimName. The program then opens up the database connection, writes each field by looping through the values until it comes to the end of the file, and then closes the connection. The result would be:
VictimName
Death
Jackie Chan
Ralph Macchio
To sort by Descending, you would do the same thing, except you would add DESC to the end of the SQL Select statement:
<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 FROM Victims ORDER BY VictimName" 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>
Which of course would result in:
VictimName
Ralph Macchio
Jackie Chan
Death
We could also allow the user to select which column they would like to sort on as well:
<html> <body>
<table border="1" width="100%"> <tr> <th align="left"> <a href="sample.asp?sort=victimname">Victim Name</a> </th> <th align="left"> <a href="sample.asp?sort=victiminjury">Victim Injury</a> </th> </tr> <% if request.querystring("sort")<>"" then sort=request.querystring("sort") else sort="victimname" end if
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 FROM Victims ORDER BY " & sort rs.Open sql,conn
do until rs.EOF response.write("<tr>") for each x in rs.Fields response.write("<td>" & x.value & "</td>") next rs.MoveNext response.write("</tr>") loop rs.close conn.close %> </table>
</body> </html>
The above code creates a table consisting of the VictimName and VictimInjury columns. Each column header has a link in it. If the user clicks on either link, it will sort the table by that column.
You can add records to a database using the INSERT INTO function from our dear friend SQL. The first step is to create an HTML form for the user to submit data to us:
<html>
<body>
<p>Enter your data and click the Submit button to add a record to the database:</p>
<form method=”post” action=”sampleform.asp”>
<table>
<tr>
<td>Victim Name</td>
<td><input name=”victim”></td>
</tr>
<tr>
<td>Victim Injury</td>
<td><input name-”injury”></td>
</tr>
<tr>
<td>How It Happened</td>
<td><input name=”how”></td>
</tr>
</table>
<br /><br /><br />
<input type=”submit”>
</form>
</body>
</html>
Next we must make an .asp page that will process the data:
<html>
<body>
<%
set conn=Server.CreateObject(“ADODB.Connection”)
conn.Provider=”Microsoft.Jet.OLEDB.4.0”
conn.Open “c:/website/chucknorrisvictims.mdb”
sql=”INSERT INTO Victims (VictimName,VictimInjury)”
sql=sql & “ VALUES “
sql=sql & “('” & Request.Form(“victim”) & “',”
sql=sql & “'” & Request.Form(“injury”) & “',”
sql=sql & “'” & Request.Form(“how”) & “')”
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write(“You do not have permission to update this database”)
else
Response.Write(“<h1>” & recaffected & “has been added</h1>”)
The above code displays the data in our database. If the user clicks on any of the Victim Names, they will be taken to an ASP page where they can opt to delete the record. Here is the code for that page:
<html>
<body>
<%
set Conn=Server.CreateObject(“ADODB.Connection”)
conn.Provider=”Microsoft.Jet.OLEDB.4.0”
conn.Open “c:/website/chucknorrisvictims.mdb”
vic=Request.Form(“VictimName”)
if Request.Form(“VictimName”)=”” then
set rs.Server.CreateObject(“ADODB.Recordset”)
rs.open “SELECT * FROM Victims WHERE VictimName='” & vic & “'”,conn
%>
<form method=”post” action=”sampledelete.asp”>
<table>
<%for each x in rs.Fields%>
<tr>
<td><%=x.name%>” value=”<%=x.value%>”></td>
<%next>
</tr>
</table>
<br /><br /><br />
<input type=”submit” value=”Click to Delete”>
</form>
<%
else
sql=”DELETE FROM Victims”
sql=sql & “WHERE VictimName='” & vic & “'”
on error resume next
conn.Execute sql
if err<>0 then
response.write(“You do not have permission to delete”>
else
response.write(“The Record “ & vic & “ has been deleted.”)
end if
end if
conn.close
%>
</body>
</html>
This program will display the record the user chose from the previous page, and give them the option to delete the record. If they do not have permission, they will receive an error message; otherwise the record will be deleted and they will receive a message saying the record has been deleted.
Well that's it for this episode. In the next article we will learn how to update a database record, and start learning about ADO Objects.
BrainDump 
ADO for the Beginner
