Headhunter
|
| Posted: 02/02/2003, 1:26 PM |
|
CCS2 beta 2
php4
mysql
win2k
In my application I have a page with a editable grid.
To make the grid work and display information is no problem. No problem there.
Now, in this grid I have 1 master table where some stuff has to be updated, and 3 lookup tables for some joins. When using the grid without joins (Visual query builder) the grid works fine. But when I use one or more joins, I get this error:
Database error: Invalid SQL: UPDATE `rma_hwr, hardware, manufacturer, customer` SET ret_date='2003-02-14', mfr_result=300, closed=1 WHERE rma_hwr.id=1 AND hardware.id=1 AND manufacturer.id=5 AND row_id=1
MySQL Error: 1146 (Table 'trackitstudio.rma_hwr, hardware, manufacturer, customer' doesn't exist)
Session halted.
where rma_hwr is my master table and hardware, manufacturer and customer are my lookup tables.
Am I doing something wrong? Is it not possible to use joins in an updatable grid?
I also noticed that when I create a new grid with the builder and select a source table, there are no fields displayed to selectn neither a primary key. I have to make a join in the visual query builder to make it work.
Thanks in advance
|
|
|
 |
Sean
|
| Posted: 02/03/2003, 10:39 PM |
|
It is not possible to use joins in an updateable grid, at least not if you want an update to actually work. You can do it by using a custom update, but even the builder for that is flawed when it comes to setting the where to match the records you are updating. I provided detailed feedback to support, so hopefully it is part of the updateable grid changes that are forthcoming (beta2?).
Sean
|
|
|
|
RonB
|
| Posted: 02/04/2003, 1:20 AM |
|
The problem is CCS thinks you want to do updates on all tables that are in the query. Even the ones you just use to join. It's not only a problem in the editable grid but in general. There are 2 ways to get around this:
1: only use the table you are updating via the visual query builder. Once you,ve created the grid do before show events on the fields to get values from other tables => the generated update query will not change.
2 Use query builder to build the grid, including all joins. After finishing and putting the record on the page go to the generated code and look for the update query. Alter the query to reflect the actual update.
If you are trying to do an update to several tables you have a problem if you are using mysql 3.x It doesn't support multi table updates. I discovered that when I build my application on my own pc(mysql 4.x) and then transfered the pages to my isp that has mysql 3.x it produced some nice error codes  and I had to rewrite the whole statement using some complex events.
Ron
|
|
|
|
Headhunter
|
| Posted: 02/04/2003, 12:17 PM |
|
Thanx RonB,
the custom update did the trick. I did never looked into it.
|
|
|
|
DaveRexel
|
| Posted: 02/04/2003, 2:30 PM |
|
Hi Ron
Your post would make an excellent article in the CodeChargers Knowledge Base http://codechargers.net/ccs/kb.php?language_id=1
Best Regards
Dave
|
|
|
|
|
