method Grid.PutItems (Items as Variant, [Parent as Variant])
Adds data to the control from a SafeArray containing numbers, strings, dates, or nested SafeArrays of numbers, strings, and dates, positioning them as child items of the specified parent item

TypeDescription
Items as Variant An array that control uses to fill with. The array can be one or two- dimensional. If the array is one-dimensional, the control requires one column being added before calling the PutItems method. If the Items parameter indicates a two-dimensional array, the first dimension defines the columns, while the second defines the number of items to be loaded. For instance, a(2,100) means 2 columns and 100 items.

For instance:

  • PutItems Array("Item 1", "Item 2", "Item 3"), adds the rows at the end of the list
  • PutItems Array("Root", Array("Child 1", "Child 2")), adds data in a hierarchical structure, at the end of the list
  • PutItems rs.GetRows(), appends data from a recordset using the GetRows method of the Recordset
  • PutItems rs.GetRows(10), inserts the first 10 records from a Recordset using the GetRows method, at the end of the list

where GetRows() method in ADO retrieves multiple records from a Recordset object and stores them in a two-dimensional array.

Parent as Variant Indicates one of the following:
  • missing, empty or 0 {number}, specifies that the data(Items) is being appended (added to the end of the list)
  • a long expression,  that specifies the handle of the item where the array is being inserted
  • a string expression of of "parent;IDColumn;ParentIDColumn" format, where, 'parent' denotes the handle of the item where the data is being inserted, 'IDColumn' refers to the index of the column containing row identifiers, and 'ParentIDColumn' indicates the index of the column containing identifiers of parent rows. This way, you can insert data hierarchically using parent-id relationship. A parent-id relationship is a way of organizing data in a hierarchical structure where each element (or "child") is associated with a parent element. Please be aware that the rows of the data are inserted as they were provided by the Items parameter. Therefore, it is important that the data provided be sorted by the IDColumn so that the parent row referred to by the ParentIDColumn value is already present and can be used to insert the current row as a child of it.

For instance:

  • PutItems Array("Item 1", "Item 2", "Item 3"), Items.ItemByIndex(2), inserts the rows as children of the item with index 2
  • PutItems Array("Root", Array("Child 1", "Child 2")), Items.FirstVisibleItem, Inserts data as a hierarchical structure, placing it as a child of the first visible item
  • PutItems rs.GetRows(), Items.ItemByIndex(0), inserts the records from the recordset using the GetRows method of the Recordset, placing them as children of the item with index 0
  • PutItems rs.GetRows(), ";0;3", inserts the records from the recordset using the GetRows method of the Recordset, utilizing parent-child relationships. The first column (index 0) contains the identifiers of the rows, while the fourth column (index 3) contains the keys of the parent rows.

where GetRows() method in ADO retrieves multiple records from a Recordset object and stores them in a two-dimensional array.

The PutItems method loads items from a safe array. The PutItems method may raise one of the following exceptions:

The PutItems method performs: 

  1. Insertion Order: The data is inserted into the system in the same order as it is provided by the Items parameter. This means that the sequence of rows in the Items parameter directly affects how the data is inserted.
  2. Sorting Requirement: To ensure correct insertion, it's crucial that the data is sorted by the IDColumn (when the Parent parameter is of "parent;IDColumn;ParentIDColumn" format). This sorting ensures that parent rows are inserted before their corresponding child rows.
  3. Parent-Child Relationship: The sorting ensures that when a row refers to a parent row using the ParentIDColumn  value (when the Parent parameter is of "parent;IDColumn;ParentIDColumn" format). The parent row is already present in the control. This allows the current row to be inserted as a child of the parent row without encountering errors or inconsistencies.

In essence, by sorting the data appropriately, you establish a clear hierarchy where parent rows are inserted before child rows, maintaining the integrity of the parent-child relationships within the dataset.

For instance, let's say we have the following data:

 EmployeeID  EmployeeName  DepartmentID  ParentID
 1  John  101  
 2  Alice  102  1
 3  Bob  101  1
 4   Sarah  102  1
 5  Emma  101  2
 6  Mike  102  2

Each row represents an employee.

Having this data organized into a two-dimensional array, the statement PutItems d loads it as a flat table:

whereas PutItems d, ";0;3" loads it as a tree structure:

where d is an array as defined next: 

Dim d(3, 5) As Variant
d(0, 0) = "1": d(1, 0) = "John": d(2, 0) = "101": d(3, 0) = ""
d(0, 1) = "2": d(1, 1) = "Alice": d(2, 1) = "102": d(3, 1) = "1"
d(0, 2) = "3": d(1, 2) = "Bob": d(2, 2) = "101": d(3, 2) = "1"
d(0, 3) = "4": d(1, 3) = "Sarah": d(2, 3) = "102": d(3, 3) = "1"
d(0, 4) = "5": d(1, 4) = "Emma": d(2, 4) = "101": d(3, 4) = "2"
d(0, 5) = "6": d(1, 5) = "Mike": d(2, 5) = "102": d(3, 5) = "2"

Use the GetItems method to get a safe array with the items in the control. The PutItems method fires AddItem event for each item added to Items collection. Use the Items property to access the items collection. Use the ConditionalFormats method to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.

The following VB6 sample loads a flat array to a single column control (and shows as in the following picture ):

With Grid1
    .BeginUpdate
    .Columns.Add "Column 1"
    .PutItems Array("Item 1", "Item 2", "Item 3")
    .EndUpdate
End With

or similar for /NET Assembly version:

 With Exgrid1
    .BeginUpdate()
    .Columns.Add("Column 1")
    .PutItems(New String() {"Item 1", "Item 2", "Item 3"})
    .EndUpdate()
End With

The following VB6 sample loads a hierarchy to a single column control (and shows as in the following picture ):

With Grid1
    .BeginUpdate
        .LinesAtRoot = exLinesAtRoot
        .Columns.Add ""
        .PutItems Array("Root 1", Array("Child 1.1", Array("Sub Child 1.1.1", "Sub Child 1.1.2"), "Child 1.2"), "Root 2", Array("Child 2.1", "Child 2.2"))
    .EndUpdate
End With

or similar for /NET Assembly version:

With Exgrid1
    .BeginUpdate()
    .LinesAtRoot = exontrol.EXGRIDLib.LinesAtRootEnum.exLinesAtRoot
    .Columns.Add("")
    .PutItems(New Object() {"Root 1", New Object() {"Child 1.1", New String() {"Sub Child 1.1.1", "Sub Child 1.1.2"}, "Child 1.2"}, "Root 2", New String() {"Child 2.1", "Child 2.2"}})
    .EndUpdate()
End With

The following VB6 sample loads a list of items, in a three columns control ( as shown in the following picture ):

Dim v(2, 2) As String
v(0, 0) = "One": v(0, 1) = "Two": v(0, 2) = "Three"
v(1, 0) = "One": v(1, 1) = "Two": v(1, 2) = "Three"
v(2, 0) = "One": v(2, 1) = "Two": v(2, 2) = "Three"

With Grid1
    .BeginUpdate
    .Columns.Add "Column 1"
    .Columns.Add "Column 2"
    .Columns.Add "Column 3"
    
    .PutItems v
    .EndUpdate
End With

The following VB6 sample loads a list of items, in a three columns control ( as shown in the following picture ):

Dim v(2, 2) As String
v(0, 0) = "One": v(0, 1) = "Two": v(0, 2) = "Three"
v(1, 0) = "One": v(1, 1) = "Two": v(1, 2) = "Three"
v(2, 0) = "One": v(2, 1) = "Two": v(2, 2) = "Three"

With Grid1
    .BeginUpdate
    .Columns.Add "Column 1"
    .Columns.Add "Column 2"
    .Columns.Add "Column 3"
    
    .Items.AddItem "Root"
    
    .PutItems v, .Items.FirstVisibleItem
    .EndUpdate
End With

The following VB sample loads the collection of records from an ADO recordset: 

Dim rs As Object
Const dwProvider = "Microsoft.Jet.OLEDB.4.0" ' OLE Data provider
Const nCursorType = 3 ' adOpenStatic
Const nLockType = 3 ' adLockOptimistic
Const nOptions = 2 ' adCmdTable
Const strDatabase = "D:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB"

'Creates an recordset and opens the "Employees" table, from NWIND database
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Employees", "Provider=" & dwProvider & ";Data Source= " & strDatabase, nCursorType, nLockType, nOptions
With Grid1
    .BeginUpdate

        .ColumnAutoResize = False
        .MarkSearchColumn = False
        .DrawGridLines = True
        ' Adds a column for each field found
        With .Columns
            Dim f As Object
            For Each f In rs.Fields
                .Add f.Name
            Next
        End With

        ' Loads the collection of records
        .PutItems rs.GetRows()

        'Changes the editor of the "Photo" column
        .Columns("Photo").Editor.EditType = PictureType
     .EndUpdate
End With

The following C++ sample loads records from an ADO recordset, using the PutItems method:

#include "Items.h"
#include "Columns.h"
#include "Column.h"


#pragma warning( disable : 4146 )
#import <msado15.dll> rename ( "EOF", "adoEOF" )
using namespace ADODB;


_RecordsetPtr spRecordset;
if ( SUCCEEDED( spRecordset.CreateInstance( "ADODB.Recordset") ) )
{
	// Builds the connection string.
	CString strTableName = "Employees", strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
	CString strPath = "D:\\Program Files\\Microsoft Visual Studio\\VB98\\NWIND.MDB";
	strConnection += strPath;
	try
	{
		// Loads the table
		if ( SUCCEEDED( spRecordset->Open(_variant_t( (LPCTSTR)strTableName ), _variant_t((LPCTSTR)strConnection), adOpenStatic, adLockPessimistic, NULL ) ) )
		{
			m_grid.BeginUpdate();
			m_grid.SetColumnAutoResize( FALSE );
			CColumns columns = m_grid.GetColumns();
			for ( long i = 0; i < spRecordset->Fields->Count; i++ )
				columns.Add( spRecordset->Fields->GetItem(i)->Name );
			COleVariant vtMissing; V_VT( &vtMissing ) = VT_ERROR;
			m_grid.PutItems( &spRecordset->GetRows(-1), vtMissing );
			m_grid.EndUpdate();
		}
	}
	catch ( _com_error& e )
	{
		AfxMessageBox( e.Description() );
	}
}

The sample uses the #import statement to import ADODB recordset's type library. The sample enumerates the fields in the recordset and adds a new column for each field found. Also, the sample uses the GetRows method of the ADODB recordset to retrieves multiple records of a Recordset object into a safe array. Please consult the ADODB documentation for the GetRows property specification.