Read last few entries in DB

[Ragol1]

Could someone please show me how to read lets say the last 5 entries that were entered into a database then display them on a page.
It doesnt matter if its DNS or DNSless, I have a Access 2002 database and I want to welcome new members on a page in my site with a box showing the last 5 entries in the DB.

Help Please


Thanks

 

[Gorkem]

If you have this information stored with dates, just send a query to the database to sort by date..

sqlCmd = "SELECT TOP 5 * FROM Users ORDER BY JoinDate DESC"

if there is no date.. you must have some sort of ID in place... then just sort by that...

sqlCmd = "SELECT TOP 5 * FROM Users ORDER BY UserID DESC"

Hope this helps,

Gorkem.

 

[JuanitaC]

Using SQL, you can get the last few records in your database this way:

Dim SQL, rs
SQL = "SELECT Top 5 * from table Order by DateEntered DESC"
Set rs = connection.execute(SQL)

if not rs.EOF then
While not rs.EOF
Response.write (rs.fields(&quot;Column_Name&quot & &quot;<br>&quot
rs.MoveNext
WEND
end if


If you don't have a dateEntered field, find another field that will be incremented as the records are inserted, maybe ID, depending on the type of your ID.

 

[Ragol1]

I use this Im getting an Error is it something to do with sqltext?

<%
dim cnn,rst
set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
set rst = Server.CreateObject(&quot;ADODB.RecordSet&quot
cnn.Open &quot;driver={Microsoft Access Driver (*.mdb)};;DBQ=F:/Membersragol.mdb;&quot;
sqltext = &quot;SELECT * FROM Membersragol&quot;
rst.Open sqltext,cnn,3,3



%>

<% Dim SQL, rs
SQL = &quot;SELECT Top 5 * from Membersragol Order by ID DESC&quot;
Set rs = connection.execute(SQL)
if not rs.EOF then While not rs.EOF Response.write (rs.fields(&quot;UserID&quot&
&quot;<BR>&quot rs.MoveNext WEND end if%>

 

[JuanitaC]

What error are you getting?

 

[Ragol1]

Microsoft VBScript compilation error '800a03ea'

Syntax error

/Default.asp, line 114

if not rs.EOF then While not rs.EOF Response.write (rs.fields(&quot;UserID&quot&
------------------------------------------------------------------------^

I remove the & and it still errors

 

[JuanitaC]

Try formatting the whole section with line breaks and see if that helps:

if not rs.EOF then
While not rs.EOF
Response.write (rs.fields(&quot;UserID&quot & &quot;<br>&quot
rs.MoveNext
WEND
end if

 

[Gorkem]

Ok.. first of all you need to lose the two instances of the code getting the same information.. )

Your code should look like this:

<%
dim cnn,rst
set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cnn.Open &quot;driver={Microsoft Access Driver (*.mdb)};DBQ=F:/Membersragol.mdb;&quot;

SQL = &quot;SELECT Top 5 * from Membersragol Order by ID DESC&quot;
cmd.commandtext = SQL
Set rs = cmd.execute
While rs.EOF = false
Response.write (rs.fields(&quot;UserID&quot& &quot;<BR>&quot
rs.MoveNext
WEND
set rs = nothing
set cmd = nothing
set cnn = nothing
' always remember to set your objects to NOTHING so there
' won't be any memory leaks that IIS is famous for!
%>



Hope this helps,

Gorkem.

 

[Ragol1]

ADODB.Command error '800a0e7d'

Operation is not allowed on an object referencing a closed or invalid connection.

/Default.asp, line 112


This is 112
Set rs = cmd.execute

 

[JuanitaC]

Use

Set rs = cnn.Execute(SQL)

Then you won't need a command object at all:

dim cnn,rst
set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
cnn.Open &quot;driver={Microsoft Access Driver (*.mdb)};DBQ=F:/Membersragol.mdb;&quot;

SQL = &quot;SELECT Top 5 * from Membersragol Order by ID DESC&quot;
Set rs = cnn.execute(SQL)
While rs.EOF = false
Response.write (rs.fields(&quot;UserID&quot& &quot;<BR>&quot
rs.MoveNext
WEND
rs.Close()
set rs = nothing
cnn.Close()
set cnn = nothing
%>

 

[Ragol1]

OK works great,Thanks.. just one other question can I display these in a small table? OH another question I do have a date joined field in the DB how would I display the date joined next to each name...

I know I know thats two questions.....

Thanks

 

[JuanitaC]

There are a couple of ways to put your results into a table:

Number one, continue with Response.write:
Response.write(&quot;<table>&quot
While rs.EOF = false
Response.write (&quot;<tr><td> &quot; & rs.fields(&quot;UserID&quot& &quot;</td>&quot
Response.write(&quot;<td>&quot; & rs.fields(&quot;DateField&quot & &quot;</td></tr>&quot
rs.MoveNext
WEND
Response.write(&quot;</table>&quot


Number two, in and out of ASP (easier to read):

%>
<table>
<%
While rs.EOF = false
%>
<tr>
<td><%=rs.fields(&quot;UserID&quot%></td>
<td><%=rs.fields(&quot;DateField&quot%></td>
</tr>
<%
rs.MoveNext
WEND
%>
</table>