Converting from Access to MySQL

[junkjones]

Hi Everyone,

I'm moving a rather large application from Access to MySQL right now, and I'm having nothing but problems with errors now! Here is the first problem I'm having:

<cfupdate Datasource=&quot;#session.ds#&quot; tablename=&quot;ForumVariables&quot; FormFields=&quot;BGColor,BGImage,Text,Link,ALink,Vlink,TimeColor,Column1,Column2,CatBGColor,CatTextColor,FontFace,FontSize,TitleColor,CopyColor,TableWidth,TitleImage,ReplyImage,NewTopicImage,ImageDir,HomeURL,ForumURL,ForumEmail,Mailserver,MailPort,DisplayCat,ForumTitle,HomeTitle,BadWordList,Copyright,TimeZone,Header,Footer,ForumRules&quot;>


Gives me this error:

SQL operation updated no rows.

The SQL operation affected no rows.Either the Key value has changed or the row has been deleted.

Any ideas on how to fix this? As I'm sure almost every query I encounter will have to be fixed to work with MySQL, can anyone also give me an overview on the difference between writing syntax for MySQL as opposed to Access? I'm kind of a novice at this stuff, and I just assumed it would all work the same

Thanks!

 

[philhege]

Jones,

Is BGColor the key value of your records?

PH

 

[junkjones]

No, the key value is passed from the form:

<input type=hidden name=VariablesID value=1>

All the other queries in the application are giving me syntax errors, I'm not sure what MySQL wants me to do differently than Access does, so I don't know where to start changing the syntax...

 

[philhege]

I'm not understanding how CFUPDATE grasps that VariablesID is the key field of the record you want to update.

I can only assume from the documentation that CFUPDATE expects the first field passed in the fieldnames list to be the key field. It's not clear, but that's the way the example works.

Try putting &quot;VariablesID&quot; at the beginning of the field list and see what happens.

Personally, I don't use CFUPDATE or CFINSERT; not a good track record for either one.

HTH,
PH

 

[PDL]

I recently moved about 80+ tables from various access DB's to SQLServer and we made it a rule... use the SQL INSERT and UPDATE statements.... CFUPDATE/CFINSERT just was too much trouble... they will work after fiddling with them but the other SQL statement offer a lot more flexibility and are stable!

 

[strantheman]

Hey PDL im sorry we couldn't have resolved this faster... i also wish you have put a space in that list of variables. hehe

Yes, SQL is the standard. Good for you to move into that standard now... i know its rough sometimes, but think about all the converting you'd have to do 6 months from now, a year?

Good move.