ADO query


I'm using a recordset built with sql including an outer join between two Oracle tables.... I need to use the update method of the ADO recordset - Error returned = "Insufficient key column information for updating or refreshing"

Any quick solutions??

Who is Participating?
mjzalewskiConnect With a Mentor Commented:
Well try the obvious. Are you selecting the primary key from both tables?  And is the Outer Join based on a foreign key? If these two conditions are not met, there will not be enough information in the Recordset to identify which rows should be written back to the corresponding tables.

I think you may want to set the cursor type to ADODB.adOpenKeyset. It depends on your ODBC driver, but if you choose a keyset cursor, the driver should pull the Oracle ROWIDs into the recordset, so you won't have to worry about the primary keys.

You can *definately* do this in Oracle. I use it all the time with ADO (the Microsoft ODBC Driver for Oracle), version 7 and 8.


select,, departments.location from departments left join employees on departments.department_id = employees.department_id;

this example is from my notes.

in an outer join, the data in the tables' common column is not an exact.
the row in which the data does not match will not be displayed unless an outer join is used. the left join indicates that all rows from the table on the left (departments) are to be included in the result while only the matching rows from the table on the right should be in included.
Sorry, but I have experienced the same problem, you cannot update from a query that uses a join in oracle. I suggest that you try to simplify the sql so that you can update the tables separately.
karlovaAuthor Commented:
Sorry jackq but you can update a join query in Oracle (see mjzalewski) The outer join in question had a field in each table which had the same name -so a simple "tableIdentifier.field AS 'OtherName' in the SQL did the trick.
karlovaAuthor Commented:
Thanks for the advice - which would have solved most of the problems encountered in updating a join - See previous comment for the solution found.
All Courses

From novice to tech pro — start learning today.