Insert/Update using composite unique index

Discussion of issues, suggestions and bugs of Devart Excel Add-ins, our product line for connecting Excel to external data from cloud applications and databases
Post Reply
ebincherian
Posts: 5
Joined: Wed 24 Oct 2018 15:51

Insert/Update using composite unique index

Post by ebincherian » Wed 02 Jan 2019 19:04

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

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Insert/Update using composite unique index

Post by Pinturiccio » Tue 08 Jan 2019 16:34

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Insert/Update using composite unique index

Post by Pinturiccio » Wed 16 Jan 2019 15:37

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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Insert/Update using composite unique index

Post by Pinturiccio » Fri 18 Jan 2019 10:39

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

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Insert/Update using composite unique index

Post by Pinturiccio » Mon 25 Feb 2019 14:53

We have changed the behaviour: now we allow starting Edit Mode if an imported table has at least one not read-only column. However, depending on the data source and imported data, in some cases it can be impossible to submit the changes to the data source.

We will post here when the corresponding build of Excel Add-ins is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Insert/Update using composite unique index

Post by Pinturiccio » Tue 26 Feb 2019 14:49

New build of Devart Excel Add-ins 2.0.86 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=38410

marksluser
Posts: 1
Joined: Sat 19 Feb 2022 18:59

Re: Insert/Update using composite unique index

Post by marksluser » Sat 19 Feb 2022 19:11

I am having the same problem with PostgreSQL.

OS: Windows 10 Pro 21H1 19043.1526
DB: PostgreSQL 14
Excel: Excel for Microsoft 365 MSO (version 2201 Build 16.0.14827.20186) 64 bit
Devart Excel Add-in: Version 2.6.791.0 (.NET 4.7)

In PostgreSQL there is no way I know of to create a unique identifiers in views.
Only tables can have unique indexes or primary keys.

I created a table with a column named "id" that is type integer and is a primary key. I created a view of that table.
I use Devart Excel Add-in to link the database view to an excel spreadsheet. When I enter into edit mode I get:

"Table does not have columns that uniquely identify rows (primary key or non-nullable unique index columns). Data modifications may be applied incorrectly or may be not applied."

How can I specify that the column "id" is a primary key to Devart Excel Add-In ?

Thank you,

-Mark

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: Insert/Update using composite unique index

Post by DmitryGm » Mon 21 Feb 2022 13:57

Unfortunately, Devart Excel Add-ins do not yet have such a feature - to assign key fields manually.
Perhaps we will consider adding the feature in the next releases.

Post Reply