SQL question....

[fuelvolts]

Website in question: http://econweb.tamu.edu/graduate-beta2.asp?yr=0

I want to show all of our Ph.D. students in multiple tables according
to years of experience. Right now the code I have only shows the last
group since it is the last call to the database in the code (there are
4 others before that get negated by the last one)

Here is the page's full code:
___________________________________________________________________________
<%
If Request("yr") = "" Then
Response.Redirect("default.asp")
else
Dim rst
Dim strSql
Dim gradYr

gradYr = Request("yr")


Sub Page_Title
Response.Write("Graduate Students")

End Sub

Dim wsType, pdf, url, semester, check, a, sem, isFirst
wsType = request("typ")
isFirst = 1




%>
<!--#include file = "dbConnection.asp"-->
<!--#include file = "header.asp"-->
<h4><span class="title" id="dir_title"><% Call Page_Title
%></span></h4>
<%

%>
<a href="graduate-beta.asp?yr=0">| All Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=1"> Year 1 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=2"> Year 2 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=3"> Year 3 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=4"> Year 4 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=5"> Year 5 and up Ph.D. Students</a> |
<br><br>

<img src="Pictures/1styear.gif" alt="1st Year Students"><br>

<%

' Execute a SQL query and store the results
' within recordset from object "dbConn."
' "dbConn" is created from the Database connection file
' ~Thiago.



gradyr="1"
strSql = "SELECT * From tblGrad WHERE Classification
LIKE 'p1' ORDER BY lastName ASC;"

%>
<br>
<img src="Pictures/2ndyear.gif" alt="2nd Year Students"><br>
<%
strSql = "SELECT * From tblGrad WHERE Classification LIKE 'p2'
ORDER BY lastName ASC;"

%>
<br>
<img src="Pictures/3rdyear.gif" alt="3rd Year Students"><br>
<%
strSql = "SELECT * From tblGrad WHERE Classification LIKE 'p3'
ORDER BY lastName ASC;"

%>
<br>
<img src="Pictures/4thyear.gif" alt="4th Year Students"><br>
<%
strSql = "SELECT * From tblGrad WHERE Classification LIKE 'p4'
ORDER BY lastName ASC;"


%>
<img src="Pictures/5thyear.gif" alt="5th Year
Students"><br>
<%
strSql = "SELECT * From tblGrad WHERE Classification NOT IN ('p1',
'p2', 'p3', 'p4', 'm1', 'm2', 'm3', 'm4') ORDER BY Classification,
lastName ASC;"


Set rst = Server.CreateObject("ADODB.Recordset")

rst.LockType= 2
rst.CursorType= 2
rst.Open strSql, dbConn

dbConn.Close

'----------------------------------------------------------------------------------------------------------------------



%>

<table border="0" class="lists" width="100%" cellpadding="1"
cellspacing="3" ID="Table1">
<tr>
<th>Name</th>
<th>Country</th>
<th>Office Phone</th>
<th>Email</th>
</tr>
<br>
<%
Dim bgCol
bgCol = "#FFFFFF"
Dim counter
counter = 0
Do while (Not rst.EOF)
If bgCol = "#F5F5F5" Then
bgCol = "#FFFFFF"
Else bgCol = "#F5F5F5"
End If
Response.Write("<tr bgColor=" & bgCol & ">")
Response.Write("<td>")

If rst("webURL") <> "" Then
Response.Write("<a href='" & rst("webURL") & "' target='_blank'>")
Else
Response.Write("&nbsp;")
End If
Response.Write(rst("lastName"))
Response.Write(",&nbsp;")
Response.Write(rst("firstName"))

If rst("webURL") <> "" Then
Response.Write("</a>")
End If
Response.Write("</td>")
%>
<td align="center"><% =rst("country") %></td>
<td align="center"><% =rst("officePhone") %></td>
<td align="center"><a href="mailto:<% =rst("email") %>"><%
=rst("email") %></a></td>

<%

Response.Write("<td>")
Response.Write("</td>")
%>

</tr>

<% counter = counter + 1
rst.moveNext
Loop

rst.close
Set rst = nothing
%>
</table>

<%

'----------------------------------------------------------------------------------------------------------------------

%>

<br>
<br>
<a href="graduate-beta.asp?yr=0">| All Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=1"> Year 1 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=2"> Year 2 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=3"> Year 3 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=4"> Year 4 Ph.D. Students</a> |
<a href="graduate-beta.asp?yr=5"> Year 5 and up Ph.D. Students</a> |





<% End If %>
<!--#include file = "footer.asp"-->

_________________________________________________________________________


I did not write this code; just trying to maintain it. Any help would
be appreciated

 

[BenM]

Looks like you are only opening your recordset once, using the last version
of 'strSql'. To get the data to open the way I think you want it, you would
open and close the recordset each time you change strSql.

Example:

strSql = "SELECT * From tblGrad WHERE Classification LIKE 'p1' ORDER BY
lastName ASC;"
rst.Open strSql, dbConn
CREATE FIRST TABLE HERE
rst.Close

strSql = "SELECT * From tblGrad WHERE Classification LIKE 'p2' ORDER BY
lastName ASC;"
rst.Open strSql, dbConn
CREATE SECOND TABLE HERE
rst.Close

etc....