SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with max rs

    Hi there nice people, sorry for butting in like this but I have been around SitePoint and I think if anyone can snwer this, you can

    Here is my problem...
    I want to show the five last posts ordered by date.
    I have tried Top * which only gives me them who is highest in the database (not the latest) and I have tried rs.maxrecords 5 which doesnīt work at all for me.

    What am I doing wrong??

    I C7P the code below for easier getting to know where I have gone wrong...

    Thanks!!

    <%
    set con=Server.CreateObject("adodb.connection")
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath("MyData.mdb")
    set rs= server.CreateObject("adodb.recordset")
    rs.maxrecords = 5 //doesnt work
    rs.cursortype = adOpenStatic
    'sql="select top 5 * from X where Y = 'YES'" //Doesnt work
    sql="select * from X where Y= 'YES'"
    sql = sql & " order by DateOfAp" //ok, ok, ok
    rs.Open sql,con,1,2
    rs.movelast
    do while not rs.bof
    %>

    <p class="minitext" align="left">
    <b><%Response.Write rs.Fields(1)%><b>:
    <a href="<%Response.Write rs.Fields(5)%>" title="<%Response.Write rs.Fields(10)%>" target="_blank"><%Response.Write rs.Fields(2)%></a> ŧ
    </p>

    <%
    rs.moveprevious
    loop
    con.Close
    %>
    Last edited by AljapaCo; Mar 21, 2002 at 13:18.
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In SQL, "Date" is a reserved word. You shouldn't really use a field name "Date". However, if you have, you need to enclose it in square brackets in your query.

    So, try changing the relevant part of your code there to:

    sql="select * from X where Y= 'YES'"
    sql = sql & " order by [Date]"


    Hope that helps .
    Nick Wilson [ - email - ]

  3. #3
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, Date is of course not used.

    I use an other name that is not important here. Just a name that isnīt already taken!

    I just didnīt wanna write all names to make it easier for a hacker to crash my site, if you know what I mean

    So, this shouldnīt be the problem.

    To make it more visual, here is the page Iīm talking about... almost at the bottom in the right TD!

    http://www.aljapaco.com/award/nyawar...&meny=theaward

    Thanks anyway!!
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............

  4. #4
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, ok... is there really no one who knows??

    I thought this was an ASP forum....

    I guess Iīll have to dig into some books
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Try Order by DateofAp DESC. Make sure that your datefield is defined as a date and not a string. That would affect your sorting.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  6. #6
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Genius!!

    Thanks Dave!

    Actually forgot about that one!

    It worked but now there is a new problem...

    Even though I told the code to start from the bottom and go back, the output starts at the oldest of the five and then goes down to the newest...

    How could this be fixed??
    Last edited by AljapaCo; Mar 22, 2002 at 20:43.
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............

  7. #7
    SitePoint Enthusiast PeteWJ's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After using the ORDER BY DESC approach (as Dave quite rightly suggests), the most recent items will be at the top of the recordset, therefore I would approach it like this:[VBS]sql="Select Top 5 * From X Where Y= 'YES'"
    sql = sql & " Order By DateOfAp DESC"
    rs.Open sql,con,1,2
    Do Until rs.EOF
    ' display links data
    Loop[/VBS]This should work without any problems - although I haven't tested it myself - it should provide the newest five entries (or less) from the database with the newest one first.

    Pete

  8. #8
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up one more genius

    Well, this thinking just throw me backwards

    After a closer examination of the code, I see that you are soo correct!!

    Of course one canīt chose .bof when ordered by DESC...

    Thanks guys! You made my day!
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............

  9. #9
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb yet another way

    While working with this recordset I found a new way to do it... some less code...

    <%
    set con = Server.CreateObject("ADODB.Connection")
    con.Open Application("News")
    sql = "select * from News Order by Id Desc"
    set rs = con.Execute(sql)
    count2 = 5
    %>

    <----body----->

    <% for I = 1 To Count2%>
    <p class="minitext">
    <%= rs("headnews")%><br /><a href="xtern2.asp#<%=rs("ID")%>" target="main">Read more &raquo;</a>
    </p>
    <%
    rs.MoveNext
    Next
    %>

    <%rs.Close
    con.Close
    %>

    Just in case anyone could have use of it
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............

  10. #10
    SitePoint Enthusiast PeteWJ's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is a perfectly legitimate method to do this but a warning to anyone who might be tempted to use it: You will need to check the EOF value of the recordset on each iteration, just in case there are less than five items in the database - otherwise you could end up with a "No current record" error mid way through the loop.

    Pete

  11. #11
    long gone but not forgotten AljapaCo's Avatar
    Join Date
    Aug 2001
    Location
    Sweden
    Posts
    548
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry...

    Forgot that one...

    as I know there are more than 5 records in my table, I excluded that code to make it shorter...

    This one was made to print and link the headers of news that is added on a daily basis!

    Pete is absolutely correct!!
    T O B I A S - S T R A N D H | visit my site here
    OPERA7 /MSIE6 /FireFox 1.1/Win2000 /ADSL /17" Screen /1024x768
    god doesn't create genius, he clones me.............


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •