I have been running a sales force contact management script (CF 5.0) with an MS Access database on a CF7 Host. But, I want a more robust database that can handle more users. So, I purchased a database converter and coverted the MS Access database to MySQL. I created a new DSN (MySQL ODBC 3.51 Driver) and had my Host register it in the CF Admin. However, I'm getting an error when I browse or edit contacts. The error is as follows:
Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM ((((((Contacts LEFT JOIN ContactGroups ON Contacts.ContactID = Cont' at line 1
The error occurred in ..\cm\common\Contact_To_Struct.cfm: line 40
Called from ..\cm\mycontacts.cfm: line 38
Called from ..\cm\common\Contact_To_Struct.cfm: line 40
Called from ..\cm\mycontacts.cfm: line 38
CODE
37 : <CFQUERY NAME="qContactInfo" DATASOURCE="#datasrc#">
38 : SELECT Contacts.AddDate AS add_date, *
39 : FROM ((((((Contacts LEFT JOIN ContactGroups ON Contacts.ContactID = ContactGroups.ContactID) LEFT JOIN Email ON Contacts.ContactID = Email.ContactID) LEFT JOIN Address ON Contacts.ContactID = Address.ContactID) LEFT JOIN Phones ON Contacts.ContactID = Phones.ContactID) LEFT JOIN Website ON Contacts.ContactID = Website.ContactID) LEFT JOIN Groups ON ContactGroups.GroupId = Groups.GroupId) LEFT JOIN Comments ON Contacts.ContactId = Comments.ContactID
40 : WHERE Contacts.ContactID = #Val(ContactID)#
41 : ORDER BY addressid, phoneid, emailid, websiteid
42 : </CFQUERY>
I have attached the entire script (salesact) with MS Access mdb.
I am a complete newbie (with CF) and would greatly appreciate any help in resolving this issue (covert to MySQL).
28 Jan, 2008 - 11:58 PM
|