[timnorvel]

Hello,

After upgrading a project from 6.1 to 6.2 I get an error when trying to edit a record.

Here is the table's SQL:
SELECT
jobs.JobID,
jobs.`Job Name`,
jobs.Customer,
jobs.Stage,
jobs.Notes,
jobs.`Start Date`,
jobs.`End Date`,
customers.`Full Name` AS `Customer Name`,
IFNULL(SUM(op.price), 0) AS `Total Original Price`,
IFNULL(SUM(oc.cost), 0) AS `Total Original Cost`,
(IFNULL(sum(op.price),0) - IFNULL(sum(oc.cost),0)) AS `Total Original Profit`,
IFNULL(SUM(cop.price), 0) AS `Total CO Price`,
IFNULL(SUM(coc.cost), 0) AS `Total CO Cost`,
(IFNULL(sum(op.price),0) + IFNULL(sum(cop.Price),0)) AS `Total Current Price`,
(IFNULL(sum(oc.cost),0) + IFNULL(sum(coc.cost),0)) AS `Total Current Cost`,
((IFNULL(sum(op.price),0) + IFNULL(sum(cop.Price),0)) - (IFNULL(sum(oc.cost),0) + IFNULL(sum(coc.cost),0))) AS `Total Current Profit`,
IFNULL(SUM(c.Collected), 0) AS `Total Collected`,
IFNULL(SUM(b.Paid), 0) AS `Total Paid`,
((IFNULL(sum(op.price),0) + IFNULL(sum(cop.Price),0)) - IFNULL(sum(c.collected),0)) AS `Yet to Collect`,
((IFNULL(sum(oc.cost),0) + IFNULL(sum(coc.cost),0)) - IFNULL(sum(b.Paid),0)) AS `Yet to Pay`,
(((IFNULL(sum(op.price),0) + IFNULL(sum(cop.Price),0)) - IFNULL(sum(c.collected),0)) - ((IFNULL(sum(oc.cost),0) + IFNULL(sum(coc.cost),0)) - IFNULL(sum(b.Paid),0))) AS `Profit Remaining`
FROM jobs
INNER JOIN customers ON jobs.Customer = customers.CustomerID
LEFT OUTER JOIN orgprice AS op ON jobs.JobID = op.job
LEFT OUTER JOIN orgcost AS oc ON jobs.JobID = oc.job
LEFT OUTER JOIN coprice AS cop ON jobs.JobID = cop.job
LEFT OUTER JOIN cocost AS coc ON jobs.JobID = coc.job
LEFT OUTER JOIN billspaid AS b ON jobs.JobID = b.job
LEFT OUTER JOIN collected AS c ON jobs.JobID = c.job
WHERE jobs.Stage <> 'complete' AND jobs.Stage <> 'on hold'
GROUP BY jobs.JobID, jobs.`Job Name`, customers.`Full Name`


Here is the error I get:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.51a-community-nt]Unknown column '(IFNULL(sum(op.price),0) - IFNULL(sum(oc.' in 'field list'
/TheTool/include/aspfunctions.asp, line 1232


The other thing is, after the upgrade the query for that table was not correct: the upgrade put extra characters (`) right were that error ends... so IFNULL(sum(oc.cost),0)) became IFNULL(sum(oc.` and it just got messy from there. I copyed the query from the 6.1 version and that seemed to fix it in ASPRunner where the results tab would work, but I still get the error on the web page when I try to edit.

Thanks for your help,
Tim

[admin]

Tim,

here is what I can suggest.

Install build 4905 (http://www.xlinesoft.com/download).
In 'After Application Initialized' event use the following code:
SQLUpdateMode = true

Rebuild your project.

If this doesn't help post your project to Demo Account and send me URL where I can see this issue.

[timnorvel]

Thanks Sergey, but that didn't work. I was already at 4905: I added the code: same problem. I uploaded to demo. here is the link:


TheTool

Click "jobs" on the menu page, then click "Edit" on any record.

Thanks for your help.
Tim

[timnorvel]

I just noticed another thing. Everytime I save the project, the query gets messed up again. I can build the project, go back to the query and it looks fine, but if I save and re-open the project, the query is messed up. Here is what it looks like after a re-open:

SELECT
jobs.JobID,
jobs.`Job Name`,
jobs.Customer,
jobs.Stage,
jobs.Notes,
jobs.`Start Date`,
jobs.`End Date`,
customers.`Full Name` AS `Customer Name`,
IFNULL(SUM(op.price), 0) AS `Total Original Price`,
IFNULL(SUM(oc.cost), 0) AS `Total Original Cost`,
`(IFNULL(sum(op`.`price),0) - IFNULL(sum(oc`. AS `Total Original Profit`,
IFNULL(SUM(cop.price), 0) AS `Total CO Price`,
IFNULL(SUM(coc.cost), 0) AS `Total CO Cost`,
`(IFNULL(sum(op`.`price),0) + IFNULL(sum(cop`. AS `Total Current Price`,
`(IFNULL(sum(oc`.`cost),0) + IFNULL(sum(coc`. AS `Total Current Cost`,
`((IFNULL(sum(op`.`price),0) + IFNULL(sum(cop`.`Price),0)) - (IFNULL(sum(oc`.`cost),0) + IFNULL(sum(coc`. AS `Total Current Profit`,
IFNULL(SUM(c.Collected), 0) AS `Total Collected`,
IFNULL(SUM(b.Paid), 0) AS `Total Paid`,
`((IFNULL(sum(op`.`price),0) + IFNULL(sum(cop`.`Price),0)) - IFNULL(sum(c`. AS `Yet to Collect`,
`((IFNULL(sum(oc`.`cost),0) + IFNULL(sum(coc`.`cost),0)) - IFNULL(sum(b`. AS `Yet to Pay`,
`(((IFNULL(sum(op`.`price),0) + IFNULL(sum(cop`.`Price),0)) - IFNULL(sum(c`.`collected),0)) - ((IFNULL(sum(oc`.`cost),0) + IFNULL(sum(coc`.`cost),0)) - IFNULL(sum(b`. AS `Profit Remaining`
FROM jobs
INNER JOIN customers ON jobs.Customer = customers.CustomerID
LEFT OUTER JOIN orgprice AS op ON jobs.JobID = op.job
LEFT OUTER JOIN orgcost AS oc ON jobs.JobID = oc.job
LEFT OUTER JOIN coprice AS cop ON jobs.JobID = cop.job
LEFT OUTER JOIN cocost AS coc ON jobs.JobID = coc.job
LEFT OUTER JOIN billspaid AS b ON jobs.JobID = b.job
LEFT OUTER JOIN collected AS c ON jobs.JobID = c.job
WHERE (jobs.Stage <> 'complete') AND (jobs.Stage <> 'on hold')
GROUP BY jobs.JobID, jobs.`Job Name`, customers.`Full Name`


Ah... I see.... it looks like it errors on the lines that start with an "(". But I need that for the calculation. Anyway, hope you can help.

Thanks,
Tim

[Jane]

Tim,

thank you for pointing me to this bug.
We'll fix it with the next update of ASPRunner.

To get your project working now modify your SQL query on the Edit SQL query tab in ASPRunner.
Remove the surrounding brackets from the fields.
I.e. change these field definitions:

(IFNULL(sum(op.price),0) - IFNULL(sum(oc.cost),0)) AS `Total Original Profit`,
...
(IFNULL(sum(op.price),0) + IFNULL(sum(cop.Price),0)) AS `Total Current Price`,

to this:

IFNULL(sum(op.price),0) - IFNULL(sum(oc.cost),0) AS `Total Original Profit`,
...
IFNULL(sum(op.price),0) + IFNULL(sum(cop.Price),0) AS `Total Current Price`,

and so on.

[timnorvel]

Thanks Jane,

Thanks for fixing this in the next version, but it's not really a big deal for me to remove the parentheses. I thought I needed them for the math to work correctly. I see now that I don't. I guess I just needed them so I could make sense of it... kinda complicated math for my head.

Thanks,
Tim