Complex SQL
Posted by:
jazzy639
Date: June 01, 2006 05:26AM
Hello,
Ive just migrated from access to MySQL and I have a complex SQL statement that wont work.
Here is the raw code
*******************************************************
Dim r01
Dim r01_numRows
Set r01 = Server.CreateObject("ADODB.Recordset")
r01.ActiveConnection = MM_dbconnect_STRING
r01.Source = "SELECT COUNT(main.room) As Total, day, period, category FROM main, rooms WHERE rooms.room = main.room AND category = '" + Replace(cat, "'", "''") + "' AND period = " + Replace(i, "'", "''") + " AND class = 0 AND week = '" + Replace(twk1, "'", "''") + "' AND daycode = DatePart('w', CURDate()+'" + Replace(wk, "'", "''") + "') AND (main.room NOT IN (SELECT room FROM booked WHERE main.period = booked.period AND main.room = booked.room AND main.day = booked.sday AND sdate LIKE CURDate()+'" + Replace(wk, "'", "''") + "')) GROUP BY day, period, category"
r01.CursorType = 1
r01.LockType = 3
r01.Open()
r01_numRows = 0
*******************************************************
It produces this output:
*******************************************************
SELECT COUNT(main.room) As Total, day, period, category FROM main, rooms WHERE rooms.room = main.room AND category = 'IT Room' AND period = 1 AND class = 0 AND week = 'A' AND daycode = DatePart('w', CURDate()+'0') AND (main.room NOT IN (SELECT room FROM booked WHERE main.period = booked.period AND main.room = booked.room AND main.day = booked.sday AND sdate LIKE CURDate()+'0')) GROUP BY day, period, category
*******************************************************
I get: Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
Any ideas?
Thanks