Re: [dba-users] Re: Base form read only

[Andrew Jensen]

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]