2. ASPRunnerPro General questions
  3. [SOLVED]  6.2 query error
This topic is locked
[SOLVED]

 6.2 query error

 		2/23/2010 5:37:23 PM
ASPRunnerPro General questions
T
 Tim author

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
Sergey Kornilov admin 2/23/2010

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.
T
 		Tim author 2/24/2010

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
T
 		Tim author 2/24/2010

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
J
 		Jane 3/4/2010

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 theEdit 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.
T
 		Tim author 3/10/2010

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