ADO Recordset and double join

[Jaco]

Hello

I have a problem with updating a recordset which is based on two tables joined with a double inner join. These tables have also double indexes (based on two fields). I receive an error message: "insufficient key column information for update or insert a row". I've set the propoerty "unique table" but it didn't help much. Thanks for any help

Greets,

Jaco

 

[nicsin]

Hi Jaco,

would you mind posting the sql here?

nicsin

 

[MatthewPeters]

Hello Jaco,

I ran into this exact problem earlier this week. The problem was that I had neglected to set the primary key constraint on the field I was using as my primary key. I could write select statements joining tables on my pKey field but I would get the "Insufficient Key . . ." error when I tried to update.

I would double check your primary keys and make sure everything is set correctly.

Matt

 

[Jaco]

My SQL query looks like this:

SELECT clients.id_cl, clients.id_branch, ...., invoices.id_inv, invoices.id_branch, invoices.idcl .... FROM clients inner join invoices on clients.id_branch = invoices.id_branch AND clients.id_cl = invoices.idcl

The table "clients" has a double Primary key (id_cl,id_branch) and the table "invoices" has also a double Primary key (id_inv,id_branch)

Meanwhile I found this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;246905

(BUG: Problem Updating ADO Hierarchical Recordset When Join Tables Share Same Column Name) which seems to explain the problem.

As a matter of fact my recordset isn't hierarchical but if change the column name invoices.id_branch e.g to invoices.idbranch the problem dissapears!
Unfortunately at this stage I can't change the database structure Seems to me that I will have to use update sql queries directly.

Jaco