MySQL Forums
Forum List  »  Microsoft Access

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Complex SQL
2311
June 01, 2006 05:26AM
1406
June 03, 2006 04:23PM
1399
June 04, 2006 12:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.