Hi,

I am trying to convert my asp pages from Access to MySQL and have run into some problems with SQL syntax. I have a series of two pages developed in UltraDev that insert new records into multiple tables. The pages have worked great with Access for almost a year, but return the error below with MySQL:

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
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
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>


This is the code that seems to be causing the problem:


&lt;%If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) &lt;&gt; "" Thenset Command1 = Server.CreateObject("ADODB.Command"Command1.ActiveConnection = MM_canphoto_STRINGCommand1.CommandText = "INSERT INTO ContactCategories(Category_ID, Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''" + ", " + Replace(Command1__varContact_ID, "'", "''" + " "Command1.CommandType = 1Command1.CommandTimeout = 0Command1.Prepared = trueCommand1.Execute()End If%&gt;

If anyone can help me figure this out I would be very grateful.

Thanks,

Andrew

Do you mind adding appropriate RETURNS to make the code more readable? I'm assuming its :

&lt;%

If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) &lt;&gt; "" 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

%&gt;

am I right?

Aegis Kleais
New Media Web Developer
(DWMX : IIS5.1 : SQL2K : WXP : ASP[VB/JS])

Edited by - aegiskleais on 02 Aug 2002 19:36:59

Sorry my post wasn't more legible and sorry I took so long to reply. You did put the returns in the right place. Do you have any ideas why the page causes the error?

I appreciate your efforts to help and any suggestions would be much appreciated.

Cheers.

Can anyone at all help me out with this one?

Here's a little more information that might clarify the problem. I have a database of services and suppliers. The first insert page inserts new contact information such as name and address into a table called 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 ( the one causing the problems) lists all the categories and should insert the appropriate combinations of Contact_ID and Category_ID into the table ContactCategories.

The pages have worked flawlessly for almost a year with an Access database. I'm trying to convert to MySQL and now they generate the SQL syntax error.

I hope someone can help, because this is driving me crazy and I can't help but think there must be an easy solution.

Cheers,

Andrew