it-partners.com Loading itCombo From a Text File

Support

Loading itCombo From a Text File

Introduction

itCombo can be loaded from a text file with just a few lines of code, and populating multiple columns is easy!

In this tutorial we'll review traditional methods to load a VB combo, and compare them to the functionality that itCombo provides.

Table of Contents

1. Traditional methods
2. Loading itCombo
3. How it works
4. Loading multiple columns
5. Tidying up
6. Measuring the performance benefit

Imagine a text file named "countries.txt" containing the following:

Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
Argentina
Armenia
Aruba
Ascension Islands
Australia
Australian External Territories
Austria
Azerbaijan
Azores
Bahamas
Bahrain
Bangladesh
Barbados
Belarus
…

Traditional methods

The traditional method to load this data into a standard VB combo is:

Private Sub LoadCombo()
  Dim sItem As String
  Open App.Path & "\countries.txt" For Input As #1
  Do While Not EOF(1)
    Line Input #1, sItem
    If Len(sItem) <> 0 Then
      cmbCombo.AddItem sItem
    End If
  Loop
  Close #1
End Sub

There is also another traditional but faster method using SendMessage (Windows API):

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As Any) As Long
Private Const CB_ADDSTRING As Long = &H143
Option Explicit
Private Sub LoadCombo()
  Dim hCombo As Long
  Dim sItem As String
  hCombo = cmbCombo.hWnd
  Open App.Path & "\countries.txt" For Input As #1
  Do While Not EOF(1)
    Line Input #1, sItem
    ' The Len(sItem) check is recommended as a zero length string will cause a fatal error
    If Len(sItem) <> 0 Then
      SendMessage hCombo, CB_ADDSTRING, 0, ByVal sItem
    End If
  Loop
  Close #1
End Sub

^ top

Loading itCombo

Just like the standard combo in VB, itGrid provides an AddItem method. Unlike the standard combo in VB, itGrid doesn’t recognise CB_ADDSTRING. That’s because itGrid provides three super fast methods for loading data: FillFromArray, FillFromDataSource and FillFromString.

In this tutorial we’ll use FillFromString:

Private Sub LoadCombo()
  Open App.Path & "\countries.txt" For Input As #1
  If EOF(1) = False Then
    itcCombo.FillFromString "0", 0, Input$(LOF(1), 1), , , , vbCrLf
  End If
  Close #1
End Sub

^ top

How it works

The code fragment:

Input$(LOF(1), 1)

is used to pass the entire contents of the source file as one string.

The FillFromString method provides eight parameters. For the purpose of this tutorial we’ll examine five of them (the other three are for use inside itGrid).

Name Description

Columns A string expression specifying the columns to be created. See Remarks for further details.

ComboField The field that is to be used in the edit portion of the combo.

Items A string expression containing the item(s) to add.

RowDelimiter The row delimiter to be used. If not specified then CARRIAGE RETURN (vbCr) is used.

ColDelimiter The column delimiter to be used. If not specified then TAB (vbTab or Chr$(9)) is used.

Let’s look more closely at the following fragment:

itcCombo.FillFromString "0", 0, Input$(LOF(1), 1), , , , vbCrLf

The Columns parameter is used to suggest which columns found in the source string should be loaded. It can also suggest individual column width, alignment, header and data type (more on this later…)

The ComboField parameter suggests which column (if more than one columns are loaded) is to be used with the edit portion of itCombo. When multiple columns exist and the user selects an item from the list portion, itGrid needs to know from which column it should retrieve the data that is displayed in the edit portion. If only one column is loaded then this parameter is always 0 (zero).

The Items parameter contains the delimited text to be loaded.

The RowDelimiter parameter is used to suggest the character(s) used to delimit rows in the source string. In a typical text file this is always vbCrLF (CARRIAGE RETURN + LINE FEED).

The term "row" can be confusing when working with combo controls. Standard combos consider each piece of data in the list portion of a combo to be an "item" (hence the methods AddItem and ItemData). itCombo retains this term for familiarity purposes, and uses the term row as it is capable of storing columns and rows of items.

The ColDelimiter parameter is used to suggest the character(s) used to delimit columns in the source string. This depends on the source file. Common column delimiters are the pipe "|" and TAB characters. In the above example, it is not specified as the text file contains one column only, and therefore no column delimiters.

As you can see, the FillFromString method makes itCombo very easy to load.

^ top

Loading multiple columns

Imagine now that our source file contains two columns of data: country names and telephone prefixes.

The FillFromString method is capable of loading just the country names column, and both columns.

This code fragment loads just the countries column:

itcCombo.FillFromString "0", 0, Input$(LOF(1), 1), , , , vbCrLf, "|"

"|" is now provided in the ColDelimiter parameter.

This code fragment loads both:

itcCombo.FillFromString "0|1", 0, Input$(LOF(1), 1), , , , vbCrLf, "|"

^ top

Tidying up

While this tutorial is not about presentation it can’t hurt to tidy up itCombo a little bit.

Quite often text files contain a final carriage return. itCombo interprets this as another row (item).

A simple method to remove this additional item is:

With itcCombo
  If .ListCount <> 0 Then
    If .ItemData(0, .ListCount - 1) = vbNullString Then
      .RemoveItem .ListCount - 1
    End If
  End If
End With

If your file cannot contain a final carriage return, or you have removed it manually, this code is unnecessary. Generally it’s a good idea to use this code just in case.

Sorting itCombo after populating is a simple way to ensure that the data is always in the correct order (again, just in case).

Sorting can also be very beneficial if you will be performing frequent lookups using the Find method. When itCombo sorts it creates an index for the ComboField column. If itCombo is asked to locate an item in an unsorted list of 1000 items it might have to compare all 1000 items before a match is made. Using an internal index itCombo will only perform 10 comparisons at most! More will be posted about this in another tutorial.

The following code sets the string comparison method for the ComboField to binary (recommended if case sensitivity is not required) and sorts itCombo.

With itcCombo
.FieldStringCompareMethod(0) = itBinary
.Sorted = True
End With

String comparison is only performed when the data type of the column is string. The Items parameter can be used to suggest the data type that should be used (when not provided string is used).

Using the Columns parameter we can modify the appearance of the columns in the list portion. The following fragment sets the width of the columns to 2400 twips and 1800 twips, and specifies a column header for each column:

itcCombo.FillFromString "0;2400;;Country|1;1800;;Telephone Prefix", 0, Input$(LOF(1), 1), , , , vbCrLf, "|"

The final code to load itCombo is:

Private Sub LoadCombo()
  Open App.Path & "\countries.txt" For Input As #1
  If EOF(1) = False Then
    itcCombo.FillFromString "0;2400;;Country|1;1800;;Telephone Prefix", 0, Input$(LOF(1), 1), , , , vbCrLf, "|"
  End If
  Close #1
  With itcCombo
    If .ListCount <> 0 Then
      If .ItemData(0, .ListCount - 1) = vbNullString Then
        .RemoveItem .ListCount - 1
      End If
    End If
    .FieldStringCompareMethod(0) = itBinary
    .Sorted = True
  End With
End Sub

itCombo now looks like this:

^ top

Measuring the performance benefit

To compare itCombo performance against a standard VB combo we’ll use Windows API with the VB combo (giving the VB combo every chance to compete).

Firstly, we’ll need a reasonably large file to accurately identify the difference. The following code is used to create a file containing 100,000 items (approx. 782kb):

Dim lLoop As Long
Open App.Path & "\temp.txt" For Output As #1
For lLoop = 100000 To 199999
Print #1, CStr(lLoop)
Next lLoop
Close #1

Assumptions:

No design-time properties have been set

The code to test the VB combo is:

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As Any) As Long
Private Const CB_ADDSTRING As Long = &H143
Option Explicit
Private Sub TestCombo()
  Dim hCombo As Long
  Dim sItem As String
  Dim lTemp As Long
  hCombo = cmbCombo.hWnd
  Open App.Path & "\temp.txt" For Input As #1
  lTemp = GetTickCount
  Do While Not EOF(1)
    Line Input #1, sItem
    ' The Len(sItem) check is recommended as a zero length string will cause a fatal error
    If Len(sItem) <> 0 Then
      SendMessage hCombo, CB_ADDSTRING, 0, ByVal sItem
    End If
  Loop
  lTemp = (GetTickCount - lTemp)
  Close #1
  MsgBox lTemp
End Sub

The code to test itCombo is:

Private Declare Function GetTickCount Lib "kernel32" () As Long
Option Explicit
Private Sub TestCombo()
  Dim lTemp As Long
  Open App.Path & "\temp.txt" For Input As #1
  lTemp = GetTickCount
  If EOF(1) = False Then
    With itcCombo
      .FillFromString "0", 0, Input$(LOF(1), 1), , , , vbCrLf
      If .ListCount <> 0 Then
        If .ItemData(0, .ListCount - 1) = vbNullString Then
          .RemoveItem .ListCount - 1
        End If
      End If
    End With
  End If
  lTemp = (GetTickCount - lTemp)
  Close #1
  MsgBox lTemp
End Sub

The result for the VB combo is:

OS Microsoft Windows 2000 Professional

CPU Pentium III 700 MHz

Memory 256 mb

Average Time 4.97 seconds (over 10 tests)

The result for itCombo is:

OS Microsoft Windows 2000 Professional

CPU Pentium III 700 MHz

Memory 256 mb

Average Time 0.55 seconds (over 10 tests)

itCombo is approx. 900% faster!

^ top

Name	Description
Columns	A string expression specifying the columns to be created. See Remarks for further details.
ComboField	The field that is to be used in the edit portion of the combo.
Items	A string expression containing the item(s) to add.
RowDelimiter	The row delimiter to be used. If not specified then CARRIAGE RETURN (vbCr) is used.
ColDelimiter	The column delimiter to be used. If not specified then TAB (vbTab or Chr$(9)) is used.

OS	Microsoft Windows 2000 Professional
CPU	Pentium III 700 MHz
Memory	256 mb
Average Time	4.97 seconds (over 10 tests)