Frode
Posts: 3
|
| Posted: 02/22/2009, 2:16 AM |
|
Please advice:
PHP, MSSQL
Error message:
***************************
Form: Grid customers_Helpdesk_Z_Addr
Error: Incorrect syntax near the keyword 'WHERE'. (Microsoft OLE DB Provider for SQL Server)
******************************
Here's the SQL:
**************************************
SELECT customers.firstname AS customers_firstname, customers.lastname AS customers_lastname, Helpdesk_Z_Address_Status.Address_Status,
customers.CStatus AS customers_CStatus, customers1.firstname AS customers1_firstname, customers1.lastname AS customers1_lastname,
Helpdesk_Support_Replies_Techs.FNr, Helpdesk_Support_Replies_Techs.ANr, Helpdesk_Support_Replies_Techs.DateX,
Helpdesk_Support_Replies_Techs.UserID, Helpdesk_Support_Replies_Techs.Support_Reply, Helpdesk_Support_Replies_Techs.Email_Tech,
Helpdesk_Support_Replies_Techs.Workstatus, Helpdesk_Support_Replies_Techs.DataY
FROM Helpdesk_Support_Replies_Techs LEFT OUTER JOIN
customers LEFT OUTER JOIN
Helpdesk_Z_Address_Status ON customers.CStatus = Helpdesk_Z_Address_Status.ENr ON
Helpdesk_Support_Replies_Techs.UserID = customers.contactid LEFT OUTER JOIN
customers AS customers1 ON Helpdesk_Support_Replies_Techs.Email_Tech = customers1.email
WHERE (Helpdesk_Support_Replies_Techs.ANr = Helpdesk_Support_Replies_Techs.ANr)
ORDER BY Helpdesk_Support_Replies_Techs.DateX DESC
*********************************************************
I have verified this SQL by doing a "Verify SQL syntax" in Microsoft SQL Server Management Studio Express.
Everything is OK.
Still I get this error message:
***********************************
Form: Grid customers_Helpdesk_Z_Addr
Error: Incorrect syntax near the keyword 'WHERE'. (Microsoft OLE DB Provider for SQL Server)
********************************
Please advice!
Frode
|
 |
 |
mrachow
Posts: 509
|
| Posted: 02/22/2009, 11:53 PM |
|
Are sure this multiple used ON keyword is right?
_________________
Best regards,
Michael |
|
|
|
Frode
Posts: 3
|
| Posted: 02/23/2009, 1:05 AM |
|
Ops !! Sorry !!
That's not the correct SQL.
Here's the correct:
*************************************
SELECT customers.firstname AS customers_firstname, customers.lastname AS customers_lastname, Address_Status, customers.CStatus AS customers_CStatus,
Helpdesk_Support_Replies_Techs.*, customers1.firstname AS customers1_firstname, customers1.lastname AS customers1_lastname
FROM (Helpdesk_Support_Replies_Techs LEFT JOIN (customers LEFT JOIN Helpdesk_Z_Address_Status ON
customers.CStatus = Helpdesk_Z_Address_Status.ENr) ON
Helpdesk_Support_Replies_Techs.UserID = customers.contactid) LEFT JOIN customers customers1 ON
Helpdesk_Support_Replies_Techs.Email_Tech = customers1.email
WHERE Helpdesk_Support_Replies_Techs.ANr = {ANr}
ORDER BY Helpdesk_Support_Replies_Techs.DateX desc
*******************************************************
When I do the WHERE statement as a "session" then it list every record. With no errors.
But I don't want every record to be listed. I want the WHERE statement
When I change the WHERE statement to "url" (the ?ANr=245 is available in the url string), then I no listings. Instead I get the error message.
Frode
|
|
|
|
E43509
Posts: 283
|
| Posted: 02/23/2009, 4:32 AM |
|
Not sure what language you are using but I find it handy to temporarily edit the CC generated files and add an output to the screen of the actual sql. In CF that is in the ccOpenRs common file. Then I take that sql and run it against the db to get more info on the error.
|
|
|
|
dprechtl
Posts: 2
|
| Posted: 03/11/2009, 12:54 PM |
|
I have the same problem. It started after an upgrade from x.6 to x.32. Has anyone come up with a solution for this? If the query type is changed from table to SQL it works fine but you loose the column sorter capability.
|
|
|
|
Frode
Posts: 3
|
| Posted: 03/11/2009, 1:04 PM |
|
Me too!
I changed it from table to sql, but then I lost the sorter capability.
|
|
|
|
Jan K. van Dalen
|
| Posted: 03/15/2009, 2:13 PM |
|
I received a fix from YesSoftware.
"dprechtl" <dprechtl@forum.codecharge> wrote in message
news:249b817138f8e6@news.codecharge.com...
>I have the same problem. It started after an upgrade from x.6 to x.32. Has
> anyone come up with a solution for this?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
