Tutorial 16 - Displaying Array Data in Unbound Mode

In this tutorial, you will learn how to use the unbound mode (DataMode property set to 1 - Unbound) of True DBGrid to display an array of strings.

Note: This unbound mode has been retained for backward compatibility with DBGrid and earlier versions of True DBGrid. ComponentOne recommends using unbound extended mode (Tutorial 17), application mode (Tutorial 18), or storage mode (Tutorial 19) instead. For detailed instructions on how to use unbound mode 1, see Unbound Mode.

For simplicity, this tutorial does not cover updating, adding, or deleting records. However, the UNBOUND1.VBP project provides a complete sample that you can use as a template for implementing unbound mode 1. This project is located in the TUTORIAL\UNBOUND1 subdirectory of the True DBGrid installation directory.

Start a new project.
Place a True DBGrid control (TDBGrid1) on the form (Form1).
Set the DataMode property of TDBGrid1 to 1 - Unbound (the default value of this property is 0 - Bound).

Configuring the grid at design time

This example uses the grid to display an array with two columns. Since True DBGrid displays two columns by default, you do not have to add or delete columns at design time.
Right-click TDBGrid1 to display its context menu.
Choose Properties to display the Property Pages dialog. On the General property page, set the AllowUpdate property to False so that data displayed in the grid will be read-only.
Select the Columns properties page by clicking the Columns tab. Set the Caption property of Columns(00) and Columns(01) to "Column 0" and "Column 1", respectively.

Initializing the array data

First, create and initialize a two-dimensional array to hold the data to be displayed in the grid.

In the General section of Form1, insert the following declarations:

Example Title	Copy Code
' General declarations. Option Explicit ' Special Note: Variables that store Row indices will be of data type Long (It ' is reasonable to assume there may be larger than 32,767 rows). Those storing ' Column indices will be of type Integer. (It is very unlikely there will be ' more than 32,767 columns.) ' Number of columns. Dim MaxCol As Integer ' Number of rows. Dim MaxRow As Long ' Array to store the data. Dim GridArray() As Variant

Example Title

Copy Code

' General declarations.

Option Explicit

 

' Special Note: Variables that store Row indices will be of data type Long (It

' is reasonable to assume there may be larger than 32,767 rows). Those storing

' Column indices will be of type Integer. (It is very unlikely there will be

' more than 32,767 columns.)

 

' Number of columns.

Dim MaxCol As Integer          

 

' Number of rows.

Dim MaxRow As Long             

 

' Array to store the data.

Dim GridArray() As Variant

In the Form_Load event, initialize the elements of GridArray and set the ApproxCount property of the grid accordingly. The ApproxCount property is optional, but setting this value will enable the grid to position the vertical scroll bar accurately. If you want to initialize the current cell to a specific location, the Form_Activate (Visual Basic) event is a good place to do it.

Example Title	Copy Code
' General declarations. Option Explicit ' Special Note: Variables that store Row indices will be of data type Long (It ' is reasonable to assume there may be larger than 32,767 rows). Those storing ' Column indices will be of type Integer. (It is very unlikely there will be ' more than 32,767 columns.) ' Number of columns. Dim MaxCol As Integer ' Number of rows. Dim MaxRow As Long ' Array to store the data. Dim GridArray() As Variant

Example Title

Copy Code

' General declarations.

Option Explicit

 

' Special Note: Variables that store Row indices will be of data type Long (It

' is reasonable to assume there may be larger than 32,767 rows). Those storing

' Column indices will be of type Integer. (It is very unlikely there will be

' more than 32,767 columns.)

 

' Number of columns.

Dim MaxCol As Integer          

 

' Number of rows.

Dim MaxRow As Long             

 

' Array to store the data.

Dim GridArray() As Variant

Displaying data in the unbound grid

When using the ListBox control in Visual Basic, you store all of the data in the control using its AddItem method. This storage method is neither adequate nor efficient when you have a large amount of data or when the data source continuously changes.

Unlike the ListBox control, True DBGrid's unbound modes do not store your data. You manage the data while the grid handles all display and end-user interactions. Whenever the grid needs to display more rows of data, such as during vertical scrolling, it will fire the UnboundReadData event to ask for data from your data source. The grid generally asks for only what it needs to display, but may cache some data for efficiency considerations. You cannot predict when the grid will ask for data, nor can you assume data will be requested in any particular order. Furthermore, since the grid does not store the data, any data that has been requested once may be requested again.

Place the following code in the UnboundGetRelativeBookmark event of TDBGrid1. This event provides bookmarks to the grid and also calibrates the vertical scroll bar:

Example Title	Copy Code
Private Sub TDBGrid1_UnboundGetRelativeBookmark( _ StartLocation As Variant, ByVal Offset As Long, _ NewLocation As Variant, ApproximatePosition As Long) ' TDBGrid1 calls this routine each time it needs to reposition itself. ' StartLocation is a bookmark supplied by the grid to indicate the ' "current" position -- the row we are moving from. Offset is the number ' of rows we must move from StartLocation in order to arrive at the ' desired destination row. A positive offset means the desired record is ' after the StartLocation, and a negative offset means the desired record ' is before StartLocation. ' If StartLocation is NULL, then we are positioning from either BOF or ' EOF. Once we determine the correct index for StartLocation, we will ' simply add the offset to get the correct destination row. ' GetRelativeBookmark already does all of this, so we just call it here. NewLocation = GetRelativeBookmark(StartLocation, Offset) ' If we are on a valid data row (i.e., not at BOF or EOF), then set the ' ApproximatePosition (the ordinal row number) to improve scroll bar ' accuracy. We can call IndexFromBookmark to do this. If Not IsNull(NewLocation) Then ApproximatePosition = IndexFromBookmark(NewLocation, 0) End If End Sub

Example Title

Copy Code

Private Sub TDBGrid1_UnboundGetRelativeBookmark( _

    StartLocation As Variant, ByVal Offset As Long, _

    NewLocation As Variant, ApproximatePosition As Long)

 

    ' TDBGrid1 calls this routine each time it needs to reposition itself.

    ' StartLocation is a bookmark supplied by the grid to indicate the

    ' "current" position -- the row we are moving from. Offset is the number

    ' of rows we must move from StartLocation in order to arrive at the

    ' desired destination row. A positive offset means the desired record is

    ' after the StartLocation, and a negative offset means the desired record

    ' is before StartLocation.

 

    ' If StartLocation is NULL, then we are positioning from either BOF or

    ' EOF. Once we determine the correct index for StartLocation, we will

    ' simply add the offset to get the correct destination row.

    ' GetRelativeBookmark already does all of this, so we just call it here.

    NewLocation = GetRelativeBookmark(StartLocation, Offset)

 

    ' If we are on a valid data row (i.e., not at BOF or EOF), then set the

    ' ApproximatePosition (the ordinal row number) to improve scroll bar

    ' accuracy. We can call IndexFromBookmark to do this.

    If Not IsNull(NewLocation) Then

       ApproximatePosition = IndexFromBookmark(NewLocation, 0)

    End If

End Sub

Place the following code in the UnboundReadData event of TDBGrid1. This example shows how data is provided to the grid via the RowBuffer object:

Example Title	Copy Code
Private Sub TDBGrid1_UnboundReadData( _ ByVal RowBuf As TrueDBGrid80.RowBuffer, _ StartLocation As Variant, ByVal ReadPriorRows As Boolean) ' UnboundReadData is fired by an unbound grid whenever it requires data ' for display. This event will fire when the grid is first shown, when ' Refresh or ReBind is used, when the grid is scrolled, and after a ' record in the grid is modified and the user commits the change by moving ' off of the current row. The grid fetches data in "chunks", and the ' number of rows the grid is asking for is given by RowBuf.RowCount. ' RowBuf is the row buffer where you place the data and the bookmarks for ' the rows that the grid is requesting to display. It will also hold the ' number of rows that were successfully supplied to the grid. ' StartLocation is a bookmark which specifies the row before or after the ' desired data, depending on the value of ReadPriorRows. If we are reading ' rows after StartLocation (ReadPriorRows = False), then the first row of ' data the grid wants is the row after StartLocation, and if we are ' reading rows before StartLocation (ReadPriorRows = True) then the first ' row of data the grid wants is the row before StartLocation. ' ReadPriorRows is a Boolean value indicating whether we are reading rows ' before (ReadPriorRows = True) or after (ReadPriorRows = False) ' StartLocation. Dim Bookmark As Variant Dim I As Long, RelPos As Long Dim J As Integer, RowsFetched As Integer ' Get a bookmark for the start location. Bookmark = StartLocation If ReadPriorRows Then ' Requesting data in rows prior to StartLocation. RelPos = -1 Else ' Requesting data in rows after StartLocation. RelPos = 1 End If RowsFetched = 0 For I = 0 To RowBuf.RowCount - 1 ' Get the bookmark of the next available row. Bookmark = GetRelativeBookmark(Bookmark, RelPos) ' If the next row is BOF or EOF, then stop fetching and return any ' rows fetched up to this point. If IsNull(Bookmark) Then Exit For ' Place the record data into the row buffer. For J = 0 To RowBuf.ColumnCount - 1 RowBuf.Value(I, J) = GetUserData(Bookmark, J) Next J ' Set the bookmark for the row. RowBuf.Bookmark(I) = Bookmark ' Increment the count of fetched rows. RowsFetched = RowsFetched + 1 Next I ' Tell the grid how many rows we fetched. RowBuf.RowCount = RowsFetched End Sub

Example Title

Copy Code

Private Sub TDBGrid1_UnboundReadData( _

    ByVal RowBuf As TrueDBGrid80.RowBuffer, _

    StartLocation As Variant, ByVal ReadPriorRows As Boolean)

 

    ' UnboundReadData is fired by an unbound grid whenever it requires data

    ' for display. This event will fire when the grid is first shown, when

    ' Refresh or ReBind is used, when the grid is scrolled, and after a

    ' record in the grid is modified and the user commits the change by moving

    ' off of the current row. The grid fetches data in "chunks", and the

    ' number of rows the grid is asking for is given by RowBuf.RowCount.

 

    ' RowBuf is the row buffer where you place the data and the bookmarks for

    ' the rows that the grid is requesting to display. It will also hold the

    ' number of rows that were successfully supplied to the grid.

 

    ' StartLocation is a bookmark which specifies the row before or after the

    ' desired data, depending on the value of ReadPriorRows. If we are reading

    ' rows after StartLocation (ReadPriorRows = False), then the first row of

    ' data the grid wants is the row after StartLocation, and if we are

    ' reading rows before StartLocation (ReadPriorRows = True) then the first

    ' row of data the grid wants is the row before StartLocation.

 

    ' ReadPriorRows is a Boolean value indicating whether we are reading rows

    ' before (ReadPriorRows = True) or after (ReadPriorRows = False)

    ' StartLocation.

 

    Dim Bookmark As Variant

    Dim I As Long, RelPos As Long

    Dim J As Integer, RowsFetched As Integer

   

    ' Get a bookmark for the start location.

    Bookmark = StartLocation

       

    If ReadPriorRows Then

 

        ' Requesting data in rows prior to StartLocation.

        RelPos = -1

    Else

 

        ' Requesting data in rows after StartLocation.

        RelPos = 1 

    End If

   

    RowsFetched = 0

    For I = 0 To RowBuf.RowCount - 1

 

        ' Get the bookmark of the next available row.

        Bookmark = GetRelativeBookmark(Bookmark, RelPos)

   

        ' If the next row is BOF or EOF, then stop fetching and return any

        ' rows fetched up to this point.

        If IsNull(Bookmark) Then Exit For

   

        ' Place the record data into the row buffer.

        For J = 0 To RowBuf.ColumnCount - 1

            RowBuf.Value(I, J) = GetUserData(Bookmark, J)

        Next J

   

        ' Set the bookmark for the row.

        RowBuf.Bookmark(I) = Bookmark

   

        ' Increment the count of fetched rows.

        RowsFetched = RowsFetched + 1

    Next I

   

    ' Tell the grid how many rows we fetched.

    RowBuf.RowCount = RowsFetched

End Sub

The UnboundReadData event handler listed in the previous step calls the following support functions to manage the array data and bookmarks: MakeBookmark, IndexFromBookmark, GetRelativeBookmark, and GetUserData.

Example Title	Copy Code
Private Function MakeBookmark(Index As Long) As Variant ' This support function is used only by the remaining support functions. ' It is not used directly by the unbound events. It is a good idea to ' create a MakeBookmark function such that all bookmarks can be created in ' the same way. Thus the method by which bookmarks are created is ' consistent and easy to modify. This function creates a bookmark when ' given an array row index. ' Since we have data stored in an array, we will just use the array index ' as our bookmark. We will convert it to a string first, using the CStr ' function. MakeBookmark = CStr(Index) End Function Private Function IndexFromBookmark(Bookmark As Variant, _ ReadPriorRows As Boolean) As Long ' This support function is used only by the remaining support functions. ' It is not used directly by the unbound events. ' This function is the inverse of MakeBookmark. Given a bookmark, ' IndexFromBookmark returns the row index that the given bookmark refers ' to. If the given bookmark is Null, then it refers to BOF or EOF. In such ' a case, we need to use ReadPriorRows to distinguish between the two. If ' ReadPriorRows = True, the grid is requesting rows before the current ' location, so we must be at EOF, because no rows exist before BOF. ' Conversely, if ReadPriorRows = False, we must be at BOF. Dim Index As Long If IsNull(Bookmark) Then If ReadPriorRows Then ' Bookmark refers to EOF. Since (MaxRow - 1) is the index of the ' last record, we can use an index of (MaxRow) to represent EOF. IndexFromBookmark = MaxRow Else ' Bookmark refers to BOF. Since 0 is the index of the first ' record, we can use an index of -1 to represent BOF. IndexFromBookmark = -1 End If Else ' Convert string to long integer. Index = Val(Bookmark) ' Check to see if the row index is valid: (0 <= Index < MaxRow). If ' not, set it to a large negative number to indicate that the bookmark ' is invalid. If Index < 0 Or Index >= MaxRow Then Index = -9999 IndexFromBookmark = Index End If End Function Private Function GetRelativeBookmark(Bookmark As Variant, _ RelPos As Long) As Variant ' GetRelativeBookmark is used to get a bookmark for a row that is a given ' number of rows away from the given row. This specific example will ' always use either -1 or +1 for a relative position, since we will always ' be retrieving either the row previous to the current one, or the row ' following the current one. ' IndexFromBookmark expects a Bookmark and a Boolean value: this Boolean ' value is True if the next row to read is before the current one [in this ' case, (RelPos < 0) is True], or False if the next row to read is after ' the current one [(RelPos < 0) is False]. This is necessary to ' distinguish between BOF and EOF in the IndexFromBookmark function if our ' bookmark is Null. Once we get the correct row index from ' IndexFromBookmark, we simply add RelPos to it to get the desired row ' index and create a bookmark using that index. Dim Index As Long Index = IndexFromBookmark(Bookmark, RelPos < 0) + RelPos If Index < 0 Or Index >= MaxRow Then ' Index refers to a row before the first or after the last, so just ' return Null. GetRelativeBookmark = Null Else GetRelativeBookmark = MakeBookmark(Index) End If End Function Private Function GetUserData(Bookmark As Variant, _ Col As Integer) As Variant ' In this example, GetUserData is called by UnboundReadData to ask the ' user what data should be displayed in a specific cell in the grid. The ' grid row the cell is in is the one referred to by the Bookmark ' parameter, and the column it is in it given by the Col parameter. ' GetUserData is called on a cell-by-cell basis. Dim Index As Long ' Figure out which row the bookmark refers to. Index = IndexFromBookmark(Bookmark, False) If Index < 0 Or Index >= MaxRow Or _ Col < 0 Or Col >= MaxCol Then ' Cell position is invalid, so just return null to indicate failure. GetUserData = Null Else GetUserData = GridArray(Col, Index) End If End Function

Example Title

Copy Code

Private Function MakeBookmark(Index As Long) As Variant

    ' This support function is used only by the remaining support functions.

    ' It is not used directly by the unbound events. It is a good idea to

    ' create a MakeBookmark function such that all bookmarks can be created in

    ' the same way. Thus the method by which  bookmarks are created is

    ' consistent and easy to modify. This function creates a bookmark when

    ' given  an array row index.

 

    ' Since we have data stored in an array, we will just use the array index

    ' as our bookmark. We will convert it to a string first, using the CStr

    ' function.

 

    MakeBookmark = CStr(Index)

End Function

Private Function IndexFromBookmark(Bookmark As Variant, _

        ReadPriorRows As Boolean) As Long

 

    ' This support function is used only by the remaining support functions.

    ' It is not used directly by the unbound events.

   

    ' This function is the inverse of MakeBookmark. Given a bookmark,

    ' IndexFromBookmark returns the row index that the given bookmark refers

    ' to. If the given bookmark is Null, then it refers to BOF or EOF. In such

    ' a case, we need to use ReadPriorRows to distinguish between the two. If

    ' ReadPriorRows = True, the grid is requesting rows before the current

    ' location, so we must be at EOF, because no rows exist before BOF.

    ' Conversely, if ReadPriorRows = False, we must be at BOF.

   

    Dim Index As Long

     

    If IsNull(Bookmark) Then

        If ReadPriorRows Then

 

            ' Bookmark refers to EOF. Since (MaxRow - 1) is the index of the

            ' last record, we can use an index of (MaxRow) to represent EOF.

            IndexFromBookmark = MaxRow

        Else

 

            ' Bookmark refers to BOF. Since 0 is the index of the first

            ' record, we can use an index of -1 to represent BOF.

            IndexFromBookmark = -1

        End If

    Else

 

        ' Convert string to long integer.

        Index = Val(Bookmark)

       

        ' Check to see if the row index is valid: (0 <= Index < MaxRow). If

        ' not, set it to a large negative number to indicate that the bookmark

        ' is invalid.

        If Index < 0 Or Index >= MaxRow Then Index = -9999

        IndexFromBookmark = Index

    End If

End Function

Private Function GetRelativeBookmark(Bookmark As Variant, _

        RelPos As Long) As Variant

    ' GetRelativeBookmark is used to get a bookmark for a  row that is a given

    ' number of rows away from the given row. This specific example will

    ' always use either -1 or +1 for a relative position, since we will always

    ' be retrieving either the row previous to the current one, or the row

    ' following the current one.

 

    ' IndexFromBookmark expects a Bookmark and a Boolean value: this Boolean

    ' value is True if the next row to read is before the current one [in this

    ' case, (RelPos < 0) is True], or False if the next row to read is after

    ' the current one [(RelPos < 0) is False]. This is necessary to

    ' distinguish between BOF and EOF in the IndexFromBookmark function if our

    ' bookmark is Null. Once we get the correct row index from

    ' IndexFromBookmark, we simply add RelPos to it to get the desired row

    ' index and create a bookmark using that index.

 

    Dim Index As Long

   

    Index = IndexFromBookmark(Bookmark, RelPos < 0) + RelPos

    If Index < 0 Or Index >= MaxRow Then

 

        ' Index refers to a row before the first or after the last, so just

        ' return Null.

        GetRelativeBookmark = Null

    Else

        GetRelativeBookmark = MakeBookmark(Index)

    End If

End Function

Private Function GetUserData(Bookmark As Variant, _

        Col As Integer) As Variant

    ' In this example, GetUserData is called by UnboundReadData to ask the

    ' user what data should be displayed in a specific cell in the grid. The

    ' grid row the cell is in is the one referred to by the Bookmark

    ' parameter, and the column it is in it given by the Col parameter.

    ' GetUserData is called on a cell-by-cell basis.

 

    Dim Index As Long

 

    ' Figure out which row the bookmark refers to.

    Index = IndexFromBookmark(Bookmark, False)

   

    If Index < 0 Or Index >= MaxRow Or _

       Col < 0 Or Col >= MaxCol Then

 

        ' Cell position is invalid, so just return null to indicate failure.

        GetUserData = Null

    Else

        GetUserData = GridArray(Col, Index)

    End If

End Function

Run the program and observe the following:

The grid displays the elements of GridArray and otherwise behaves as if it were bound to a Data control.

This concludes Tutorial 16.