ASP Question

[donald]

Hi all I am new to asp and had a question, I have a database driven site and
would like to have an option showing the last 10 records added to the
database, does anyone know if a) this is possible and b) how to do it?
Thanks alot.

 

[Bob Lehmann]

SELECT TOP 10 <fields> FROM <table_name> ORDER BY <field_name> DESC.

The first item in your fields list is the one the TOP clause will apply to.

Bob Lehmann

 

[donald]

Thanks so I would put a piece of aspcode that reads

<? SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC ?>

?

Thanks alot

 

[Roland Hall]

in message : Thanks so I would put a piece of aspcode that reads
:
: <? SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC ?>

? is not ASP. See PHP.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

 

[Bob Lehmann]

Well, yeah, if you are using ASP, and - I assume Access or SQL Server.

Except, since it looks like you are using PHP, and Lord knows what DB
(<shiver>MySQL</shiver> or PostGres), I can't say for sure.

What happened when you tried it?

Bob Lehmann

 

[donald]

No I am using ASP and MS Access, I am new to ASP as I said so when I typed
the email I made the mistake of using PHP code, Iam still learning after all


So can you please tell me the correct code to use?
Much appreciated.

 

[donald]

Did it again sorry not with it today, I had already tried
<%
Response.Write(SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC)
%>

but that just gives me a 500.100 error saying

Error Type:
Microsoft JScript compilation (0x800A03EE)
Expected ')'
/index.asp, line 21, column 22
Response.Write(SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC)

Thanks for any help much appreciated.

 

[Aaron Bertrand [MVP]]

<%
// you'll need to fix this line:
dbPath = Server.MapPath("/file.mdb")

Set conn = CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

sql = "SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC"
Set rs = conn.execute(sql)

do while not rs.eof
response.write rs(0) & "<br>"
rs.movenext
loop

rs.close: set rs = nothing
conn.close: set conn = nothing
%>

 

[Bob Lehmann]

Not sure what the Response Write is all about. If you want to write it out
to the browser it would be...
Response.Write("SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC")
Note the quotes.

The ordinary sequence is.....

Assign query to a variable
Create Connection object
Open connection
Execute SQL statement
Display results
Close result & set to Nothing
Close connection & set to Nothing

Aaron's example is a good implementation of this.

Bob Lehmann

 

[Bullschmidt]

Well perhaps this:
Response.Write("SELECT TOP 10 Titles FROM DVD ORDER BY Title DESC")

Should be changed to this instead (no plural in Title field):
Response.Write("SELECT TOP 10 Title FROM DVD ORDER BY Title DESC")

Or:
Response.Write("SELECT TOP 10 ID FROM DVD ORDER BY ID DESC")

But realize that after compacting a database that an autonumber field
can then use a previously deleted ID and thus you wouldn't really always
be showing the latest 10 records added.

Best regards,
J. Paul Schmidt, Freelance ASP Web Consultant
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

 

[Bob Lehmann]

What the heck are you talking about?

He had a Response.Write that was causing problems. He didn't ask about DB
Field naming conventions.

And you are somewhat wrong about compacting an Access DB with an autonumber
field. It won't re-use ids that had been deleted from the middle of the of
the pack, only ids greater than the largest existing id. So, the 10 largest
ids would be the 10 most recent entries.

Bob Lehmann

 

[donald]

Thanks everyone for the help, I am 99% there, I had it working only for it
to stop working 5 mins later

I will get it yet

Thanks again.