Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to display top 5 data

    if i connect to the database and display some data like so:

    Code:
    <%
    
    ' Set Up our Database connection via DSNLess Connection
    ' You May be able to use Server.MapPath for you connection
    ' Or alternatively connect via ODBC, Please contact your
    ' Hosting provider for more information
    
    ' Dimension our Variables for the Database Connection
    
    dim objConn, strCon, objRS, strSQL
    Set objConn = Server.CreateObject("ADODB.Connection")
    
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\business\network.mdb"
    
    objConn.connectionstring = strCon
    objConn.Open
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ON ERROR RESUME NEXT
    
    Dim pageView, strNewsText
    ' See what view the User is requesting
    pageView=Request.Querystring("view")
    %>
    
    
    <% dim ordervariable, sortorder
    ordervariable = "Category"   'default column to sort on
    sortorder = "ASC"   'default sortorder
    
    if (len(request.querystring("sortby")) >= 1) then 
         ordervariable = request.querystring("sortby")
    end if
    if (len(request.querystring("sortorder")) >= 1) then 
    sortorder = request.querystring("sortorder")
    end if %>
    
    <!-----THIS IS THE MAIN LIST--->
    
    <%if pageView="" THEN
    
    ' We display the news article list
    
    ' Set the SQL Statement to get the information from the database
    
    strSQL="SELECT * FROM CATEGORY WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder
    
    ' Open the Database
    objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
    IF NOT objRS.EOF THEN
    'objRS.Filter=objRS("Status")=1
    %>
    how can i then show the 'top 5 results' from the data.

    Much like a 'headlines' function in a news script - a little area shows the last 5 entries into the database etc..

    i know you can use
    Code:
    strSQL="SELECT TOP 5 * FROM CATEGORY WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder
    but im not sure how you can implement it into a page..

    help please! thanks
    Web and Business Development from Cornwall - Startbar.co.uk

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    do you only want to display the top 5 results? if so then use the 2nd query
    you have but with these small changes.
    strSQL="SELECT TOP 5 * FROM CATEGORY WHERE Status=1 ORDER BY " & ordervariable & sortorder

    If your question is in how to display the results onto a page then do so like you would with any results derived from a database. ie response.write objRS("widget")
    Last edited by miranda; 08-04-2004 at 06:16 PM.

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    but i want to show both at the same time on the same page!!

    i need a webpage with lets say news on it or something then in another section of that page like a summary of the news (like headlines)

    i dont know how do make the headlines becuase when i try and just do the same as the main news it doesnt work!!
    Web and Business Development from Cornwall - Startbar.co.uk

  • #4
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    look at my code..


    Code:
    <%@ Language=VBScript %> 
    <%
    
    %>
    <!--#include virtual="adovbs.inc"--> 
    <%
    
    ' Set Up our Database connection via DSNLess Connection
    ' You May be able to use Server.MapPath for you connection
    ' Or alternatively connect via ODBC, Please contact your
    ' Hosting provider for more information
    
    ' Dimension our Variables for the Database Connection
    
    dim objConn, strCon, objRS, strSQL
    Set objConn = Server.CreateObject("ADODB.Connection")
    
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=news.mdb"
    
    objConn.connectionstring = strCon
    objConn.Open
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ON ERROR RESUME NEXT
    
    Dim pageView, strNewsText
    ' See what view the User is requesting
    pageView=Request.Querystring("view")
    %>
    
    
    <html>
    <head>
    
    
    <link rel="stylesheet" href="text.css" type="text/css">
    
    
    </head>
    
    <body bgcolor="#FFFFFF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
    <table width="770" border="0" cellspacing="0" cellpadding="0">
      
      
      <tr> 
        <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr> 
              <td width="206" valign="top" bgcolor="#A50A01"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr> 
                    <td><img src="images/logo.gif"></td>
                  </tr>
                  
                  <tr> 
                    <td valign="top"><BR><a href="#"><img src="images/link1.gif" width="206" height="26" border="0"></a></td>
                  </tr>
                  <tr> 
                    <td valign="top"><a href="#"><img src="images/link2.gif" width="206" height="26" border="0"></a></td>
                  </tr>
                  <tr> 
                    <td valign="top"><a href="#"><img src="images/link3.gif" width="206" height="26" border="0"></a></td>
                  </tr>
        <tr> 
                    <td valign="top"><a href="#"><img src="images/link7.gif" width="206" height="26" border="0"></a></td>
                  </tr>
                  <tr> 
                    <td valign="top"><a href="#"><img src="images/link4.gif" width="206" height="26" border="0"></a></td>
                  </tr>
      <tr> 
                    <td valign="top"><a href="#"><img src="images/link8.gif" width="206" height="26" border="0"></a></td>
                  </tr>
      <tr> 
                    <td valign="top"><a href="#"><img src="images/link9.gif" width="206" height="26" border="0"></a></td>
                  </tr>
                  <tr> 
                    <td valign="top"><a href="#"><img src="images/link5.gif" width="206" height="26" border="0"></a></td>
                  </tr>
    <tr> 
                    <td valign="top"><a href="#"><img src="images/link6.gif" width="206" height="26" border="0"></a></td>
                  </tr>
    
                  <tr> 
                    <td valign="top">&nbsp;</td>
                  </tr>
                  <tr> 
                    <td valign="top"><table width="91%" border="0" align="right" cellpadding="0" cellspacing="0" bgcolor="#A50A01">
                        <tr> 
                          <td align="right"><font color="#ffffff"></td>
                        </tr>
                      </table></td>
                  </tr>
                </table></td>
              <td width="1" valign="top" background="images/dotted_bg.gif"><img src="images/dotted.gif" width="1" height="708"></td>
              <td valign="top" bgcolor="DADADA"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr> 
                    <td><img src="images/pix003.gif"></td>
                  </tr>
                  <tr> 
                    <td>
                      <table width="100%" border="0" cellspacing="0" cellpadding="2" background="images/bg.gif" height="1" class="text">
                        <tr> 
                          <td width="520"><B><font class="welcome">&nbsp;Welcome to the uk's number one south asian cultural event</b></td>
                          
                          <td width="320" height="17" align="right"> <font class="welcome">
                            <script language="JavaScript1.2">
    
    <!-- Begin
    var months=new Array(13);
    months[1]="January";
    months[2]="February";
    months[3]="March";
    months[4]="April";
    months[5]="May";
    months[6]="June";
    months[7]="July";
    months[8]="August";
    months[9]="September";
    months[10]="October";
    months[11]="November";
    months[12]="December";
    var time=new Date();
    var lmonth=months[time.getMonth() + 1];
    var date=time.getDate();
    var year=time.getYear();
    if (year < 2000)    // Y2K Fix, Isaac Powell
    year = year + 1900; // http://onyx.idbsu.edu/~ipowell
    document.write("<right>" + lmonth + " ");
    document.write(date + ", " + year + "</right>");
    // End -->
    </script>
                            &nbsp;&nbsp; </td>
                        </tr>
                      </table></td>
                  </tr>
    
    <tr> 
                    <td><table width="100%" border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFFFF" valign="top">
                        <tr> 
                          <td width="293" valign="top"><table width="95%" border="0" align="center" cellpadding="3" cellspacing="0" valign="top">
                            
     <tr valign="top"><td valign="top">
    
    
    <%if pageView="" THEN
    
    ' We display the news article list
    
    ' Set the SQL Statement to get the information from the database
    
    dim ordervariable, sortorder
    ordervariable = "Date"   'default column to sort on
    sortorder = "DESC"   'default sortorder
    
    if (len(request.querystring("sortby")) >= 1) then 
         ordervariable = request.querystring("sortby")
    end if
    if (len(request.querystring("sortorder")) >= 1) then 
    sortorder = request.querystring("sortorder")
    end if
    
    strSQL="SELECT TOP 5 * FROM news WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder
    
    ' Open the Database
    objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
    IF NOT objRS.EOF THEN
    'objRS.Filter=objRS("Status")=1
    %>
    
    
    <title>asiashow.co.uk - latest news</title>
    
    <img src="images/bullet.gif"> <font class="titles">LATEST NEWS</font><BR><img src="images/line.gif"><BR>
    
    
    <%
    ' Write out the list of articles but it will
    ' only show the ones that have a status of
    ' 1 which shows they are online
    DO WHILE NOT objRS.EOF%>
    
    
    <font class="newsheading"><%=objRS("Title")%></font><BR><font class="newstext">written on <%=objRS("Date")%> by <%=objRS("user")%><br><BR><font class="newstext"><%=objRS("body")%><BR><img src="images/line.gif"><BR>
    
    <%
    objRS.MoveNext
    Loop
    objRS.Close
    %>
    
    
    <BR><BR>
    
    </td>
                              </tr>
                            </table></td>
                          <td width="1" background="images/shortdot.gif"></td>
                          <td valign="top"><table width="95%" border="0" align="center" cellpadding="3" cellspacing="0">
                              <tr> 
                                <td height="25" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                                    <tr> 
                                      <td width="250">
    
    
    
    <img src="images/bullet.gif"> <font class="titles">LATEST NEWS</font><BR>
    <%
    strSQL="SELECT TOP 2 * FROM news WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder
    %>
    
    
    <font class="newsheading"><%=objRS("Title")%></font><BR><font class="newstext">written on <%=objRS("Date")%> by <%=objRS("user")%><br><BR><font class="newstext"><%=objRS("short")%><BR><img src="images/line.gif"><BR>
    
    <%
    objRS.MoveNext
    Loop
    objRS.Close
    %>
    
    <BR><BR>
    
    
    <%ELSE%>
    <p>No Courses Available
    
    <p></p>
    <%END IF%>
    <%END IF%>
    Web and Business Development from Cornwall - Startbar.co.uk

  • #5
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Try this. Since you dont have any error handling in place, comment out the line that says ON ERROR RESUME NEXT. now run the code. you should get an error pointing to this line.
    Code:
    <font class="newsheading"><%=objRS("Title")%></font><BR><font class="newstext">written on <%=objRS("Date")%> by <%=objRS("user")%><br><BR><font class="newstext"><%=objRS("short")%><BR><img src="images/line.gif"><BR>
    this is because you have not opened the recordset for the 2nd sql statement! So after the 2nd sql statement you need to add
    objRS.Open strSQL, objConn

    Now I have a question do you close your database connection? do you ever release your objects from memory? if the answer is no to these two then add the following at the end of the page
    Set objRs = Nothing
    objConn.Close
    Set objConn = Nothing
    also now if you wanted to display every record you need to change the sql statement to reflect such. Right now you are saying to only display first the top 5 then the top 2


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •