SQL syntax error '80004005'

Can anyone at all help me out with this one?

I am trying to convert my asp pages from Access to MySQL and have run into some problems with SQL syntax. My first insert page inserts new contact information such as name and address into table Contacts. Each contact can belong to several categories, so I also have a table called Categories and a table between the two called ContactCategories. The second insert page lists all the categories with check boxes and should insert the appropriate combinations of Contact_ID and Category_ID into table ContactCategories.

The pages have worked flawlessly for almost a year with an Access database. I'm trying to convert to MySQL and now the second page generates the error below:

ADODB.Connection.1 error '80004005'
SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near ')' at line 1

/canphotodirect/insert_pg2.asp, line 102


This is the code that seems to be causing the problem. Line 102 is "Command1.Execute()":

<%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID").Value)) <> "" Then

set Command1 = Server.CreateObject("ADODB.Command")

Command1.ActiveConnection = MM_canphoto_STRING

Command1.CommandText = "INSERT INTO ContactCategories (Category_ID, Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''") + ", " + Replace(Command1__varContact_ID, "'", "''") + ") "

Command1.CommandType = 1

Command1.CommandTimeout = 0

Command1.Prepared = true

Command1.Execute()

End If
%>

If anyone can help I would really appreciate it. My update page also generates the same error, but it works perfectly with Access too.

Cheers,

Andrew

Command1.CommandText = "INSERT INTO ContactCategories (Category_ID, Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''") + ", " + Replace(Command1__varContact_ID, "'", "''") + ") "

This should be "''" according to the rest of your code.

Hope this helps.

Not sure that is correct merlin. What arossphoto is doing there is replacing single quotes with double, which should be done when inserting into a DB.. the replace function parameters are string literal, string to find, string to replace with.

arossphoto - Create you SQL in a string variable first, then response write that string before executing it - that'll show you what the problem. This method, btw, is the best way to debug.