Recent

Author Topic: Complex query editable  (Read 3595 times)

xinyiman

  • Hero Member
  • *****
  • Posts: 2092
    • Lazarus and Free Pascal italian community
Complex query editable
« on: January 04, 2012, 09:04:27 am »
How can I edit a query inside a DBGrid which is a join between two tables? And 'possible or not?

Error: cannot update a complex query with more then one table
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Complex query editable
« Reply #1 on: January 04, 2012, 09:46:01 am »
How can I edit a query inside a DBGrid which is a join between two tables? And 'possible or not?

Error: cannot update a complex query with more then one table
TSqlQuery can not create automatically the update/insert/delete SQL for multi-table selects. You'll have to create these yourself.
UpdateSQL, InsertSQL and DeleteSQL use special pre-defined variables :fieldname and :OLD_fieldname (replace 'fieldname' with the real field name). :fieldname is the current value in your DBGrid, :OLD_fieldname the value in the database.
Example:
Code: [Select]
update MyTable
 set Name = :name, multiplier = :multiplier
 where Name = :OLD_name and multiplier = :OLD_multiplier

Note that you can specify only a single query. So updating multiple tables with this technique won't be possible. In that case you can use stored procedures if the database has them and call these from UpdateSQL, etc.

 

TinyPortal © 2005-2018