DataGrid Control

A data grid control displays data in a series of rows and columns.

Client support

Newlook Smartclient

Newlook Server

Use

A data grid control is automatically generated in a form whenever Newlook receives a subfile from the host. It is also possible to create a custom data grid control in Designer or dynamically via a script or macro. Custom data grids can be bound to an external data source or populated manually using one or several data grid methods.

Host generated data grids

When a subfile is received from the host, the Newlook recognition engine attempts to organize its data into columns. It does so by analyzing the relative placement of data and attribute bytes within the subfile. If columns can be clearly identified in the subfile then the ShowColumns property defaults to True and column separators are displayed in the GUI. The resulting columns are represented by the Columns collection. It is possible to reorder columns, change the visibility of columns and add new columns to a host-generated grid. It is not possible to delete any host columns however.

When the recognition engine encounters ambiguities in column recognition, the ShowColumns property defaults to False and columns will not be displayed. Despite this, the recognition engine will attempt to populate the Columns collection with data that most closely represents a logical grid structure, and even though columns are not displayed by default, it is possible to force columns to display by overriding the ShowColumns property to True, either in Designer or via a script or macro. When column recognition is forced on, the recognition engine inserts blank cells, where necessary, to organize the data into logical columns, and maintain grid headings.

When a data grid control is generated from a host subfile, there may be a difference between the total number of identified columns (which is returned by App.ActiveForm.grid.Columns.Count), and the total number of columns displayed in the GUI. This is due to the fact that Newlook will hide certain columns where it determines that these columns are not valid data columns. The columns by default have their Visible property set to False. For this reason it is important to use the count property to determine the total number of columns in a subfile before creating any code that references specific columns or contains looping structures.

It is also worth noting that each row in a data grid control may contain a different number of cells, therefore if you need to return the total number of cells in a specific row, then the grid.ColCount(RowIndex) property must be used. This is particularly relevant when looping through all the cells in a grid.

Inserted data grids

If the grid is bound to an external data source, it is typically bound to a single table containing no relationships. In this case, the data appears in simple rows and columns, as in a spreadsheet. The rows and columns displayed are dictated by the external data source.

It is also possible to use the AddRecord, DataBind, InsertColumn, InsertColumnArray and InsertRow methods to populate an inserted data grid.

Material skin considerations

When the material skin is enabled in your solution, data grids will be rendered with material styling. The most noticeable difference is a cleaner appearance and more spacing between elements in the grid (where possible). Entry capable cells will render with an underline when given focus.

Material skin	Classic skin

Common DataGrid Tasks

Inserting a data grid control

Open the relevant screen in Design () mode.
Select Insert | DataGrid () from the main menu, then use your cursor to draw the control area on the screen.
Use the Data properties to bind the control to an external data source (see section below).
Specify a Name for the control. This name will be used to dynamically set any properties or call methods for the grid.
Configure Appearance properties to specify colors and visual behavior for the grid.

Binding a data grid control to an external data source

If you are linking your DataGrid control to an external data source you will need to set various Data properties.

The first property you will need to specify is the Server, this determines the type of server that you are connecting to, either DDM for an IBMi data source or ADO for an external source.

Once this has been done you will need to establish a connection with the data source via the ConnectionString property. Clicking the prompt button next to this property will open a wizard which will generate a semi-colon separated name/value pair that specifies that database and all its relevant connection properties. The contents of the connection string can vary, depending upon the data source or server that is used for the connection.

The final step is specifying the Source property for the grid. Typically this is a table name or SQL statement for an ADO data source, or a file or member name for DDM sources.

Adding and removing columns

It is not possible to remove columns from data grids that have been generated automatically from host subfiles. In order to hide the display of these columns turn the Visible property of the column to False. It is however possible to add columns to a host generated grid via the Add button in the columns collection editor or the InsertColumn or InsertColumnArray DataGrid methods at Run-time.

To add or remove columns from an inserted data grid control, use the Add and Remove buttons in the columns collection editor or use the InsertColumn, InsertColumnArray and DeleteColumn DataGrid methods at Run-time.

Sorting

A datagrid control can be sorted manually or dynamically. To allow users to manually sort the contents of a data grid control by a specific column, ensure that the AllowSort property for the relevant column objects is set to True. It is also possible to dynamically sort the datagrid by using the SortColumn method at run-time.

Note that for host-generated data grids, it is only possible to sort the data that is currently loaded in the grid.

Referencing individual cells in the datagrid

The contents of a data grid control are represented as an array of cells.

A Grid control has a specific number of rows however the number of cells (and hence columns) in a grid may vary from row to row.

Any given cell can be referenced using the following syntax:

App.ActiveForm.DatagridControl.Cells(rowIndex, CellIndex)

App.ActiveForm.DatagridControl.Rows(rowIndex).Cells(cellIndex)

App.ActiveForm.DatagridControl.Columns(colIndex).Cells(rowIndex)

The first two methods use the Cell Index within a specified row to reference the cell. In some grids the total number of cells may very from row to row therefore the same Cell Index on any two rows does not necessarily mean that those cells are in the same column. When you need to ensure that the returned cell is within a specified column, use the columns(colindex).Cells(rowIndex) syntax. You can then use the ColCount(n) property, where N is the row index, to determine the total number of columns for the specified row.

With grids bound to an external data source, the number of cells in each row is usually consistent with the number of columns, so in these cases, a column index and a cell index are the same thing.

To return the contents of the active datagrid cell use the following syntax:

App.ActiveForm.DataGridControl.cells(rowIndex, ColIndex).Text

RowIndex returns the currently active row while ColIndex returns the currently active cell index within that row. Note that you need to specify the Text property also as leaving this out will return the cell object itself rather than it's contents.

Datagrid column display

When a datagrid control is generated from a host subfile the Newlook recognition engine attempts to organize data into logical columns. If columns are easily identified then the ShowColumns property of the grid defaults to True and column separators are displayed. Where the Newlook recognition engine cannot identify any columns in the data grid then the ShowColumns property is set to False and columns are not displayed. Items that may affect the recognition engine's ability to detect columns include: column headers that do not line up logically with column data, differing number of columns per row and attribute byte placement. If a column has been automatically generated and does not contain any data then Newlook will default the Visible property of this column to False.

Properties

Newlook Smartclient | Newlook Server | Deprecated | Obsolete

Property	Client	Description
AllowDrop		Gets or sets whether the object allows drag/drop support. Newlook Smartclient-only property.
AutoFitColumns		Gets or sets whether columns are auto-sized to occupy all available horizontal space in the grid.
BackColorRepeat		Gets or sets the number of rows to repeat before alternating the background color.
BlockSize		Gets or sets the number of records to request when requesting records from OpenLook.
CaptionBackColor		Gets or sets the background color of the grid's caption bar.
CaptionForeColor		Gets or sets the color of text in the grid's caption bar.
CaptionText		Gets or sets the text to be shown in the grid's caption bar.
CCSID		Gets or sets the Coded Character Set Identifier (CCSID), which defines the control's code page and character set.
Cells		Returns the specified cell in the grid control.
ColCounts		A collection consisting of the number of columns for each row of the grid. Read only.
ColIndex		Returns the index of the currently active column.
Column		The green screen column coordinate that marks the beginning of the object. Read only.
Columns		Returns the columns collection, a collection of all the grid's columns.
CommandType		Gets or sets the command type in the source object (ADO only).
ConnectionString		Returns the connection string used to establish a connection to a data source.
CssClass		Sets or returns the CSS class(es) of the control in Newlook Server.
CursorLocation		Returns the location of the cursor service for a recordset (ADO only).
CursorType		Returns the cursor type for the recordset (ADO only).
DataAltBackColor		Gets or sets the background color of alternate read-only cells in the grid.
DataAltForeColor		Gets or sets the foreground color of alternate read-only cells in the grid.
DataBackColor		Gets or sets the background color of read-only cells in the grid.
DataForeColor		Gets or sets the foreground color of read-only cells in the grid.
DefaultOption		Gets or sets the default context menu option for the grid.
Droppedfiles		Returns a collection of files dropped onto the object.
DroppedFilesCell		Returns the index of the datagrid cell into which the file was dropped. Read-only.
Enabled		Gets or sets whether the grid can respond to user input.
EntryBackColor		Gets or sets the background color of entry capable grid cells.
EntryForeColor		Gets or sets the foreground color of entry capable grid cells.
FoldCount		Gets or sets the number of host data rows within a single data grid row.
FontBold		Gets or sets whether text should be displayed in a bold font.
FontItalic		Gets or sets whether text should be displayed in an italic style.
FontName		Gets or sets the name of the font family used to display text.
FontSize		Gets or sets the font point size used to display text.
FontUnderline		Gets or sets whether text should be displayed in an underlined style.
FormControlsCollectionIndex		Gets or sets the grid's controls collection index.
HeadingBackColor		Gets or sets the background color of the column headers.
HeadingForeColor		Gets or sets the foreground color of the column headers.
HeadRows		Returns the number of header rows in the data grid control. Read-only.
Height		Returns or sets the height of the object's size in pixels at default size, which is the object's size at design-time.
ImageList		Gets or sets the name of the image list associated with this control.
LastOption		Returns the last option selected by the user withe data grid control context menu. Read-only.
LastScroll		Returns the last scrolling action undertaken by the user. Read-only.
Left		Determines the distance, in pixels, between the left edge of the control and the left edge of its form or parent container at default size, which is the object's size at design-time.
LoadAllRecords		Gets or sets whether all records should be requested from OpenLook when loading the screen. Server-only.
LockType		Returns the type of lock placed on records in a Recordset during editing (ADO only).
Marker		Gets or sets the text of the grid's host subfile maker.
Name		Returns the name used to identify this control.
OnAllRecordsLoaded		Gets or sets the name of the macro or script to be run when the AllRecordsLoaded event occurs.
OnBeginEdit		Gets or sets the name of a macro or script to be run when the BeginEdit event occurs.
OnBOF		Gets or sets the name of a macro or script to be run when the BOF event occurs.
OnChange		Gets or sets the name of a macro or script to be run when the Change event occurs.
OnClick		Gets or sets the name of a macro or script to be run when the Click event occurs.
OnColumnResize		Gets or sets the name of a macro or script to be run when the ColumnResize event occurs.
OnDblClick		Gets or sets the name of a macro or script to be run when the DblClick event occurs.
OnDrop		Gets or sets the name of a macro or script to be run when the Drop event occurs.
OnEndEdit		Gets or sets the name of a macro or script to be run when the EndEdit event occurs.
OnEOF		Gets or sets the name of a macro or script to be run when the EOF event occurs.
OnGotFocus		Gets or sets the name of a macro or script to be run when the GotFocus event occurs.
OnLoadRecord		Gets or sets the name of a macro or script to be run when the LoadRecord event occurs.
OnLostFocus		Gets or sets the name of a macro or script to be run when the LostFocus event occurs.
OnOption		Gets or sets the name of a macro or script to be run when the Option event occurs.
OnScroll		Gets or sets the name of a macro or script to be run when the Scroll event occurs.
OnShowOptions		Gets or sets the name of a macro or script to be run when the ShowOption event occurs.
OnSort		Gets or sets the name of a macro or script to be run when the Sort event occurs.
OptionColumn		Gets or sets the name of the columns to which grid options should be applied.
Options		Gets or sets the options to be displayed in the grid's context menu.
ReadOnly		Gets or sets the read-only status of the control.
Row		The green screen row coordinate that marks the beginning of the object. Read only. Host generated controls only.
RowCount		Returns the total number of rows in the data grid control. Read only.
RowIndex		Returns the active row in the data grid control.
Rows		Returns the Rows Collection. Read Only.
Server		Gets or sets the Server type of the external data source.
ShowCaption		Gets or sets whether the caption bar of the grid is visible.
ShowColumnHeaders		Gets or sets whether grid header cells are displayed.
ShowColumns		Gets or sets whether data is displayed in columns.
ShowHorzScrollBar		Gets or sets whether the horizontal scroll bar is displayed within the grid.
ShowProgressBar		Gets or sets whether the progress bar is displayed within the grid while data loads.
ShowVertScrollBar		Gets or sets whether the vertical scroll bar is displayed within the grid.
Source		Gets or sets the name of the data source.
TabIndex		Returns a value corresponding to the control's tab order.
TabStop		Gets or sets whether the Tab key can be used to set focus to the control.
Tag		Gets or sets additional user-defined information for a control.
Top		Determines the distance, in pixels, between the top edge of the control and the top edge of its form or parent container at default size, which is the object's size at design-time.
Type		Returns the control's type. Read only.
UseHostColors		Gets or sets whether colors in the grid are derived from the host (in conjunction with Settings) or the control's own color properties.
Variable		Gets or sets the name of the variable that stays synchronized with the contents of the control.
Visible		Gets or sets whether the control is visible or hidden.
Width		Gets or sets the width of the object in pixels at default size, which is the object's size at design-time.

Methods

Newlook Smartclient | Newlook Server | Deprecated | Obsolete

Property	Client	Description
AddRecord		Adds a new record to the DataGrid control.
Clear		Clears the contents of the DataGrid control.
DataBind		Creates a binding between a server control property of the Data Source.
DataUnbind		Removes a binding between a server control property of the Data Source.
DeleteColumn		Deletes a column from columns collection of the DataGrid object.
DeleteRecord		Deletes a record from the DataGrid.
DeleteRow		Deletes a row from the DataGrid.
EnsureVisible		Ensures that the specified item is visible within the control, scrolling the control, if necessary.
GetFirstVisibleColumn		Returns a reference to the first visible Column in the DataGrid object.
GetFirstVisibleRow		Returns a reference to the first visible row of the DataGrid object.
InsertColumn		Adds a column to the columns collection of the DataGrid object.
InsertColumnArray		Adds a column to the columns collection of the DataGrid object and populates the cells of the column.
InsertRow		Inserts a new row in the DataGrid.
Refresh		Forces a repaint of the object.
SearchColumn		Searches each cell in the specified column for a given string.
SetActiveCell		Specifies the cell that will be given focus.
SortColumn		Sorts a DataGrid's contents by the column specified.

Examples

The following example loops through the cells in a host-generated grid:

VB SCRIPT:

'Obtain maximum row count for the grid

GridRowCount = App.ActiveForm.Controls.grdActiveJobs.Rowcount

'Loop through each cell for each row of the grid

For GridRowIndex = 0 to GridRowCount -1

'Obtain number of cells in the current row and loop through them

GridColumnCount = App.ActiveForm.Controls.grdActiveJobs.ColCount(GridRowIndex)

For GridColumnIndex = 0 to GridColumnCount -1

GridCellText = App.ActiveForm.Controls.grdActiveJobs.Cells(GridRowIndex,GridColumnIndex).Text

next

GridColumnIndex = 0

JSCRIPT:

//Obtain maximum row count for the grid

var GridRowCount = App.ActiveForm.Controls.grdActiveJobs.Rowcount;

//Loop through each cell for each row of the grid

for(RowIndex=0; RowIndex<GridRowCount; RowIndex++)

{

//Obtain number of cells in the current row and loop through them

var GridColumnCount = App.ActiveForm.Controls.grdActiveJobs.ColCount(RowIndex);

for(ColIndex=0; ColIndex<GridColumnCount;ColIndex++)

{

GridCellText = App.ActiveForm.Controls.grdActiveJobs.Cells(RowIndex,ColIndex).Text;

}

}

This example uses the DataBind method to dynamically bind an inserted DataGrid control called grdMyGrid to an external access database:

VB SCRIPT:

'Set Variables that will be used to specify the connection properties

ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=MS Access Database;DBQ=@test/customer.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

ServerType = nlDBServerADO

SourceName = "Customer"

CommandType = nlDBCmdTableDirect

LockType = nlDBLockReadOnly

CursorType = nlDBOpenKeyset

'Use the above variables and set the DataGrid properties at runtime

App.SetValue "grdMyGrid.ConnectionString", ConnectString

App.SetValue "grdMyGrid.Server", ServerType

App.SetValue "grdMyGrid.Source", SourceName

App.SetValue "grdMyGrid.CommandType", CommandType

App.SetValue "grdMyGrid.LockType", LockType

App.SetValue "grdMyGrid.CursorType", CursorType

'Bind the DataGrid

grdMyGrid.DataBind

JSCRIPT:

//Set Variables that will be used to specify the connection properties

var control = App.ActiveForm.grdMyGrid;

var ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=MS Access Database;DBQ=@test/customer.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;";

var ServerType = nlDBServerADO;

var SourceName = "Customer";

var CommandType = nlDBCmdTableDirect;

var LockType = nlDBLockReadOnly;

var CursorType = nlDBOpenKeyset;

//Use the above variables and set the DataGrid properties at runtime

control.Server = ServerType;

control.ConnectionString = connectString;

control.Source = SourceName;

control.CommandType = CommandType;

control.LockType = LockType;

control.CursorType = CursorType;

//Bind the DataGrid

control.DataBind();

DataGrid Control

Client support

Use

Host generated data grids

Inserted data grids

Material skin considerations

Inserting a data grid control

Binding a data grid control to an external data source

Adding and removing columns

Sorting

Referencing individual cells in the datagrid

Datagrid column display

VB SCRIPT:

JSCRIPT:

VB SCRIPT:

JSCRIPT:

See Also