SQL question....

Discussion in 'ASP General' started by fuelvolts@gmail.com, Mar 28, 2006.

  1. Guest

    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
     
    , Mar 28, 2006
    #1
    1. Advertising

  2. BenM Guest

    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....
     
    BenM, Mar 29, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dna
    Replies:
    1
    Views:
    1,286
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    456
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    464
    David Browne
    Aug 21, 2003
  4. ecoolone
    Replies:
    0
    Views:
    775
    ecoolone
    Jan 3, 2008
  5. Jules
    Replies:
    6
    Views:
    155
    Jules
    Jul 15, 2003
Loading...

Share This Page