Page 1 of 1

Insert/Update using composite unique index

Posted: Wed 02 Jan 2019 19:04
by ebincherian
Hi All,

Oracle Database 12c
Devart Excel Add-in Version : 1.8.868.0
Microsoft Excel 2016 MSO (16.0.9126.2295) 32-bit


We recently purchased devart license and our goal is enter to/modify data in database table. We setup devart connection to database and was able to select the table and view data. Now if we try to enter new data or edit existing data, we get following error message.

"Data cannot be edited because the table does not have columns that uniquely identify rows (primary key or unique index columns)".

Our data set doesn't have a primary key. So we defined a composite unique index as combination of 5 columns. But we are still getting the same error.

Please let us know if the usage of unique composite index is supported ?


Thanks
Ebin

Re: Insert/Update using composite unique index

Posted: Tue 08 Jan 2019 16:34
by Pinturiccio
We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Re: Insert/Update using composite unique index

Posted: Wed 16 Jan 2019 15:37
by Pinturiccio
We can offer two workarounds to you:
1) When you select your table in the import wizard, switch to the SQL Query tab and add ROWID to a select query. For example, you have this query:

Code: Select all

SELECT Column1, Column2 FROM TableName
then it should be

Code: Select all

SELECT ROWID, Column1, Column2 FROM TableName
After this, you can start Edit Mode.

2) You can create a view in your database which will select all columns from your table plus ROWID. For example, you can create such a view:

Code: Select all

CREATE OR REPLACE VIEW View_Name AS SELECT ROWID AS "RowId", T.* FROM TableName T
Please note that is required to use an alias for ROWID when you create a view.


If you decide to use one of these 2 workarounds, then you will need our internal build in which we supported the possibility to edit user-defined SQL query or views with ROWID.

You can download our internal build at http://devart.com/pub/devartexcel_2_0_60.exe . The internal build is a full-featured Excel Add-ins build and can be used in the same way as a public build.


As for unique index issue, we will investigate the possibility to support the edit mode when all columns in a unique index are not null columns. We will post here when we get any results.

Re: Insert/Update using composite unique index

Posted: Fri 18 Jan 2019 10:39
by Pinturiccio
New build of Devart Excel Add-ins 2.0.62 is available for download now!
It can be downloaded from http://www.devart.com/excel-addins/download.html (trial version) or from Customer Portal (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=38289