Re: insufficient key column information

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 28 Feb 2003 12:06:05 +0000
Message-ID: <b3ncfd$kmm$1@ctb-nnrp2.saix.net>

Scott wrote:

> My project originally had a VB frontend and a MSAccess backend. Now
> I'd like to migrate it over to a Oracle backend and use the ADO code
> from the original frontend. I've had lots of problems, but the latest
> has got me stumped. When I try to update using an altered recordset,
> I get an "insufficient key column information for updating or
> refreshing" error. I haven't changed anything, so I'm not sure what
> key is being talked about.

Scott, that's what happen when you take a stockcar rider (VB code running on MS Access) and put him on a state-of-the-art F1GP car. :-)

When using data controls, the controls generate your SQL for you. This is fine when dealing with local file-based "databases", but a bad idea for real client-server implementations.

In order for the control to generate the UPDATE statement, it needs to know how to find the row to be updated in the database.

Worse case scenario is something like (data control has no clue what the unique columns are):
UPDATE mytable
SET postalcode = 1234

WHERE cust_code = :value1
AND   cust_name = :value2
AND   address1  = :value3

AND address2 = :value4
AND postalcode = :value5
..etc. for every single column for the table...

So to prevent this type of thing from happening, data controls allow you to specify the unique identifier(s) for the table. This can often be done by either selecting an index to be used (the control interrogates the index to find the column names), or manually identifying the columns that must be used for uniquely identifying the row.

In such a case, the data control will generate this type of SQL: UPDATE mytable
SET postalcode = 1234
WHERE cust_code = :value1

It seems to me that in your case, the VB data control code fails to discover what the unique columns are - and as it fails that, it can not update the table.

I personally would balk a lot when anyone wants to run a VB/MS Access application, "ported" to Oracle, against any of my Oracle databases. The techniques used for developing using local file-based databases, differs *SUBSTANTIALLY* with what should be done in a proper client-server application.

Back in '94 I did my own port of a VB app I did, from MS Access, to SQL-Server 4. Performance sucked (and a single app instance loaded, generated 50% of the total LAN traffic).

I winded up re-writing the _entire_ application, basically from scratch. Through the years I've seen others running into this same problem time and time again. So be very wary when it comes to functionality and performance of your VB/MS Access app running on Oracle.

Final comment. Personally, I have stayed far away from using VB and ADO (or ODBC) for client-server development. VB fails as an object orientated language. ADO/ODBC/Jet Engine and all the other flavours of Microsoft's generic database APIs, are fat and interfering with dangerous defaults (visions of Austin Meyer's Fat Bastard).

I've shown in benchmarks to my clients back in the 90's that something like ODBC generates up to 4x the network traffic that using alternative methods (like native OCI or thin drivers).

--
Billy

Received on Fri Feb 28 2003 - 06:06:05 CST