asp EOF - but query executes in mysql

[thompom]

Hi,

i dont think this is a sql syntax prob as when i execute the query from mysql it runs fine and displays a recordset.

However when i run the query from my asp page it returns eof.

this is because of this part of the statment

sum(TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1) AS holcount

i am opening the rs like this

Set rsside = Server.CreateObject("ADODB.Recordset")
rsside.Open sqlside, conn

and have tried

set rsside = conn.execute(sqlside)

the sql looks like

SELECT CC.code, sum(TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1) AS holcount, CC.holtype 
FROM holtype AS CC 
LEFT JOIN (SELECT CB.bookst, CB.bookend, CB.userholid, CB.holbookid, CB.holtypeid, CB.deptid FROM holbook AS CB WHERE CB.userholid = '46' 
AND ((cb.bookst BETWEEN '20070101' AND '20071231') 
OR (cb.bookend BETWEEN '20070101' AND '20071231') 
OR (cb.bookst <= '20070101') AND (cb.bookend
>= '20071231'))) 
AS CBB ON CC.holtypeid = CBB.holtypeid 
GROUP BY CC.holtypeid 
ORDER BY CC.holtypeid

if you have an idea of what i could try next please let me know

 

[thompom]

..also this executes in ASP if i take out the 'SUM' part of the statement

 

[DotNetGnat]

make your query a stored procedure and call it in the asp page...that should work...

-DNG

 

[Sheco]

There are no errors ??

err.number = 0 ??

conn.Errors.Count = 0 ??

 

[thompom]

hi - thanks for your replies [didnt get any email notifications so sorry for the delay]

DotNetGnat - never used stored procs befors will give that a go

Sheco - no errors that i can see just EOF - did

if rs.eof then
response.write "EOF"
end if

and i get the EOF message - as soon as i take out the SUM from the sql it runs outputs the RS.

thanks again

 

[thompom]

solved!! - needed to download the latest ODBC driver
spent hours trying to get this to work, strange thing is sum on other statements worked

DotNetGnat - tried using stored procs - what a nightmare