ADO query

Hi,

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??

Thanks,
Karlova
karlovaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RuchiCommented:

i.e.

select employees.name, departments.name, 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.
0
mjzalewskiCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jackgCommented:
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.
0
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.
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.