Hi All - thanks for any help you can give me with this. I'm at my minds end at this point. 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!!!! 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!

I may be way off but I don't think your recordset can be both at the end and at the beginning at the same time. So, it won't write anything to the page.

If recTemp.EOF Then
Response.Write("Got Nothing")
Else
Response.Write("Got Some")
End If

I'm new, but it seems like you should set "recTmp.Open" = to the database properties/ username / password. And then run your SELECT against recTmp.Execute <SQL Code>.

As an alternative connection approach, the code I found that works for me on a SQL Server 2000 database is:
set conn=server.createobject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")
conn.open = "Driver={SQL Server}; server=<Server_Name>; database=<database_name>; uid=<User_Name>; password=<password>;"
sqlrs="EXEC <Database_Name>.<Owner>.<Stored_Procedure> @param1 = '" _
& param & "'"
rs.open sqlrs, conn, 2, 2

And then I reference my SQL result set like rs("Column_Name").

Please note the underscore in the sample above is just because the code spanned multiple lines.

Hope you get some help from this post. Good Luck!

Hi danastasio and rstelma -

Thanks for the replies.

The rectmp.EOF and recTmp.BOF check is to see if I got any record sets, if no recordsets are returned than they are both true.

Danastsio -

I'm using a DSN connection so all of the passwords, and such are in the DSN record. I'll have to try writing the connection out to see if something changes.

If I try simpler queries evertyhing works fine, it is just this one query (in particular when I use SUM())

Thanks. I did not know what about DSN connections, but thanks to your post have learned a bit more today.
Have you tried response.write recTmp("Username") to verify no records are being returned?
Having a query work in the database and other queries without the SUM work in the ASP code would certainly drive me nuts. Good Luck!

It seems like if your connection wasn't working you'd be getting some kind of error.
What is your database?

Try this.

Dim recTmp
Set db = Server.CreatObject("ADODB.Connection")
db.Open(""DSN=MYDB;")
SQL = "SELECT username, SUM(distance) as TotalDistance FROM routes GROUP BY username ORDER BY TotalDistance ASC limit 0,5;"
Set recTmp = db.Execute(SQL)

If not recTmp.EOF Then
Do While not recTmp.EOF
Response.Write username & " " & TotalDistance & "<br>"
recTmp.MoveNext
Loop
Else
Response.Write "No Records Found."
End If

recTmp.Close
Set recTmp = Nothing
db.Close
Set db = Nothing

Are you sure you're not getting an error? Take a look at the resulting HTML in the browser.

Quite often in ASP sites, the error does occur but is not displayed on the page because it's written inside some <table> tags in the markup. By looking at the final HTML, you may see an error that otherwise isn't visible in the page.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Hi all - thanks again for the replies. Unfortunately, no luck. The database is MYSQL 5+

 - Yep, I've checked the source of the page but no errors.
 - If I print out a value before the If statement that checks for recordsets I get an error (mostly out of index errors verifying no record sets). I've tried:

Response.Write recTmp("username)
Response.Write recTmp.Fields.Item("username")
Response.Write recTmp.Fields("username")
Response.Write recTmpFields.Item(0)
Response.Write recTmp.Items("username")

All giving out of index errors or this:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

[Microsoft][ODBC Driver Manager] Program type out of range

/include/test.asp, line 0

Maybe this will help. Have you seen it?

http://support.microsoft.com/kb/175239