Back To Programming / Scripting / Coding Forum

LIMIT the Amount of Results Returned

  • anyusernamewilldo
  • Novice
  • Novice
  • No Avatar
  • Joined: Aug 05, 2009
  • Posts: 22
  • Status: Offline

Post August 5th, 2009, 4:43 pm

Hi All,

Im a bit of a noob and i'm hoping that what i'm trying to do is fairly common and Im totally stuck and really need some help...

I have an access database that i need to return some results from. At present I have an ASP Sript that Will return all the records in decending order by ID number... This is Perfect.
However i only Wish for it to Return the three most Recent Results.

Here is the code im Using to return ALL the results:

Code: [ Select ]
<%
' Declaring variables
Dim rs, data_source, no
no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("form.mdb")
 
' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM users ORDER BY id DESC ", data_source
 
 
' Looping through the records to show all of them
While Not rs.EOF
    Response.Write "<form action='del.asp' method='post'>"
    Response.Write "Name : " & rs("name") & "<br>"
    Response.Write "Email : " & rs("email") & "<br>"
    Response.Write "Country : " & rs("country") & "<br>"
    Response.Write "Comments : " & rs("comments") & "<br>"
    Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
    Response.Write "<input type='submit' value='Delete'>" & "<br>"
    Response.Write "</form>"
    no = no + 1
    rs.MoveNext
Wend
 
' Done. Now close the Recordset
rs.Close
Set rs = Nothing
 
Response.Write "<p>Total Records Found : " & no
%>
 
  1. <%
  2. ' Declaring variables
  3. Dim rs, data_source, no
  4. no = 0
  5. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  6.     Server.MapPath("form.mdb")
  7.  
  8. ' Creating Recordset Object and opening the database
  9. Set rs = Server.CreateObject("ADODB.Recordset")
  10. rs.Open "SELECT * FROM users ORDER BY id DESC ", data_source
  11.  
  12.  
  13. ' Looping through the records to show all of them
  14. While Not rs.EOF
  15.     Response.Write "<form action='del.asp' method='post'>"
  16.     Response.Write "Name : " & rs("name") & "<br>"
  17.     Response.Write "Email : " & rs("email") & "<br>"
  18.     Response.Write "Country : " & rs("country") & "<br>"
  19.     Response.Write "Comments : " & rs("comments") & "<br>"
  20.     Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
  21.     Response.Write "<input type='submit' value='Delete'>" & "<br>"
  22.     Response.Write "</form>"
  23.     no = no + 1
  24.     rs.MoveNext
  25. Wend
  26.  
  27. ' Done. Now close the Recordset
  28. rs.Close
  29. Set rs = Nothing
  30.  
  31. Response.Write "<p>Total Records Found : " & no
  32. %>
  33.  


As mentioned above - this script displays ALL the results in the Table. I only want the Newest 3 results to be returned.

I don't know what it is i need to change to only get the three most recent items from the access database. I've tried putting
Code: [ Select ]
"SELECT * FROM `users` LIMIT 0, 3 "
in various places in the script in addition to what I have above. Please can anyone advise what i need to change or where on earth i am to put the LIMIT Variable or anything.

Thanks to Any Help In Advance

Jy
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post August 5th, 2009, 4:43 pm

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post August 5th, 2009, 5:21 pm

Try it like this:

Code: [ Select ]
SELECT TOP 3 * FROM 'users' LIMIT 0,3
I'd love to change the world, but they won't give me the source code.
  • anyusernamewilldo
  • Novice
  • Novice
  • No Avatar
  • Joined: Aug 05, 2009
  • Posts: 22
  • Status: Offline

Post August 5th, 2009, 11:22 pm

Iv Tried Putting

Code: [ Select ]
SELECT TOP 3 * FROM 'users' LIMIT 0,3


Into the Script like so:

Code: [ Select ]
<%
' Declaring variables
Dim rs, data_source, no
no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("form.mdb")
 
' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT TOP 3 * FROM 'users' LIMIT 0,3", data_source 
 
 
' Looping through the records to show all of them
While Not rs.EOF
    Response.Write "<form action='del.asp' method='post'>"
    Response.Write "Name : " & rs("name") & "<br>"
    Response.Write "Email : " & rs("email") & "<br>"
    Response.Write "Country : " & rs("country") & "<br>"
    Response.Write "Comments : " & rs("comments") & "<br>"
    Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
    Response.Write "<input type='submit' value='Delete'>" & "<br>"
    Response.Write "</form>"
    no = no + 1
    rs.MoveNext
Wend
 
' Done. Now close the Recordset
rs.Close
Set rs = Nothing
 
Response.Write "<p>Total Records Found : " & no
%>
  1. <%
  2. ' Declaring variables
  3. Dim rs, data_source, no
  4. no = 0
  5. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  6.     Server.MapPath("form.mdb")
  7.  
  8. ' Creating Recordset Object and opening the database
  9. Set rs = Server.CreateObject("ADODB.Recordset")
  10. rs.Open "SELECT TOP 3 * FROM 'users' LIMIT 0,3", data_source 
  11.  
  12.  
  13. ' Looping through the records to show all of them
  14. While Not rs.EOF
  15.     Response.Write "<form action='del.asp' method='post'>"
  16.     Response.Write "Name : " & rs("name") & "<br>"
  17.     Response.Write "Email : " & rs("email") & "<br>"
  18.     Response.Write "Country : " & rs("country") & "<br>"
  19.     Response.Write "Comments : " & rs("comments") & "<br>"
  20.     Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
  21.     Response.Write "<input type='submit' value='Delete'>" & "<br>"
  22.     Response.Write "</form>"
  23.     no = no + 1
  24.     rs.MoveNext
  25. Wend
  26.  
  27. ' Done. Now close the Recordset
  28. rs.Close
  29. Set rs = Nothing
  30.  
  31. Response.Write "<p>Total Records Found : " & no
  32. %>


And I Receive this Error:

Microsoft JET Database Engine error '80040e14'
Syntax error in query. Incomplete query clause.
/forms/showall.asp, line 10

What do i need to change???

Thanks in Advance
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post August 6th, 2009, 4:35 am

Put it where the old query is:

Code: [ Select ]
rs.Open "SELECT TOP 3 * FROM 'users' LIMIT 0,3", data_source
I'd love to change the world, but they won't give me the source code.
  • anyusernamewilldo
  • Novice
  • Novice
  • No Avatar
  • Joined: Aug 05, 2009
  • Posts: 22
  • Status: Offline

Post August 6th, 2009, 9:46 am

No Joy I'm Afraid :( Inserting the code you suggested in place of the old query displays the following:

Microsoft JET Database Engine error '80040e14'
Syntax error in query. Incomplete query clause.
/forms/showall.asp, line 10

Heres the code:

Code: [ Select ]
<%
' Declaring variables
Dim rs, data_source, no
no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("form.mdb")
 
' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT TOP 3 * FROM 'users' LIMIT 0,3", data_source
 
 
' Looping through the records to show all of them
While Not rs.EOF
    Response.Write "<form action='del.asp' method='post'>"
    Response.Write "Name : " & rs("name") & "<br>"
    Response.Write "Email : " & rs("email") & "<br>"
    Response.Write "Country : " & rs("country") & "<br>"
    Response.Write "Comments : " & rs("comments") & "<br>"
    Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
    Response.Write "<input type='submit' value='Delete'>" & "<br>"
    Response.Write "</form>"
    no = no + 1
    rs.MoveNext
Wend
 
' Done. Now close the Recordset
rs.Close
Set rs = Nothing
 
Response.Write "<p>Total Records Found : " & no
%>
  1. <%
  2. ' Declaring variables
  3. Dim rs, data_source, no
  4. no = 0
  5. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  6.     Server.MapPath("form.mdb")
  7.  
  8. ' Creating Recordset Object and opening the database
  9. Set rs = Server.CreateObject("ADODB.Recordset")
  10. rs.Open "SELECT TOP 3 * FROM 'users' LIMIT 0,3", data_source
  11.  
  12.  
  13. ' Looping through the records to show all of them
  14. While Not rs.EOF
  15.     Response.Write "<form action='del.asp' method='post'>"
  16.     Response.Write "Name : " & rs("name") & "<br>"
  17.     Response.Write "Email : " & rs("email") & "<br>"
  18.     Response.Write "Country : " & rs("country") & "<br>"
  19.     Response.Write "Comments : " & rs("comments") & "<br>"
  20.     Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
  21.     Response.Write "<input type='submit' value='Delete'>" & "<br>"
  22.     Response.Write "</form>"
  23.     no = no + 1
  24.     rs.MoveNext
  25. Wend
  26.  
  27. ' Done. Now close the Recordset
  28. rs.Close
  29. Set rs = Nothing
  30.  
  31. Response.Write "<p>Total Records Found : " & no
  32. %>
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post August 6th, 2009, 10:18 am

Doh, I missed the LIMIT...let's drop that off and make it:

Code: [ Select ]
rs.Open "SELECT TOP 3 * FROM 'users'", data_source
I'd love to change the world, but they won't give me the source code.
  • anyusernamewilldo
  • Novice
  • Novice
  • No Avatar
  • Joined: Aug 05, 2009
  • Posts: 22
  • Status: Offline

Post August 6th, 2009, 10:28 am

Still a no go.

Receiving Error:

Microsoft JET Database Engine error '80040e14'
Syntax error in query. Incomplete query clause.
/forms/showall.asp, line 10

Heres the Code:

Code: [ Select ]
<%
' Declaring variables
Dim rs, data_source, no
no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("form.mdb")

' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT TOP 3 * FROM 'users' ", data_source


' Looping through the records to show all of them
While Not rs.EOF
    Response.Write "<form action='del.asp' method='post'>"
    Response.Write "Name : " & rs("name") & "<br>"
    Response.Write "Email : " & rs("email") & "<br>"
    Response.Write "Country : " & rs("country") & "<br>"
    Response.Write "Comments : " & rs("comments") & "<br>"
    Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
    Response.Write "<input type='submit' value='Delete'>" & "<br>"
    Response.Write "</form>"
    no = no + 1
    rs.MoveNext
Wend

' Done. Now close the Recordset
rs.Close
Set rs = Nothing

Response.Write "<p>Total Records Found : " & no
%>
  1. <%
  2. ' Declaring variables
  3. Dim rs, data_source, no
  4. no = 0
  5. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  6.     Server.MapPath("form.mdb")
  8. ' Creating Recordset Object and opening the database
  9. Set rs = Server.CreateObject("ADODB.Recordset")
  10. rs.Open "SELECT TOP 3 * FROM 'users' ", data_source
  13. ' Looping through the records to show all of them
  14. While Not rs.EOF
  15.     Response.Write "<form action='del.asp' method='post'>"
  16.     Response.Write "Name : " & rs("name") & "<br>"
  17.     Response.Write "Email : " & rs("email") & "<br>"
  18.     Response.Write "Country : " & rs("country") & "<br>"
  19.     Response.Write "Comments : " & rs("comments") & "<br>"
  20.     Response.Write "<input type='hidden' name='id' value='" & rs("id") & "'>"
  21.     Response.Write "<input type='submit' value='Delete'>" & "<br>"
  22.     Response.Write "</form>"
  23.     no = no + 1
  24.     rs.MoveNext
  25. Wend
  27. ' Done. Now close the Recordset
  28. rs.Close
  29. Set rs = Nothing
  31. Response.Write "<p>Total Records Found : " & no
  32. %>
  • anyusernamewilldo
  • Novice
  • Novice
  • No Avatar
  • Joined: Aug 05, 2009
  • Posts: 22
  • Status: Offline

Post August 6th, 2009, 10:51 am

SOLVED IT!!!

UPSGuy Suggested:

Code: [ Select ]
rs.Open "SELECT TOP 3 * FROM 'users'", data_source



Tweaked Version: Working! (displays the 3 newest results)

Code: [ Select ]
rs.Open "SELECT TOP 3 * FROM users ORDER BY id DESC ", data_source



I'm so happy it's working Now that it's made my day! :D
I couldn't have done it without your help UPSGuy. Thanks for all of your very fast responses. You're a credit to the forum.

Thank you
amazing first impressions of this forum.

Regards

Jy
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post August 6th, 2009, 2:53 pm

Thanks - I'm glad that got it for you. :D
I'd love to change the world, but they won't give me the source code.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Joined: May 21, 2004
  • Posts: 3229
  • Loc: South Africa
  • Status: Offline

Post August 7th, 2009, 8:21 am

So it was the quotes causing the error because there is no intrinsic difference between the queries. (Asides for the limit, I saw top and I saw limit and thought to myself "Killing two birds with one stone" LOL)

Just to define:
TOP is sql server
LIMIT is mysql
Watch me grow
  • anyusernamewilldo
  • Novice
  • Novice
  • No Avatar
  • Joined: Aug 05, 2009
  • Posts: 22
  • Status: Offline

Post August 7th, 2009, 8:45 am

Ta Muchly for the Info Rabid Dog...

you guys have been big help, I've only ever really worked with html & php and have only ever fiddled around with some flash and mySql.

Two weeks ago I decided sink my teeth into something new and attempt to get my head around
.asp, mysql / sql.

Anyhow, Things have gone well and until now "this post" This was my first issue that had me at truly stuck for a day...
I'm sure there will be many more hurdles for me to deal with yet but so far so good.

Cheers again guys
No Doubt I'll be Back.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Joined: May 21, 2004
  • Posts: 3229
  • Loc: South Africa
  • Status: Offline

Post August 7th, 2009, 9:20 am

Looking forward to your posts :) Might I recommend looking at SubSonic or nHibernate for your data access requirements? Oh and don't worry about asp, rather look at asp.net. IIS vaguely supports asp now a days
Watch me grow
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post August 7th, 2009, 10:50 am

SubSonic or nHibernate on top of an Access db? :S
I'd love to change the world, but they won't give me the source code.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Joined: May 21, 2004
  • Posts: 3229
  • Loc: South Africa
  • Status: Offline

Post August 7th, 2009, 10:59 am

LOL I am pretty certain you can do it, after all it is just the driver that changes
Watch me grow

Page 1 of 1

Post Information

  • Total Posts in this topic: 14 posts
  • Users browsing this forum: No registered users and 100 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.