Modifying Data

Editing Data

To edit data in the table, an update session must be started. For this, click the Edit Mode button.

Now you can edit data in the imported table as you usually do it in Excel. Modified cells will be highlighted in olive by default.

Note
You can change all the highlighting colors in the Appearance section of Options.

Adding Rows

The simplest way to add rows to the table is to fill the cells of the new empty row, highlighted with green by default, which is at the end of the table, and then press Enter.

Or add rows to the table as you usually do it in Excel:

1.	Right-click on the number of the row to insert a new row before the selected one.

2.	Click Insert. The new row will be highlighted in yellow by default.

3.	In the newly inserted row, add the required data.

Devart Excel Add-ins underline cells of the new row that require a non-empty value by default. Without values for these columns, the new row cannot be inserted to a data source.

Committing will add the new rows to the table in the data source.

Note
Note that if you enabled data filtering while in Edit Mode, the green empty row for inserting new rows to the table may be filtered out if you haven't included blanks to the filter. In order to avoid this, select blanks in the filter.

Deleting Rows

Delete rows from the table as you usually do it in Excel:

1.	Right-click on the number of the row you want to delete.

2.	Click Delete. The deleted row will be highlighted in dark-yellow by default.

Committing will delete the rows from the table in the data source.

Using Formulas

You can use formulas for imported data. For this you need to perform the following actions:

1.	Import data from your data source to Excel.

2.	Select cells where you want to insert formulas and change their format cells from Text to General.

3.	Enter formulas to these cells.

4.	Start Edit Mode (do not refresh data, otherwise your formulas will be overwritten by data from your data source).

5.	Edit cells, on which formulas are based, and value in imported cells will change.

6.	Commit changes.

Devart Excel Add-ins provide data validation. It means, that when you are modifying or inserting data in a table, the type of entered data and the type of the column specified at the server side are compared and checked whether they mismatch. In other words, if a column has the type NUMBER (integer), it can't contain any text, dates, etc. If a type mismatch is detected, Devart Excel Add-ins will notify you. For example:

The same check is performed for match of data length (number of characters) in cells, date and time formats, etc.

When an imported table contains a column with unique values (e.g. ID), Devart Excel Add-ins checks for duplicates in this column and notifies if one is detected, like on the screen-shot below.

Applying Changes

After all needed changes have been made, click the Commit button. If you don't want to apply these changes, you can also close the Execution window and Rollback changes. After this, all your modifications will be undone.

After applying or rolling back changes, you can continue editing or switch the Edit Mode off.

Note
In the Edit section of Options, there is a Continue after commit or rollback check box for convenience. If selected, the edit session won't be closed after committing changes or rolling them back, and you will be able to continue editing data in the same session.

If you need to modify another table, click the Get Data button. Select another table and modify the generated SQL query if needed. Repeat the above actions to retrieve and modify the required table data.

Checking for Concurrent Changes

Important Note
If concurrency check is enabled in Options, the add-in will check for concurrent changes in the edited table, notify if one is detected, and suggest actions to do with data: rollback or commit changes anyway. In the latter case, your changes will override the concurrent changes made by another person.

If your modifications don't conflict with any other concurrent changes or you commit your changes forcibly, you will see a generated SQL UPDATE query with all your changes.

Error Handling

By default, the generated SQL query is not shown before applying changes. To see the generated query each time before you Commit, go to Devart Excel Add-ins Options and select the corresponding check box in the Error Handling section.

As a result, when the Commit button is clicked, you will see a dialog box, that displays all the INSERT, UPDATE, DELETE statements, generated for the data changes.

For example, the following data changes:

Data Changes

will generate the following SQL queries:

Generated SQL Query

In the appeared Execution dialog box, click Execute to apply changes.

The SQL query is executed on the server side and the data is updated.