Key Column Info is not enough, says VB6 attackin MySQL with Left Join

[miguelgm]

Hi!!!!
I have this code in a MS VB6 adodc recordsource:

SELECT stat.DescripcionEst, inm.*
FROM (inm LEFT JOIN stat ON inm.Estado = stat.IdEstadoInmueble) ORDER BY inm.IdInm

It shows records ok, fast and linked. But when I try to change any value, an error says: Not enough key column info... and I can't update.

I use client cursor, R/W access, etc, etc. I want to know if Left Join selects can be R/W, or only are ReadOnly.
If they are R/W, where am I mistaking?
Lots of thanks

 

[gheist]

what is your "update" statement - as this "select" seems to be working and if added with "primary key" for specific table can be used in next update ( i dont see any "for update" or so in your select statement and inm.Estado doesnt seem to be primary key )

 

[miguelgm]

inm.Estado is not a primary key.
stat.IdEstadoInmueble IS primary key.
I dont use any update sentence, 'cos VB updates everytime y change cursor to another record. In this moment is when error happens, or when I use rec.recordset.update.

I dont use FOREING KEY in MySQL definition, but I guess I don't need.
I use Access and ODBC to build large SELECT sentences, and it works well (retrieving and updating) , but when copy&paste to VB6 (same ODBC, same DB) this SELECT string, retrieving is ok, but updating.....not!
It's a very bizarre error, and I'm crazy about it.

 

[gheist]

ODBC or ADO? -they are a bit different

from

http://www.mysql.com/documentation/mysql/full/

Visual Basic
To be able to update a table, you must define a primary key for the table. Visual Basic with ADO can't handle big integers. This means that some queries like SHOW PROCESSLIST will not work properly. The fix is to set add the option OPTION=16834 in the ODBC connect string or set the Change BIGINT columns to INT option in the MyODBC connect screen. You may also want to set the Return matching rows option.

 

[miguelgm]

I've checked both, and still doesn't work.
I used Access 2000 as frontend, same DB, same ODBC, same select sentece (copied and pasted), and it worked with these options disabled (Change BIGINT to INT and Returning matching rows) and now, both enabled.
When I use Access as frontend, everything goes well, but I copy Select string, and paste it on a adodc, I use same ODBC, same DB...... it shows records well, but I can't change any data.
I also enable Don't optimize column width, Allow Big results, Safety, and Disable transactions.

 

[gheist]

Access uses ODBC
VB uses ADO
ODBC works
ADO does not

You will need to select primary key for table you need to update

 

[miguelgm]

jajajajajaja :-D :-D

Can you explain a little more? How do I select primary key in a Select string?.... and a lot of thanks!!!!

 

[gheist]

"To be able to update a table, you must define a primary key for the table"

so

ALTER TABLE a_table PRIMARY KEY access_unique_id

primary key is way of identifyinig records in table, just like access uses unique counter

 

[miguelgm]

Primary key was defined first time i created these tables. I've tried your last advice, and mysql returns "ERROR 1068: Multiple primary key defined". So, this item was ok. But updating still remains wrong.

:-( Can you see why i'm crazy............?

 

[gheist]

did you select a primary key column for table you need to update in your select statement?

 

[miguelgm]

How do I do that?. I've been searching MySQL doc and I did not find anything about it. Which is the real instruction?.

........and a lot of thanks!!!

 

[gheist]

SELECT at_least_a_primary_key FROM table_you_need_to_update WHERE it=ok_to_update

UPDATE ...

 

[miguelgm]

I "select alltables.* ..." so primary keys also are selected from all tables. What do you mean "WHERE it=ok_to_update"?.

 

[gheist]

you may need some filtering not to manipulate all the records at once -

 

[miguelgm]

Yes. Actually, I'm working with 2 connections. First, only read, to show records in a datagrid, and second, r/w, showing selected record in a editable form. When i change clicking in the datagrid, second adodc points to selected record..... it's a unstable, uncomfortable and dirty trick, and creates many problems..... I can't believe this is the only one solution...!!!!

 

[gheist]

Yes - and then you update a row(-set) using primary KEY value

 

[miguelgm]

OK. F****ing M$!!!!. A lot of thanks for your help!!! And pleased to "forum" you!!!