by
right mouse clicking on the column header.Garry Lowther
3rd September 2001
Build 5.00.090 has easy to use functions to bind a SQL statement to a grid to provide:
Virtual unbound recordsets – the data is demand paged from the server
Sortable columns – standard column sorting (right mouse column header click)
Automatic column generation – for prototyping purposes, all columns are generated
The techniques demonstrated here should be used for all grids which can be fully populated from a SQL statement.
The following new functions are now available:
DataWidgets.InitialiseUnboundGrid
(dwGrid As SSDBGrid, sCaption as string)
DataWidgets.PopulateGridFromSQLQuery
(dwGrid As SSDBGrid, sSQL as String)
Note: the word unbound derives from the 3 modes that the data grid can operate:
Add Item – rows are added to the grid 1 at a time like a list box. This is used for small non-SQL oriented data sets.
Bound – the grid is bound to a data control on a form. This is useful when developing prototypes but does not scale to production databases.
Unbound – the grid is populated from a SQL recordset which is demand paged from the server.
A simple example demonstrates how to add an unbound grid to view all companies:
Public Sub Form_AfterLoad()
Set dwGrid =
FormObjectsLocal.ShowForm.GetFieldWidget( _
“EntityName”, “GridFieldName”)
if dwGrid is
nothing then exit sub
With DataWidgets
.InitialiseUnboundGrid (dwGrid), “All
Companies”
.PopulateGridFromSQLQuery (dwGrid), “Select *
From Company”
End With
End Sub
Not
only can the grid now efficiently process very large recordsets, but it also
automatically provides column sorting:
by
right mouse clicking on the column header.
Warning: Column sorting is very generic so you must obey an important rule when writing your SQL query. This rules relates to fields of type TEXT in SQL Server. TEXT fields cannot be sorted unless cast to a VARCHAR data type. For example the JobTitle column should be written as:
SELECT….. Convert(Varchar(64), JobTitle) as JobTitle …. FROM….
This will ensure that sorting the JobTitle column will be efficiently processed.
It is normal to add your own style to columns (width, type etc..) and this should be done using DataWidgets.AddColumnToGrid before calling DataWidgets.PopulateGridFromSQLQuery.