CodeCharge Studio

Editable Grid

Description

Editable Grid form combines the functionality of typical grid and record forms. A grid form is designed to allow browsing of multiple records without the capability of altering the records, while a record form is used to insert, update or delete single records. The Editable Grid brings together the functionality of grid and record forms by allowing the user to browse, insert, update or delete multiple records from the same form. The editable grid can be used to make changes to multiple records using a single operation rather than dealing with each individual record.

The editable grid can be constructed using a builder or by dropping a barebones Editable Grid form onto a page. The builder uses a series of dialogs to guide you through the steps of configuring various options for the Editable Grid that is based on a single database table. You can also use the Visual Query Builder to construct a query that retrieves fields from different related tables.

If you use the Forms tab of the Toolbox to place a barebones Editable Grid onto a page you have to manually configure all the properties of the Editable Grid and add components such as Sorters and Navigator controls that are not included in the initial form. You should use the builder to create the Editable Grid even if you intend to customize it later. It is recommended you only use the barebones form if you want to exercise control over most of the construction of the form.

Structure of the Editable Grid

The structure of an Editable Grid is a hybrid of the structure of a grid form and a record form. The form also contains a title with descriptive text about the contents of the form. The user can change this text or remove it entirely.

  1. An Editable Grid contains an HTML hidden input field called FormState and a template variable called FormScript. These two controls play a role in the internal functioning of the Editable Grid and should not be altered or removed.
  2. It might appear strange that the Editable Grid has two rows reserved for the display of error messages. However, the two rows serve two different purposes. The first error row is used to display error messages that pertain to the entire Editable Grid. Within the HTML mode, this first row is composed of HTML code such as that shown below:
    <!-- BEGIN Error -->
    <tr>
    <td class="ClearErrorDataTD" colspan="2">{Error}</td>
    </tr>
    <!-- END Error -->
  3. The second error row is used to display errors that pertain to particular rows within the Editable Grid. For example if a value in a submitted row does not pass the validation check upon submission, an error will be displayed above the offending row when the form is displayed again. If multiple rows have errors, each row will be preceded with an error row containing the appropriate error text. The HTML code for this second row is of the form:
    <!-- BEGIN RowError -->
    <tr>
    <td class="ClearErrorDataTD" colspan="4">{Error}</td>
    </tr>
    <!-- END RowError -->
  4. Like the grid form, an editable grid has a row containing sorter controls and another row at the bottom that contains the navigator controls.
  5. In addition to the columns for each of the database fields that need to be shown, an editable grid has an extra column that is used to indicate the records that should be deleted. This column contains a single checkbox control which the user can activate before submitting the form so that the corresponding records are deleted.

The bottom row of the editable grid also contains the Submit and Cancel buttons. The Submit button is used to initiate insert, update and delete operations. The Cancel button is used to terminate any current operations based on the contents of the rows within the editable grid. When the form is submitted, the underlying code determines what operation to perform based on the submitted values.

Editable Grid Properties

The properties of an Editable Grid which are displayed under the Data tab of the Properties window are similar to the properties of a record form. An Editable Grid has some unique properties such as Empty Rows and Delete Control as shown below:

Editable Grid Properties

Property Description
Name The name of the form.
Connection The database connection from which information will be retrieved.
Source Type The type of data source or the method used to retrieve the data.
Data Source The database tables, SQL Query or stored procedure from where the data will be retrieved.
Return Page The page the user is directed to after the form has been processed successfully.
Remove Parameters Specify URL or Form parameters that should be removed when the form is submitted.
Convert URL To Specify whether URLs should be automatically converted to absolute URLs or secure URLs for the SSL protocol (https://).
Records Per Page Specify the default number of records that should be displayed per page.
Restricted Specify whether users must be logged in before accessing the form.
Empty Rows Specify the number of empty rows that will be provided at the bottom of the Editable Grid for the purpose of inserting new records.
Allow Insert Specify whether the form can be used to insert new data.
Allow Update Specify whether the form can be used to update existing data.
Allow Delete Specify whether the form can be used to delete existing data.
Delete Control Specify the name of the Checkbox or Hidden control which is used to determine the records that should be deleted.
Preserve Parameters Specify whether Get or Post parameters should be preserved.
Custom Insert Type For forms that require additional configuration for the insert operation, specify whether a table, SQL query or stored procedure will be used.
Custom Insert Specify the table, SQL query or procedure to be used to insert data.
Custom Update Type For forms that require additional configuration for the update operation, specify whether a table, SQL query or stored procedure will be used.
Custom Update Specify the table, SQL query or stored procedure to be used to update data.
Custom Delete Type For forms that require additional configuration for the delete operation, specify whether a table, SQL query or stored procedure will be used.
Custom Delete Specify the table, SQL query or stored procedure to be used to delete data.

Note:  when configuring an editable grid the Data Source property must clearly indicate the primary key of the database table the form is based upon. The primary key must be specified because the editable grid code needs to uniquely identify records. To specify the primary key, click the [...] button next to the Data Source property to open the Data Source window. In the Data Source window, click the Build Query button to open the Visual Query Builder. The Visual Query Builder displays the fields in the current table and the primary key appears in bold. You can designate a field as the primary key by right-clicking on it and selecting the Primary Key option.

Editable Grids Based on Queries

In its simplest form, an Editable Grid is based on a single database table. This means that all the fields within the grid are retrieved from one table. Any other operations such as insert, update or delete are also based on the same table. An Editable Grid can also be based on a join query that retrieves fields from different related tables. For instance, when using the Editable Grid Builder the user can invoke the Visual Query Builder and specify a query based on multiple tables such as that shown below.

The important point to note is that any Editable Grid that is not based on a single database table requires additional configuration in order to support insert, update or delete operations. The SQL commands that are used to perform insert, update and delete operations are restricted to single tables. A select statement can retrieve fields from multiple tables but the insert, update and delete statements can operate only on a single table. All Editable Grids that are not based on a single table must define the Custom Insert, Custom Update and Custom Delete properties in order to support insert, update and delete operations.

DataSource Column Parameters

If an Editable Grid is based on a query or a stored procedure, you have to specify the Custom Insert, Custom Update and Custom Delete properties in order to facilitate the insert, update and delete operations respectively. When you specify the Custom Update and Custom Delete properties you have to provide a Where clause which determines the records that need to be updated or deleted. Unlike an ordinary record form that operates on single records, an Editable Grid deals with multiple records at the same time. This means that when the Editable Grid is submitted for update or delete operations the form submits multiple primary keys corresponding to the records that need to be updated or deleted.

The Where parameter for the Custom update or Custom delete properties cannot simply specify URL or Form parameter types as the source of the primary keys. There is a special parameter type which is designed to deal with the multiple values submitted by an Editable Grid. This is the DataSource Column parameter Type as shown in the screenshot below.

Whenever you specify a Custom Update or Custom Delete operation for an Editable Grid, make sure that the Type of the source parameter is set to DataSource Column.

Note: a Custom Insert operation simply inserts a new record into a table without the need for a where clause. There is no need to specify input parameters for a Custom Insert operation, however, you must specify parameters with Custom Update and Custom Delete operations.

See also


On-line, printable versions and updates