Code to retrieve only the latest record from db

[adderfangs]

Hi guys,
I am slowly learning to use asp.
I am creating a page for a website I take care of and I need to find a way to publish only the latest record (news) from the database. The code below is for the news page on the same website. The code gets all the news records from the db in descending order. Would appreciate your help! Thanks in advance

<%
'declare your variables
Dim connection, rs
Dim sSQL, sConnString

'declare SQL statement that will query the database
sSQL="SELECT * FROM news_tbl ORDER BY newsdate DESC"

'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("..\db\news.mdb")

'Open the connection to the database
Connection.Open sConnString

'Open the recordset object, executing the SQL
Rs.Open sSQL, Connection

'Looping through the records until the end of the records
Do while Not rs.eof
%>
<tr>
<td width="25%" align="center"><br /><a href="http://portal.unesco.org/en/ev.php-URL_ID=<%=rs("newsnumber")%>&URL_DO=DO_TOPIC&URL_SECTION=201.html" target="_blank"><img src="images/news08/<%=rs("imagename")%>" alt="News of the <%=rs("newsdate")%>"></a></td>
<td width="60%"><br /><b><%=rs("newsdate")%></b><br> <%=rs("headline")%> <p class="newsheadline"><%=rs("fullnews")%> - <a href="http://portal.unesco.org/en/ev.php-URL_ID=<%=rs("newsnumber")%>&URL_DO=DO_TOPIC&URL_SECTION=201.html" class="linksinpage" target=blank>More</a></td>
<td width="15%" align="center" valign="bottom" ><%=rs("commentstart")%><a name="<%=rs("monthlink")%>" class="linksinpage"><img src="images/months/<%=rs("monthimg")%>"></a><p>&nbsp;</p><br><a href="#top" align="right"><img src="images/months/btop.jpg" width="80"></a><%=rs("commentend")%></td>
</tr>
<tr>
<td><hr></td>
<td><hr></td>
<td><hr></td>
</tr>
<%
'move on to the next record
rs.MoveNext
loop

'Now close the recordset and the connection object
rs.Close
Set rs = Nothing
connection.Close
Set connection = Nothing
%>

[yamaharuss]

If you are only retreiving the latest record, there is no need to loop through a recordset.


<%
'declare your variables
Dim connection, rs
Dim sSQL, sConnString

'declare SQL statement that will query the database
sSQL="SELECT TOP 1 * FROM news_tbl ORDER BY newsdate DESC"

'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("..\db\news.mdb")

'Open the connection to the database
Connection.Open sConnString

'Open the recordset object, executing the SQL
Rs.Open sSQL, Connection

If Not rs.eof then
%>
<tr>
<td width="25%" align="center"><br /><a href="http://portal.unesco.org/en/ev.php-URL_ID=<%=rs("newsnumber")%>&URL_DO=DO_TOPIC&URL_SECTION=201.html" target="_blank"><img src="images/news08/<%=rs("imagename")%>" alt="News of the <%=rs("newsdate")%>"></a></td>
<td width="60%"><br /><b><%=rs("newsdate")%></b><br> <%=rs("headline")%> <p class="newsheadline"><%=rs("fullnews")%> - <a href="http://portal.unesco.org/en/ev.php-URL_ID=<%=rs("newsnumber")%>&URL_DO=DO_TOPIC&URL_SECTION=201.html" class="linksinpage" target=blank>More</a></td>
<td width="15%" align="center" valign="bottom" ><%=rs("commentstart")%><a name="<%=rs("monthlink")%>" class="linksinpage"><img src="images/months/<%=rs("monthimg")%>"></a><p>&nbsp;</p><br><a href="#top" align="right"><img src="images/months/btop.jpg" width="80"></a><%=rs("commentend")%></td>
</tr>
<tr>
<td><hr></td>
<td><hr></td>
<td><hr></td>
</tr>
<%else%>
Sorry, no records exist.
<%end if%>

'Now close the recordset and the connection object
rs.Close
Set rs = Nothing
connection.Close
Set connection = Nothing
%>

[adderfangs]

Thanks mate. I will test the code and will get back! Appreciate your help!!

[adderfangs]

Hey yamaharuss
The code worked beautifully! Thanks for your help once again!!