SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL SUM() does not return recordset

    Hi All - I'm doing the following in an ASP page:

    **********************
    Dim recTmp
    Set db = Server.CreateObject ("ADODB.Connection")
    db.Open ("DSN=MYDB")
    Set recTmp = Server.CreateObject ("ADODB.Recordset")
    recTmp.Open "SELECT username, SUM(distance) as TotalDistance FROM routes GROUP BY username ORDER BY TotalDistance ASC limit 0,5", db
    if recTmp.EOF and recTmp.BOF then
    Response.Write "Got Nothing"
    else
    Response.Write "got some"
    End If

    *********************

    I don't receive an error, but, no matter what, I get no records returned!!!! The SQL is correct because when I paste the SQL and run it directly in my DB I get four records returned:

    username TotalDistance
    bill 1454
    ed 1896
    phil 11642
    joe 35151

    I'm using MYSQL as the backend db. Any ideas? And thanks again!

  2. #2
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    looks fine, are you sure the database is being opened correctly?

    Did you try a simpler query?

  3. #3
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jimfraser View Post
    looks fine, are you sure the database is being opened correctly?

    Did you try a simpler query?
    yep, I tried simpler queries and it works fine. This one is driving me crazy.

  4. #4
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    does it work if you get rid of the limit clause?

  5. #5
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope - it doesn't work. As soon as I take out the SUM() function it works (just doesnt give me the results I want). Aaaarrrggg.... weird.


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
  •