Re: Re: Base form read only

Gert, Frank

I have a query in an embedded HSQL database:

SELECT "STUDENTS"."ID" AS "STUDENTID", "STUDENTS"."FirstName", 
"STUDENTS"."LastName", "STUDENTS"."email", "STUDENTS"."COURSEID", 
"MARKS"."ID" AS "MARKID", "MARKS"."STUDENTID", "MARKS"."MARKFOR", 
"MARKS"."MARK", "MARKS"."COMMENTS" FROM { OJ "MARKS" "MARKS" LEFT OUTER 
JOIN "STUDENTS" "STUDENTS" ON "MARKS"."STUDENTID" = "STUDENTS"."ID" }

The query includes all the fields from both tables.

It is indeed treated as updateable - however, when I open the query and 
insert a new record only the values for the MARKS table are written. The 
values entered for the STUDENTS table are discarded.

Both STUDENT.ID and MARKS.ID are of type IDENTITY. It appears that the 
marks table is written to first, in fact it completely bypasses any 
referential integrity check, the tables are related with a foreign key.

Andrew Jensen

Gert Blij wrote:

> > You cannot update queries joining two tables, unless 
>  
>
>>they contain (IIRC - would have  to look into the code or ask 
>>my colleague :) ) a JOIN. That means that "SELECT A.a, B.b 
>>from A, B where A.b = B.ID" is not updatable. Try changing 
>>the connection between A and B: In the query design, 
>>double-click the connection line, and change it's Type to 
>>something else - does this help?
>>    
>>
>
>It didn't. The standard INNER JOIN indeed generates "Where x = y" statement.
>Changing it to LEFT JOIN generates a JOIN (RIGHT JOIN doesn't seem to be
>supported):
>
>SELECT `categorymaster`.`Category`, `categorymaster`.`Description`,
>`stockmaster`.`ItemNo`, `stockmaster`.`Description` FROM { OJ
>`newstock`.`stockmaster` `stockmaster` LEFT OUTER JOIN
>`newstock`.`categorymaster` `categorymaster` ON `stockmaster`.`Category` =
>`categorymaster`.`Category` }
>
>But the generated form on this query also does *not* allow any editing.
>
>Any further ideas?
>
>Cheers
>Gert
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [email protected]
>For additional commands, e-mail: [email protected]
>
>
>
>
>  
>