Trouble with SELECT clauses when migrating from Access

[Jakob Hellquist]

Hi,

I just started migrating our website from Access to MySQL, and I can't get some of the SQL clauses to work. It worked perfectly with Access, and the connection to mysql is fine, but the html tables with the data are blank as soon as I switch to mysql.

I guess there is something in the syntax that should be changed; brackets, parentheses... - any suggestions would be highly appreciated!

/Jakob


Set Database = Server.CreateObject("ADODB.Connection")
Database.Open "PROVIDER=MSDASQL;" & _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"DBQ="&myserver&";" & _
"UID=myDB;PWD=myPwd;"


' Getting the data
Set rsAnswers = Server.CreateObject("ADODB.Recordset")
sql="SELECT CorrectAnswerId, CourseID, ExerciseID, ExerciseSubID, CorrectAnswer, ExerciseItemId FROM CorrectAnswers WHERE ExerciseId="& iExerciseId &" AND ExerciseSubId ="& Request.Form("exercisesubid") &" ORDER BY ExerciseItemId"
rsAnswers.Open sql, Database, 3, 3

-----------------and another SELECT clause:

Set rsMistakes = Server.CreateObject("ADODB.Recordset")
sql="SELECT CommonMistakeId, CommonMistake, TeachersComment FROM CommonMistakes WHERE CorrectAnswerId="& rsAnswers.Fields("CorrectAnswerId").Value &" ORDER BY CorrectAnswerId"
rsMistakes.Open sql, Database, 3, 3

-----------------and an INSERT clause:

sql="INSERT INTO ToComment (CorrectAnswerId, SubmittedAnswer, StudentQueryName) VALUES ("& rsAnswers.Fields("CorrectAnswerId").Value &", '"& arExerciseItemId(i) &"', '"& myStudent &"')"
rsComments.Open sql, Database, 3, 3