2. PHPRunner General questions
  3. [SOLVED]  MySQL SQL syntax
This topic is locked
[SOLVED]

 MySQL SQL syntax

 		9/14/2010 2:54:40 PM
PHPRunner General questions
R
 rjr003 author

Hello,
I have a child table 'po_details' with master 'orders'. When adding a record to 'po_details' using the inline add functionality i receive the following error:
"Record was not added. You have an error in your SQL syntax; check the manual that corresponds to you MySQL server version for the right syntax to use near 'det_price, products.pro_code,..."
My SQL query for 'po_details' is the following:
SELECT

po_details.det_id,

po_details.det_order,

po_details.det_supplier,

po_details.det_customer,

po_details.det_quantity,

po_details.det_price,

po_details.det_user,

det_quantitydet_price AS Amount,

products.pro_code,

products.pro_description,

products.pro_category,

products.pro_unit,

po_details.det_product

FROM po_details

INNER JOIN products ON po_details.det_product = products.pro_id
I have done this before without issues in other applications, and cannot really find what's wrong. Can anyone point me in the direction of why am i receiving this error when adding a record to this table?
Thanks!!
Regards,

Ricardo
Sergey Kornilov admin 9/14/2010

It would be helpful if you post the whole error message here.
As a rule of thumb - make sure you are not trying to update calculated or joined fields.
R
 		rjr003 author 9/15/2010



It would be helpful if you post the whole error message here.
As a rule of thumb - make sure you are not trying to update calculated or joined fields.


Hi Sergey,
The full error message is "Record was not added. You have an error in your SQL syntax; check the manual that corresponds to you MySQL server version for the right syntax to use near '*det_price, products.pro_code, products.pro_description, products.pro_category, ' at line 1"
I have double checked and all the fields to be updated are neither calculated nor joined fields.
Thanks in advance.
Regards,

Ricardo
R
 		rjr003 author 9/15/2010

Hi all,
I have managed to figure out that the inline add functionality is trying to insert into 'po_details' the calculated field above 'Amount' which is 'det_quantity*det_price'. Why is this happening and how can I prevent the action to try and update this field in the insert SQL statement?
Many thanks in advance.
Ricardo
Sergey Kornilov admin 9/15/2010

Ricardo,
make sure this field doesn't appear on Add/Edit pages. Remove it from those pages on 'Choose fields' screen.
R
 		rjr003 author 9/15/2010

Hi,
I have solved this issue. Had this field selected in the 'inline add' field list.
Thanks.
Ricardo