davep
Posts: 41
|
| Posted: 02/16/2009, 10:10 AM |
|
I'm developing an app using ASP code and MS Access as the development database. I've built a grid and record using the builder and included a search. Everything initially appears fine when the page is viewed, but whenever ANY criteria is entered in the search, I get a "Syntax Error in JOIN Operation" error, and the grid does not display.
The SQL query is as follows:
SELECT Title, CNumber, SDate, EDate, Amount, Contracts.VendNo AS ContractsVendNo, Contracts.OptCode AS ContractsOptCode, Signer, Contact,
VendName, ProgName, Signer.FName+' '+Signer.LName AS SignerName, Contact.FName+' '+Contact.LName AS ContactName, ConNo
FROM (((Contracts INNER JOIN Vendors ON
Contracts.VendNo = Vendors.VendNo) INNER JOIN Programs ON
Contracts.OptCode = Programs.OptCode) INNER JOIN Staff Signer ON
Contracts.Signer = Signer.PSL) INNER JOIN Staff Contact ON
Contracts.Contact = Contact.PSL
WHERE Contracts.CNumber LIKE '%{s_CNumber}%'
AND Contracts.SDate = #{s_SDate}#
AND Contracts.EDate = #{s_EDate}#
AND Contracts.VendNo LIKE '%{s_ContractsVendNo}%'
AND Contracts.OptCode LIKE '%{s_ContractsOptCode}%'
AND Contracts.Signer LIKE '%{s_Signer}%'
AND Contracts.Contact LIKE '%{s_Contact}%'
ORDER BY CNumber
Any help would be greatly appreciated.
Thanks,
Dave
|
 |
 |
Mango_Lier
Posts: 110
|
| Posted: 02/17/2009, 6:12 AM |
|
I have the same problem. My final SQL looks like this.
SELECT TOP 20 UserInfo.ID AS UserInfo_ID, UserInfo.Name, UserInfo.Address, City.City AS City_City, UserInfo.Comments, UserLevel.UserLevel AS UserLevel_UserLevel, UserInfo.Birthdate, UserInfo.CanPost
FROM (UserInfo LEFT JOIN City ON
UserInfo.City = City.ID) LEFT JOIN UserLevel ON
UserInfo.UserLevel = UserLevel.ID WHERE UserInfo.UserLevel = 1 ORDER BY Rating
|
|
|
|
davep
Posts: 41
|
| Posted: 02/18/2009, 3:35 PM |
|
I received a patch to the classes.ccp file from Yes and that fixed the problem.
|
|
|
|
Mango_Lier
Posts: 110
|
| Posted: 02/18/2009, 9:36 PM |
|
Sent you a private message via forum, can you send me the patch?
|
|
|
|
durandal
Posts: 4
|
| Posted: 03/19/2009, 2:50 PM |
|
I'm having the exact same problem.
Is there any way someone can send me the patch?
|
|
|
|
jpflana
Posts: 13
|
| Posted: 04/29/2009, 4:17 PM |
|
Had the same error. I just upgraded to version 4.2 and it fixed the problem. So for anyone else who searches for an answer to this error, just install the newest version of CodeCharge. They must have included whatever fix was put out in the before-mentioned patch.
|
|
|
|
|
