...

View Full Version : how to display top 5 data



startbar
08-04-2004, 04:25 PM
if i connect to the database and display some data like so:


<%

' 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
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

miranda
08-04-2004, 05:23 PM
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")

startbar
08-07-2004, 07:54 AM
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!!

startbar
08-07-2004, 07:58 AM
look at my 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%>

miranda
08-08-2004, 06:58 AM
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.
<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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum