ADO Update Question
-
My Environment: MySQL Database, ADO, MSVC6, MS DataGrid. Datagrid is being used to display data from three tables, joined by their primary keys. Problem: User wants to be able to edit the data in the grid itself. When he does, and tried to move to another record, the grid displays the message "Insufficient Key Column Information for updating or refreshing". User is not willing to have me bring up the row data in a dialog to edit. Even Bigger Problem: I need to keep this user happy! Schema: Table_1: ID (AutoNumber, PK), Field_A (Varchar), Field_B (Varchar) Table_2: ID (Autonumber, PK), Field_C (Varchar), Field_D (Integer) Table_3: ID (Autonumber, PK), Field_E (Double) SQL for Grid: SELECT Table_1.ID, Table_1.Field_A, Table_2.Field_C, Table_3.Field_E FROM (Table_1 INNER JOIN Table_2 ON Table_1.ID=Table_2.ID) INNER JOIN Table_3 ON Table_1.ID=Table_3.ID ORDER BY Table_1.Field_A MS KB: There is no duplication of field names except ID. ID is always the primary key. The ID field is not being updated. Field_A is. ADO: I have tried using both adUseClient and adUseServer as the cursor location, for both the connection object and the recordset object. Has not made any difference. Can I set this up so that enough key column information is available for the update? Is this even possible?
-
My Environment: MySQL Database, ADO, MSVC6, MS DataGrid. Datagrid is being used to display data from three tables, joined by their primary keys. Problem: User wants to be able to edit the data in the grid itself. When he does, and tried to move to another record, the grid displays the message "Insufficient Key Column Information for updating or refreshing". User is not willing to have me bring up the row data in a dialog to edit. Even Bigger Problem: I need to keep this user happy! Schema: Table_1: ID (AutoNumber, PK), Field_A (Varchar), Field_B (Varchar) Table_2: ID (Autonumber, PK), Field_C (Varchar), Field_D (Integer) Table_3: ID (Autonumber, PK), Field_E (Double) SQL for Grid: SELECT Table_1.ID, Table_1.Field_A, Table_2.Field_C, Table_3.Field_E FROM (Table_1 INNER JOIN Table_2 ON Table_1.ID=Table_2.ID) INNER JOIN Table_3 ON Table_1.ID=Table_3.ID ORDER BY Table_1.Field_A MS KB: There is no duplication of field names except ID. ID is always the primary key. The ID field is not being updated. Field_A is. ADO: I have tried using both adUseClient and adUseServer as the cursor location, for both the connection object and the recordset object. Has not made any difference. Can I set this up so that enough key column information is available for the update? Is this even possible?
From MSDN: When updating an ActiveX Data Objects (ADO) recordset, an error occurs if the recordset, based on a SHAPE command, joins two tables where the primary key of one table has the same name as a column name of the other table. The following error appears: Run-time error '-2147467259 (80004005)': Insufficient key column information for updating or refreshing. You can try renaming the primary keys.
-
From MSDN: When updating an ActiveX Data Objects (ADO) recordset, an error occurs if the recordset, based on a SHAPE command, joins two tables where the primary key of one table has the same name as a column name of the other table. The following error appears: Run-time error '-2147467259 (80004005)': Insufficient key column information for updating or refreshing. You can try renaming the primary keys.
Actually, I had tried that already, it did not work. I am beginning to think this is something so deep inside Microsoft Cursor engine, that there may not be an easy work-around. The SQL I used was not a SHAPE sql anyway. My solution so far has been to switch to a disconnected recordset, and intercept update notifications from the grid. Then I have to write some code for a Lazy Writer to actually update the database with the changes from the grid.