Cell Change Handlers

Cell Change Handlers

DBEdit allows executing server-side stored procedures or SQL codes to handle cell change events.

DBEdit highlights the yellow arrow button if the active table has such handlers:

The simplest way to attach the handler is to create a procedure with the same name and the _change suffix.

For example, the s02.usp_cashbook3_change procedure handles change events of the s02.usp_cashbook3 procedure.

You may use a handler configuration table like xls.handlers or a view to link handlers.

For example:

In this sample, you may see that view_cashbook2 has itself as a change handler. So, DBEdit just commits every change immediately.

The view_cashbook3 view has multiple column-level handlers defined inline.

For example, the debit column handler has the following SQL code in the HANDLER_CODE field:

UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id

It just updates the debit column with a new value.

The usp_cashbook3 procedure has the usp_cashbook3_change handler (this row is optional as the handler has the _change suffix).

The usp_cashbook3_change procedure has the following code:

CREATE PROCEDURE s01.usp_cashbook3_change
    @column_name nvarchar(255)
    , @cell_value nvarchar(255) = NULL
    , @cell_number_value money = NULL
    , @cell_datetime_value date = NULL
    , @id int = NULL
AS
BEGIN

SET NOCOUNT ON

IF @column_name = 'debit'
    UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id
ELSE IF @column_name = 'credit'
    UPDATE s02.cashbook SET credit = @cell_number_value WHERE id = @id
ELSE IF @column_name = 'item_id'
    UPDATE s02.cashbook SET item_id = @cell_number_value WHERE id = @id
ELSE IF @column_name = 'company_id'
    UPDATE s02.cashbook SET company_id = @cell_number_value WHERE id = @id
ELSE IF @column_name = 'account_id'
    UPDATE s02.cashbook SET account_id = @cell_number_value WHERE id = @id
ELSE IF @column_name = 'date'
    UPDATE s02.cashbook SET [date] = @cell_datetime_value WHERE id = @id

END

The procedure has the @id parameter that receives the value of the id column.

If the column contains characters that not supported in parameter names, DBEdit escapes the names.

Also, the procedure uses special parameters populated by DBEdit with context values like the column name, cell value, cell number value, and the cell datetime value.

Below is a complete list of such context parameters.

Note that you may use cell change handlers to check the user's input and notify the user or rollback the changes.

Print a message or raise a warning for the first case, and raise an exception to rollback the changes.

You may see in the first screenshot that the usp_cashbook3 procedure also allows saving changes and deleting/adding rows accordingly.

You may disable this behavior using the DoNotSave and ProtectRows handlers as shown below for the view_cashbook3 view:

Context Parameters

Cell change handlers can use the following context parameters:

Parameter NameValue
@DataLanguage or @data_languageA data language code selected using the Options dialog box
@TableName or @table_nameA qualified name of the active query object like [s02].[usp_cashbook]
@ColumnName or @column_nameThe active column name
@CellValue or @cell_valueThe active cell text value
@CellNumberValue or @cell_number_valueThe active cell number value
@CellDateTimeValue or @cell_datetime_valueThe active cell datetime value
@ChangedCellCount or @changed_cell_countThe total number of changed cells in the current transaction
@ChangedCellIndex or @changed_cell_indexA cell index in the changed cell set starting 1
@TransactionID or @transaction_idA unique GUID of the current transaction
@TransactionStartTime or @transaction_start_timeA UTC start time of the current transaction
@WindowsUserName or @windows_user_nameThe Windows login of the current user
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.