Edit Mode Error Message

[pryan]

Hello,
I have been using Devart Excel Add-ins on a very limited basis for the past few weeks and am trying to really dig into it now for the first time. I am trying to enter edit mode to modify cell values and am getting the following message: "Data cannot be edited because the table does not have columns that uniquely identify rows (primary key or unique index columns)". However, I feel very confident that my data does contain not just one, but multiple columns that can be used to this end and which are defined as indexes by the application from which the data was obtained.

Specifically, I am using a SQL view of the LINEITEM.DAT from Sage 50 US as my data source.

Does anyone have experience troubleshooting this error message?

Thanks!
Phil

[Pinturiccio]

Specifically, I am using a SQL view of the LINEITEM.DAT from Sage 50 US as my data source.

Do you mean http://sagecity.na.sage.com/support_com ... unting_us/?
1. Which Excel Add-in do you use in order to connect to this data source? Is it Excel Add-in for Oracle or may be Excel Add-in for SQL Server or something else?
2. Do you establish a connection in Excel Add-ins, retrieve data, and then get this error after clicking "Edit mode"?

[pryan]

Hi Pinturiccio,

Yes, I am using Sage 50 Accounting US.
I am using the SQL Server Excel Add-in from DevArt Excel Add-ins 1.5.239.0.
Yes, I established a connection, retrieved the data and now am getting this error message when clicking "Edit Mode".

Thanks and please let me know if you need additional info to assist!

Phil

[Pinturiccio]

Devart Excel Add-ins does not support such source as Sage 50 Accounting US. You can find the list of the supported sources here https://www.devart.com/excel-addins/compatibility.html

We will investigate the possibility to support the Sage 50 Accounting US source, but we can't tell any timeframe at the moment.

[pryan]

OK, so just to make sure we are on the same page:
>The raw data is from Sage 50 Accounting US,
>the SQL view drawing from the raw data is in SQL Server,
>and DevArt Excel Add-ins is able to retrieve the data from the SQL Server view, but cannot edit and write back because the raw data came from Sage 50 Accounting US, which is not currently supported?

Would I be assuming correctly that there is no current way to use DevArt to connect directly to the Sage 50 Accounting US .DAT files?

Thanks,
Phil

[Shalex]

We have reproduced the following issue with linked servers:
1) creating a connection to SQL Server
2) choosing "SQL Query" (instead of default "Visual Query Builder") in Import Data Wizard and typing the query:

Code: Select all

select * from Northwind...Categories

As a result, data are successfully imported to Excel sheet
3) clicking Edit Mode to modify data and send them back to the database, modifying some field, pressing Enter, clicking Commit: "Invalid object name 'Categories'.".
The reason of the problem is that Devart Excel Add-ins generates the "Categories" name of object instead of "Northwind...Categories".

We will notify you when the issue is fixed.

JIC: navigate to the Devart menu in Excel > Options > Edit > Error Handling > select the Show SQL option to see the generated SQL query when committing changes.

[Shalex]

The bug with SQL generation when editing tables via linked server is fixed. We have just sent the build with the fix to your email used at the forum.

JIC: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server does not support transactions. You can turn off usage of transactions by Devart Excel Add-ins via Options -> Error Handling -> Use transaction.

[pryan]

Hi Shalex,

Thank you for the effort but the new build did not resolve my error.

I am trying to enter edit mode to modify cell values and am getting the following message: "Data cannot be edited because the table does not have columns that uniquely identify rows (primary key or unique index columns)". However, I feel very confident that my data does contain not just one, but multiple columns that can be used to this end and which are defined as indexes by the application from which the data was obtained.

Specifically, I am using a SQL view of the LINEITEM.DAT from Sage 50 US as my data source.

Can you help me resolve?
Thanks!
Phil

[Shalex]

1. Please send us your test *.DAT file. It should include a test view which represents at least one record for reproducing the issue.
2. You followed the steps described at viewtopic.php?t=33487#p116023, didn't you?