Embedded Visual Basic Windows Ce And Pocket Pc Mobile Applications [Electronic resources] نسخه متنی

Creating the CD Library Sample Application

In this chapter, we'll use a sample application that allows users to keep an inventory of musical compact discs. Two friends of mine, whose CD collection numbers more than 6,000 CDs, inspired this application.

The CD Library allows the storage of artists, genres, and CDs. On the initial form, CD Library displays a grid of all artists sorted alphabetically and a grid of each artist's CDs (see Figure 7.1). Users can then select a CD from the grid and the application enables editing of the CD information. To enter a new CD, users tap the New button on the menu.

Understanding Some Issues

Before you get started with ADOCE, you must become familiar with a few caveats. The first issue centers on the different versioning that ADOCE uses in comparison to the desktop ADO. The second issue centers on the CreateObject memory leak (as discussed in Chapters 2 and 3). These two issues alone dramatically change the architecture of how an application that uses ADOCE should be designed as compared to a desktop Visual Basic application.

ADOCE Versioning Implementation

To use ADOCE in an eVB application, your project needs to have a reference to the ADOCE library. Interestingly enough, there seems to be a design flaw in the way Microsoft implemented the ADOCE library in comparison to ADO for the desktop or other ActiveX libraries. There is no methodology incorporated in the type libraries for an eVB application to gracefully "upgrade" to a newer version of ADOCE.

So, you can't reference an older ADOCE library and use a newer version installed on the machine. For example, you create an eVB application using a reference to ADOCE version 3.0 and this application is installed on a device. Now, a newer version (ADOCE 3.1) is installed on the machine. The older application will continue to use ADOCE 3.0, because the names of the ActiveX classes for ADOCE must be referenced with a version number. For example,

CreateObject( "ADOCE.Connection.3.0" )

will work properly, but

CreateObject( "ADOCE.Connection" )

won't work. Issuing

CreateObject( "ADOCE.Connection" )

will result in an error that the ActiveX object can't be found.

For maintainability of an eVB application, I have found it best to define a Const with the ADOCE class names defined as a string. In an application, you can define the version of ADOCE that should be used in one place, utilizing this Const wherever CreateObject is called for an ADOCE object. Then, when you need to upgrade the ADOCE version that the application should utilize, simply edit these Consts.

Consts define the version of ADOCE classes used in CD Library:

' constants for the ADOCE classes
Const ADOCE_CLASS_CONNECTION = "ADOCE.Connection.3.0"
Const ADOCE_CLASS_RECORDSET = "ADOCE.Recordset.3.0"

The following is an example of Consts being used:

Set gobjArtist = CreateObject(ADOCE_CLASS_RECORDSET)

Impact of CreateObject Memory Leak on ADOCE Applications

Before building Windows CE applications, I built Web applications. Typically, these applications used the Microsoft Windows DNA design, a three-tier architecture. In a three-tier architecture, the typical design utilizes a methodology to open a connection, retrieve a recordset resource locally, and then use and dispose of it. This is a good design to keep applications modular, scalable, maintainable, and consume less resources. If you have built three-tier applications, your instincts to build Windows CE applications similarly might follow three-tier design, as my experience did. But, there is a problem with this methodologythe CreateObject memory leak put a stop to this.

In eVB, if your application constantly creates new ADOCE.Connection or ADOCE.Recordset objects, the device eventually runs out or runs low on memory. This happens because of a bug in the CreateObject function and its garbage collection. The CreateObject function slowly, yet surely, leaks memory.

Therefore, when designing ADOCE applications, it's best to define all your ADOCE objects globally, instantiate them at application startup, and leave them there until your application closes. This goes against many design rules, but really is one of the better workarounds for ADOCE applications. One reason this isn't such a large problem is that all data is local and typically there is only one connection to the database.

The CD Library application declares all ADOCE objects in a module file so that they are available globally within the application:

' global objects for all ADOCE objects
Public gobjConnection As ADOCE.Connection
Public gobjArtist As ADOCE.Recordset
Public gobjCD As ADOCE.Recordset
Public gobjPKEY As ADOCE.Recordset
Public gobjGenre As ADOCE.Recordset

The CD Library application initializes the ADOCE at the startup of the application during the Form.Load event (see Listing 7.1).

Listing 7.1 modGlobal.bas: Initializing the Global ADOCE Objects

Function InitializeGlobals() As Boolean
On Error Resume Next
InitializeGlobals = False
' initialize globals - this needs to take place
' only once, due to CreateObject memory leak
' ADOCE_CLASS_CONNECTION & ADOCE_CLASS_RECORDSET
' are used because ce references these classes
' by their version numbers, create Consts allows
' changing of these version quickly and accurately
Set gobjConnection = CreateObject(ADOCE_CLASS_CONNECTION)
Set gobjArtist = CreateObject(ADOCE_CLASS_RECORDSET)
Set gobjCD = CreateObject(ADOCE_CLASS_RECORDSET)
Set gobjGenre = CreateObject(ADOCE_CLASS_RECORDSET)
Set gobjPKEY = CreateObject(ADOCE_CLASS_RECORDSET)
If Err.Number = 0 Then
InitializeGlobals = True
End If
End Function

Understanding the Project Components

The CD Library contains the following three components:

• The modGlobal.bas module serves to declare the global variables and hold common routines.

• The frmMain form serves as the main form for the Artist and CD grids.

• The frmCD form enables users to edit existing and new CD records.

Defining Data Structures for the Application

The data for the CD Library application is created and stored in a Pocket Access database named cd.cdb. Table 7.1 lists the definitions of the four tables used in the CD Library Application. The first threeArtist, Genre, and CDare entities displayed and edited by users.

The fourth table, PKey, serves as a repository for the last value used as a primary key in each of the other tables. This is necessary because Pocket Access doesn't support AutoNumber. This table is referenced each time a new record is added to one of the three other tables. When a record is added to a table, a record corresponding to the table is looked up in the PKey table. The value of the Value field is incremented by one and updated in the PKey table. This takes place in the function GetNewPK, which resides in the modGlobal module.

Setting Up the CD Library Project

Create a new Pocket PC project.

To add a reference to ADOCE for the project, choose Project, References. As discussed previously, there are multiple versions of ADOCE. Each version installed on the development machine will be present in the Project References dialog.

Check a reference to ADOCE by checking Microsoft CE ADO Control 3.0. Notice that Microsoft CE ADO Control 3.1 might be available if SQL Server CE was installed.

Adding References to Additional Controls

Three additional controls are used in the CD Library: Grid, Menu, and File System. Add these components as shown in Figure 7.3.

Adding the frmCD Form to the Project

Add a form to the project, and rename it to frmCD. Set the caption of frmCD to "CD".

Adding the modGlobal Module to the Project

Add a module to the project, and name it modGlobal.

Setting Up frmMain

First, rename the form to frmMain. Also, set the value of the Caption property to CD Library.

Add one instance of the Pocket PC menu bar control to the form. No attention needs to be placed to the Top, Left, Width or Height properties as the actual control is invisible at runtime. For more information on the menu bar control, see Chapter 4, "Working with Menu Controls for Pocket PC."

Next, add the File System control to the form. Again, no attention needs to be given to the Top, Left, Width, or Height properties as the File System control is invisible at runtime. This allows you to delete the database file when you call the sub to re-create the database. For more information about the File System control, see Chapter 3, "Manipulating Files."

Finally, let's add two instances of the grid control to the form. These grids should be arranged as shown earlier in Figure 7.1. The top grid should be named grdArtist and the lower grid grdCD.

Setting Up modGlobal's Consts and Public Variables

Now set up the code for modGlobal as shown in Listing 7.2. First, set up the Consts and global variables.

Listing 7.2 modGlobal.bas: Defining Consts and Global Objects

' constants for mode that the
' cd form can operate in
Const CDFORM_MODE_EDIT = 0
Const CDFORM_MODE_NEW = 1
' contstants for the ADOCE classes
Const ADOCE_CLASS_CONNECTION = "ADOCE.Connection.3.0"
Const ADOCE_CLASS_RECORDSET = "ADOCE.Recordset.3.0"
' contstant for database path and name
Const CD_CDB_PATH_FILENAME = "\cd.cdb"
' global objects for all ADOCE objects
Public gobjConnection As ADOCE.Connection
Public gobjArtist As ADOCE.Recordset
Public gobjCD As ADOCE.Recordset
Public gobjPKEY As ADOCE.Recordset
Public gobjGenre As ADOCE.Recordset

In Listing 7.2, the first two Consts are used when calling frmCD, to inform it of the mode in which it should operate, editing an existing or new record.

The next two Consts define the ActiveX class name (and version) that will be used throughout the application for the ADOCE classes that will be instantiated directly. These Consts assist in maintainability for the version problem as discussed earlier.

The last Const defines the path and name of the CD database. The backslash denotes to put the database into the root directory of the device. You can change this to meet your needs.

The five Public variables declared are used throughout the application to access data. Remember that these objects being global (Public) is a workaround that diminishes the CreateObject memory leak as discussed earlier.

Secondly, stub out all the subs and functions for modGlobal. These functions serve as application-level functions. Enter the code in Listing 7.3 into modGlobal.

Listing 7.3 modGlobal.bas: Stubbing Out the Global Functions

Function InitializeGlobals() As Boolean
End Function
Function ReleaseGlobals()
End Function
Function OpenDatabase() As Boolean
End Function
Function EnsureDatabaseExists() As Boolean
End Function
Function CreateTables() As Boolean
End Function
Function GetNewPK(ByVal strTable As String) As Long
End Function
Function AddGenre(ByVal strGenre As String) As Long
End Function
Function AddArtist( ByVal strArtist As String, _
ByVal lngDefaultId As Long) As Long
End Function
Function AddCD( _
ByVal lngArtistId As Long, _
ByVal lngGenreId As Long, _
ByVal strCDName As String, _
ByVal lngYear As Long, _
ByVal curPrice As Currency) As Long
End Function
Function CreateData() As Boolean
End Function
Function RecreateDatabase() As Boolean
End Function
Function DeleteDataBase() As Boolean
End Function

Setting Up frmMain's Consts and Public Variables

First, set up all form-level Consts and Public variables as shown in Listing 7.4.

Listing 7.4 Consts and Public Variables

Const MENU_ROOTPROMPT_TOOLS_KEY = "TOOLS"
Const MENU_TOOLS_RECREATEDB_KEY = "TOOLSRECREATEDB"
' stores the column names and
' captions for the cd grid
Public gstrCDCols(1, 3) As String
' used to store the current sort
' order for the cd grid
Public glngCDSortCol As Long
' stores the current artist id
Public glngArtistId As Long

The two constants define unique key values for the menu. This follows the same methodology used in Chapter 4.

Next, the three Public variables are to hold form-level values that will be used. The first is a two-dimensional array that holds the column names and their respective captions to display in the first row of the CD grid.

The second Public variable is an index to the row of the array for the current sort order of the CD grid. The CD grid allows users to re-sort by tapping the first row; when a column in the first row of the CD grid is tapped, the grid is refreshed and sorted by that column.

The last Public variable is used to store the ID value of the last artist selected from the grid. This value is used when the CD grid is refreshed to filter the records to that artist. It's also used when the new button is pressed to inform the CD form of the default artist for the new CD record.

Stubbing Out All frmMain Subs and Functions

The functions in Listing 7.5 are defined and implemented in frmMain. Each is a function returning a Boolean type. Although there shouldn't be any errors when calling these stub functions, the return values still need to be True for the application to function in limited capacity while we're still creating it. So, include the code to check for errors and set the return value into the stubs for now. We'll implement the rest later.

Listing 7.5 Stubbing Out Common Functions

Function PopulateArtistGrid() As Boolean
' check for errors, if none, return True
If Err.Number = 0 Then
PopulateArtistGrid = True
End If
End Function
Function RefreshCDGrid() As Boolean
' check for errors, if none, return True
If Err.Number = 0 Then
RefreshCDGrid = True
End If
End Function
Function PopulateMenus() As Boolean
' check for errors, if none, return True
If Err.Number = 0 Then
PopulateMenus = True
End If
End Function
Function InitializeGridVariables() As Boolean
' check for errors, if none, return True
If Err.Number = 0 Then
InitializeGridVariables = True
End If
End Function

The following code snippet is the template used in the stub functions to ensure that each is returning True (the non-error return value), until you implement them later:

If Err.Number = 0 Then
' set return value for function
' to True here
End If

Using the Form.Load Event

Let's look at what happens in the Form.Load event (see Listing 7.6).

Listing 7.6 Implementing frmMain's Load Event

Private Sub Form_Load()
On Error Resume Next
' initialize all global variables
If Not InitializeGlobals() Then
MsgBox "Error initializing global objects."
Exit Sub
End If
' open the database
If Not OpenDatabase() Then
MsgBox "Error opening the database."
Exit Sub
End If
' set up the menus
If Not PopulateMenus() Then
MsgBox "Error populating the menu object."
Exit Sub
End If
' initialize the column array and other
' grid variables
If Not InitializeGridVariables() Then
MsgBox "Error initializing the grid variables."
Exit Sub
End If
End Sub

Using the InitializeGlobals Function

In Listing 7.7, the InitializeGlobals function is called from the Form.Load event. InitializeGlobals instantiates all the global ADOCE objects. Remember, InitializeGlobals exists in the module modGlobal.

Listing 7.7 modGlobal.bas: Creating the ADOCE Objects

Function InitializeGlobals() As Boolean
On Error Resume Next
InitializeGlobals = False
' initialize globals - this needs to take place
' only once, due to CreateObject memory leak
' ADOCE_CLASS_CONNECTION & ADOCE_CLASS_RECORDSET
' are used because ce references these classes
' by their version numbers, create Consts allows
' changing of these version quickly and accurately
Set gobjConnection = CreateObject(ADOCE_CLASS_CONNECTION)
Set gobjArtist = CreateObject(ADOCE_CLASS_RECORDSET)
Set gobjCD = CreateObject(ADOCE_CLASS_RECORDSET)
Set gobjGenre = CreateObject(ADOCE_CLASS_RECORDSET)
Set gobjPKEY = CreateObject(ADOCE_CLASS_RECORDSET)
If Err.Number = 0 Then
InitializeGlobals = True
End If
End Function

Note

All the ADOCE objects are created here and only here, once and only once. This minimizes the CreateObject memory leak. This, as discussed earlier, diverges completely from the architecture that should be used in a three-tier application.

The first object created is ADOCE.Connection. There is only one Connection object in the application. The next four objects are ADOCE.Recordset objects. Each Recordset object corresponds to a table within the application. The first three correspond to an entity within the CD Library that appears as an entity with the application.

Using the OpenDatabase Function

Next, look at OpenDatabase as shown in Listing 7.8. The OpenDatabase function opens the connection for the entire application. It first calls EnsureDatabaseExists, which makes sure that \cd.cdb, the Pocket Access file, exists. EnsureDatabaseExists also creates the tables, indexes, and populates some sample data.

Note

This sample application places cd.cdb in the device's root directory. More robust applications should use the App.Path or a registry to locate the database.

Listing 7.8 modGlobal.bas: Opening the Pocket Access Database

Function OpenDatabase() As Boolean
On Error Resume Next
' set the return value to an error
' condition, so we can exit early
' if an error occurs
OpenDatabase = False
' make sure the database exists
If Not EnsureDatabaseExists() Then
Exit Function
End If
' we need to check if the connection is
' open or close, because EnsureDatabaseExists may
' opened the database
If gobjConnection.State = adStateClosed Then
' opens the pocket access database
gobjConnection.Open CD_CDB_PATH_FILENAME
End If
If Err.Number = 0 Then
OpenDatabase = True
End If
End Function

Using the EnsureDatabaseExists Function

Let's look at EnsureDatabaseExists in Listing 7.9, where the CD database (cd.cdb), its tables, and its sample data are created.

Listing 7.9 modGlobal.bas: Checking for the Existence of the Database

Function EnsureDatabaseExists() As Boolean
Dim strDir As String
On Error Resume Next
' set the return value to an error
' condition, so we can exit early
' if an error occurs
EnsureDatabaseExists = False
' check to see if the database exists
strDir = frmMain.FileSystem1.Dir( CD_CDB_PATH_FILENAME )
' if it doesn't let's create it
If Len(strDir) = 0 Then
' prepares the connection for use
gobjConnection.Open
' creates a pocket access database
gobjConnection.Execute "CREATE DATABASE '" & CD_CDB_PATH_FILENAME & "'"
' closes the connection - can not open
' the database while the connection is open
gobjConnection.Close
' reopen the database (using the cd.cdb)
If Not OpenDatabase() Then
MsgBox "Can not open database."
Exit Function
End If
' create the tables and indexes
If Not CreateTables() Then
MsgBox "Can not create tables."
Exit Function
End If
' create test data
If Not CreateData() Then
MsgBox "Error occurred in creating sample data."
Exit Function
End If
End If
' if no errors have occurred, we
' have successfully completed this
' function, so let's return a
' successful return value
If Err.Number = 0 Then
EnsureDatabaseExists = True
End If
End Function

In Listing 7.9, EnsureDatabaseExists first checks to see if the database exists. It uses the File System control that resides on frmMain. It uses the Dir method to get the file path (\cd.cdb) to the database. If the file exists, the Dir method will return this path, if it doesn't exist, the Dir method will return an empty string. For more information about the File System control, see Chapter 3.

Next in Listing 7.9, the ADOCE.Connection object (gobjConnection) is opened. This must be done to execute any statements. Even though no ConnectionString is specified, the ADOCE.Connection opens. It actually is opening a connection to the Windows CE Object Store; this doesn't hamper our effort to create a database by executing the SQL command CREATE DATABASE '\cd.cdb'. CREATE DATABASE 'cd.cdb' actually creates the Pocket Access database.

Then, the Connection object (gobjConnection) is closed. This allows the object to be reopened, this time opening the Pocket Access database (\cd.cdb). This is accomplished by making a recursive call to OpenDatabase method.

Using the CreateTables Function to Create Tables and Indexes, and to Insert Data

After the database is open, you need to create the tables. The CreateTables function in Listing 7.10 creates all four tables and indexes for those tables.

Listing 7.10 modGlobal.bas: Creating the Tables for the CD Library

Function CreateTables() As Boolean
On Error Resume Next
CreateTables = False
' creates the table artist
gobjConnection.Execute "CREATE TABLE Artist " _
& "(ArtistId int, ArtistName varchar(64), DefaultGenre int)"
' creates the table CD
gobjConnection.Execute "CREATE TABLE CD " _
& "(CDId int, ArtistId int, CDName varchar(64), " _
& "Year int, Price float, GenreId int)"
' creates the Genre table
gobjConnection.Execute "CREATE TABLE Genre " _
& "(GenreID int, GenreName varchar(64))"
' creates the PKey table (primary key)
gobjConnection.Execute "CREATE TABLE PKey " _
& "(TableName varchar(20), KeyValue int)"
' creates an index on ArtistId on table CD
gobjConnection.Execute "CREATE INDEX ArtistId ON CD (ArtistId)"
' creates an index on TableName on table PKey
gobjConnection.Execute "CREATE INDEX TableName ON PKey (TableName)"
' creates an index on GenreId on table Genre
gobjConnection.Execute "CREATE INDEX Genre ON Genre (GenreId)"
' populates the PKey table with a record for the Artist table
gobjConnection.Execute "INSERT INTO PKey VALUES ("Artist", 0 )"
' populates the PKey table with a record for the cd table
gobjConnection.Execute "INSERT INTO PKey VALUES ("CD", 0 )"
' populates the PKey table with a record for the Genre table
gobjConnection.Execute "INSERT INTO PKey VALUES ("Genre", 0 )"
If Err.Number = 0 Then
CreateTables = True
End If
End Function

Creating Tables by Using CREATE TABLE

The first four lines in CreateTables each execute a standard SQL statement that creates the four tables:

' creates the table artist
gobjConnection.Execute "CREATE TABLE Artist " _
& "(ArtistId int, ArtistName varchar(64), DefaultGenre int)"
' creates the table cd
gobjConnection.Execute "CREATE TABLE CD " _
& "(CDId int, ArtistId int, CDName varchar(64), " _
& "Year int, Price float, GenreId int)"
' creates the genre table
gobjConnection.Execute "CREATE TABLE Genre " _
& "(GenreID int, GenreName varchar(64))"
' creates the PKey table (primary key)
gobjConnection.Execute "CREATE TABLE PKey " _
& "(TableName varchar(20), KeyValue int)"

These statements use SQL directly to perform data-definition. Each SQL statement complies with standard Data Definition Language (DDL).

Creating Indexes by Using CREATE INDEX

The next three statements (repeated here) use SQL to create indexes, directly executed through the connection object. The syntax of the CREATE INDEX statement is standard DDL.

' creates an index on ArtistId on table CD
gobjConnection.Execute "CREATE INDEX ArtistId ON CD (ArtistId)"
' creates an index on TableName on table PKey
gobjConnection.Execute "CREATE INDEX TableName ON PKey (TableName)"
' creates an index on GenreId on table Genre
gobjConnection.Execute "CREATE INDEX Genre ON Genre (GenreId)"

The index on the Genre table is absolutely necessary, because there is a JOIN condition in the application between the CD and Genre tables (see Figure 7.4). With ADOCE, at least one of the tables needs to be indexed on the field(s) used for the JOIN.

The remaining two indexes are created for performance reasons when selecting filter data from their tables using the WHERE clause.

Inserting Records by Using INSERT

The last three statements are the first statements called in the application that manipulate data. These use standard SQL to insert records into the PKey table. Each record corresponds to one of the other three tables. These records are used to store the last value for the primary key column.

' populates the PKey table with a record for the Artist table
gobjConnection.Execute "INSERT INTO PKey VALUES ("Artist", 0 )"
' populates the PKey table with a record for the cd table
gobjConnection.Execute "INSERT INTO PKey VALUES ("CD", 0 )"
' populates the PKey table with a record for the Genre table
gobjConnection.Execute "INSERT INTO PKey VALUES ("Genre", 0 )"

Using the CreateData Function

The CreateData function creates sample data. The code in Listing 7.11 creates three genres. Later you will add code to create three artists and two CDs for each artist.

Listing 7.11 modGlobal.bas: Creating Sample Genre Records

Private Function CreateData() As Boolean
Dim lngRockId As Long
Dim lngBlueGrassId As Long
Dim lngHeavyMetalId As Long
On Error Resume Next
' set the return value to an
' error condition, this allows
' early exit if an error occurs
CreateData = False
' let's add genres
lngRockId = AddGenre("Rock")
' check the return value, should be
' greater than zero
If lngRockId < 0 Then
Exit Function
End If
lngBlueGrassId = AddGenre("Bluegrass")
' check the return value, should be
' greater than zero
If lngBlueGrassId < 0 Then
Exit Function
End If
lngHeavyMetalId = AddGenre("Heavy Metal")
' check the return value, should be
' greater than zero
If lngHeavyMetalId < 0 Then
Exit Function
End If
If Err.Number = 0 Then
CreateData = True
End If
End Function

Each Genre has a corresponding variable of type Long declared. The primary key values for each of these Genre records are retained to these variables. These primary key values are used when creating CD records.

Using the AddGenre Function

Listing 7.12 calls the AddGenre function from the CreateData function (it's also called from the CD form, as you will see later). The AddGenre function inserts records but uses the ADOCE.Recordset method to accomplish this. This is different from the SQL direct method used to insert the records into PKey in the CreateTables function. After a successful insert of the genre record, AddGenre returns the value of the primary key of the newly inserted record.

Listing 7.12 modGlobal.bas: Encapsulating Adding a Genre Record

Function AddGenre(ByVal strGenre As String) As Long
' adds an genre to the table and returns
' its primary key value
Dim lngGenreId As Long
On Error Resume Next
' set the return value to error code
' in case we need to exit early
AddGenre = -1
' get a new primary key value
lngGenreId = GetNewPK("Genre")
' check the return value for the new primary key
If lngGenreId < 0 Then
Exit Function
End If
' if the recordset is open
' let's check if it supports addnew
If gobjGenre.State = adStateOpen Then
' if the current recordset does not
' support adding new records, let's close
' it and reopen it
If Not gobjGenre.Supports(adAddNew) Then
gobjGenre.Close
End If
End If
' if the recordset is closed above
' or closed before we get into this
' function, let's open it with the
' proper support for adding
If gobjGenre.State = adStateClosed Then
gobjGenre.Open "genre", gobjConnection, _
adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
End If
' add a new record
gobjGenre.AddNew
' set the values
gobjGenre.Fields("GenreId").Value = lngGenreId
gobjGenre.Fields("GenreName").Value = strGenre
' update the recordset
gobjGenre.Update
' set the return value
AddGenre = lngGenreId
' if there was an error, let's set the return
' value to a negative value
If Err.Number <> 0 Then
AddGenre = -1 * Abs(Err.Number)
End If
End Function

Getting a New Primary Key Value

First, AddGenre does some routine setup, but then calls the function GetNewPK (as repeated in the code that follows). GetNewPK will be explained later, but for now, know that it will return the next key for the table that's passed as a string. The value returned from GetNewPK is retained into a local variable.

' get a new primary key value
lngGenreId = GetNewPK("Genre")
' check the return value for the new primary key
If lngGenreId < 0 Then
Exit Function
End If

Also, it's important to note the methodology of a quick exit and the preset error-condition return value. This is a methodology found to work well for a few reasons. This presetting of the return value and quick exit on any error works well in Visual Basic, but even better in eVB. Because there is no error handler in eVB, except for On Error Resume Next, it's nice to assume an error return value unless the function makes it to the end without an error. This allows an Exit Function to be issued on every error condition without having to set the return value each time. It also prevents arrowheads, as demonstrated in this pseudocode:

If everything Is Ok Then
' do something
If That Went OK Then
'do something else
If This 3rd Thing Went OK Then
'do a 3rd thing
and so on and so on....
End If
End If
End If

This methodology is in several places within the application, so familiarize yourself with it. Also, at the end of the function, the Err object's Number property's value is checked. If it's not zero, the return value is set to the negative of the absolute value of the error number. For example, let's say you have a native eVB of 1200. Taking 1 and multiplying it by the absolute value of 1200, gives you 1200; because this value is less than 0, it's an error condition. Use the absolute value in case the error is an ActiveX errorfor example, 800096007. This returns 1 times the absolute value of 80096007, which is still the negative value 80096007.

If Err.Number <> 0 Then
GetNewPK = -1 * Abs(Err.Number)
End If

Checking the Recordset State

Once the value returned from GetNewPK is verified, the state of the Genre recordset object is verified. The verification first checks to see if the recordset object is open by inquiring its State property. If the Genre recordset is open, a check is made to ensure that the recordset currently supports adding a new record by using the Supports method and the adAddNew enumeration. If the recordset doesn't currently support adding new records, it's closed, reopen by:

' if the recordset is open
' let's check if it supports addnew
If gobjGenre.State = adStateOpen Then
' if the current recordset does not
' support adding new records, let's close
' it and reopen it
If Not gobjGenre.Supports(adAddNew) Then
gobjGenre.Close
End If
End If

Opening the Recordset for Adding (or Updating/Deleting)

To open the recordset for adding new, and updating or deleting records, the recordset must have certain attributes. The most important attribute for the recordset to be modifiable is the LockType assigned to it. The LockType property can be set directly:

Recordset.LockType = adLockOptimistic

Or, the LockType can be set via the LockType parameter of the Open method:

Recordset.Open "(
Source for records )", 
connection,  
cursortype,  _
adLockOptimistic

In AddGenre, if the recordset was closed on entry to AddGenre or closed in AddGenre because it didn't currently support adding new records, the recordset is opened. It's opened with

table direct, a preferable method for using ADOCE recordsets. The table direct performs better with SQL Server CE. To open a recordset directly, notice the adCmdTableDirect enumeration value as the optional parameter to the Recordset.Open method. Table direct opens the table directly without using SQL; it's equivalent to SELECT * FROM table.

' if the recordset is closed above
' or closed before we get into this
' function, let's open it with the
' proper support for adding
If gobjGenre.State = adStateClosed Then
gobjGenre.Open "genre", gobjConnection, _
adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
End If

Adding the Record

After the recordset supports adding records, issue the statement AddNew. The AddGenre function issues the AddNew method to the recordset.

' add a new record
gobjGenre.AddNew

Setting the Values for the Recordset's Columns

After the new record is appended to the recordset, the values for that new record are assigned using the Recordset.Fields collection.

The AddGenre function uses a verbose method to assign the values:

' set the values
gobjGenre.Fields("GenreId").Value = lngGenreId
gobjGenre.Fields("GenreName").Value = strGenre

Because the Field object's default property is Value, the column's values can be assigned as follows:

' set the values
gobjGenre.Fields("GenreId") = lngGenreId
gobjGenre.Fields("GenreName") = strGenre

Each Field in the collection can be referenced by the column name or its index in the collection. An example of using the index value is as follows:

' set the values
gobjGenre.Fields(0) = lngGenreId
gobjGenre.Fields(1) = strGenre

And, because the recordset's default property is actually the Fields collection, an even shorter method of assigning the values can be used:

' set the values
gobjGenre(0) = lngGenreId
gobjGenre(1) = strGenre

Committing the Update

To send the update back to the data store, call the Update method:

' update the recordset
gobjGenre.Update

Returning the Primary Key Value

Because the AddGenre function is called from various places within the application to add a new genre on-the-fly, the primary key value of the newly created record is returned:

' set the return value
AddGenre = lngGenreId

Ensuring a New Record's Validity

To ensure that the record has correctly inserted, the eVB error object Err is checked to be sure that no errors occurred throughout the entire function:

' if there was an error, let's set the return
' value to a negative value
If Err.Number <> 0 Then
AddGenre = -1 * Abs(Err.Number)
End If

If an error occurred, the return value is set to the negative value of the Err.Number. Any method that calls AddGenre should check the return value to be zero or greater.

Using the GetNewPK Function to Control Primary Keys

Pocket Access does not support automatically incrementing key values; eVB applications need to control their own primary keys. Typically this is accomplished by using a control table. The CD Library application uses the PKey table to achieve this. Each time a new record is added to any of the other tables (Artist, Genre, CD), GetNewPK finds the appropriate record and increments the value (KeyValue) by one, updates the record, and returns this new value (see Listing 7.13).

Listing 7.13 modGlobal.bas: Getting a New Primary Key Value

Function GetNewPK(ByVal strTable As String) As Long
' gets a new pk for a particular table
On Error Resume Next
' set the return value in case we exit early
GetNewPK = -1
' if the table is open, let's close it
If gobjPKEY.State = adStateOpen Then
gobjPKEY.Close
End If
' open the table with the correct record
gobjPKEY.Open "SELECT KeyValue FROM PKey WHERE TableName = " _
& "" & strTable & "", gobjConnection, _
adOpenForwardOnly, adLockOptimistic
' if there is no record for this table
' we have an error
If gobjPKEY.EOF Then
Exit Function
End If
' set the value to itself plus one
gobjPKEY.Fields("KeyValue").Value = gobjPKEY.Fields("KeyValue").Value + 1
' update the record
gobjPKEY.Update
' set the return value
GetNewPK = gobjPKEY.Fields("KeyValue").Value
' if we had an error, let's set the
' return value to a negative return
If Err.Number <> 0 Then
GetNewPK = -1 * Abs(Err.Number)
End If
End Function

Give It a Whirl

At this point, your CD Library application is ready for testing. It should be able to

through the entire application's codebase in one debugging session.

Using the AddArtist Function




Now, let's implement the AddArtist function (see Listing 7.14). Just as AddGenre adds a record to the Genre table, AddArtist adds a record to the Artist table. AddArtist follows the exact same template as AddGenre.

Listing 7.14 modGlobal.bas: Adding a New Artist




Function AddArtist(ByVal strArtist As String, _
ByVal lngDefaultId As Long) As Long
' adds an artist to the table and returns
' its primary key value
' if there is an error, returns a negative value
Dim lngArtistId As Long
On Error Resume Next
' set the return value to an error value
' in case we exit early
AddArtist = -1
' returns a new id for the new artist
lngArtistId = GetNewPK("Artist")
' checks the return value
If lngArtistId < 0 Then
Exit Function
End If
' needs to test the recordset to see if
' it currently supports adding new records
' let's see if recordset is open
If gobjArtist.State = adStateOpen Then
' if it currently does not support
' adding new, let's close it and reopen
' it below
If Not gobjArtist.Supports(adAddNew) Then
' close the recordset
gobjArtist.Close
End If
End If
' if it was closed before we got into this
' function or closed in this function, let's
' reopen it with the correct support (AddsNew)
If gobjArtist.State = adStateClosed Then
gobjArtist.Open _
"artist", _
gobjConnection, _
adOpenForwardOnly, _
adLockOptimistic, _
adCmdTableDirect
' "artist" - open the table direct
' gobjConnection - the global connection object
' adOpenForwardOnly - we're only going to add,
'   so no need to have scrollable cursor
' adLockOptimistic - quick updates
' adCmdTableDirect - optional parameter that
' informs the recordset to open the table directly
' allowing the quickest manipulation
End If
' append a new record
gobjArtist.AddNew
' set values
gobjArtist.Fields("ArtistId").Value = lngArtistId
gobjArtist.Fields("ArtistName").Value = strArtist
' if we have a genre, let's set it
If Not IsEmpty(lngDefaultId) Then
gobjArtist.Fields("DefaultGenre").Value = lngDefaultId
End If
' update the recordset - commits the record
gobjArtist.Update
' set the return value
AddArtist = lngArtistId
' if we had an error, let's set the
' return value to a negative value
' so we know that it failed
If Err.Number <> 0 Then
AddArtist = -1 * Abs(Err.Number)
End If
End Function




Using the AddCD Function




The final function to be implemented that adds a record to a table is AddCD (see Listing 7.15). Again, AddCD follows the same template.

Listing 7.15 modGlobal.bas: Adding a New CD




Function AddCD( _
ByVal lngArtistId As Long, _
ByVal lngGenreId As Long, _
ByVal strCDName As String, _
ByVal lngYear As Long, _
ByVal curPrice As Currency) As Long
Dim lngCDId As Long
On Error Resume Next
' let's set the return value to
' an error code, so if we exit early
AddCD = -1
' get a new primary key value
lngCDId = GetNewPK("CD")
' check the value to make sure it valid
If lngCDId < 0 Then
Exit Function
End If
' if the CD recordset is open, let's
' check to make sure it can support
' adding a new record
If gobjCD.State = adStateOpen Then
' check to see if the current
' recordset supports adding a new record
If Not gobjCD.Supports(adAddNew) Then
gobjCD.Close
End If
End If
' if the recordset is closed, either
' before we entered the function
' or if we closed it above
If gobjCD.State = adStateClosed Then
gobjCD.Open "cd", gobjConnection, adOpenForwardOnly, _
adLockOptimistic, adCmdTableDirect
End If
' add a new record
gobjCD.AddNew
' set the values
gobjCD.Fields("CDId").Value = lngCDId
' let's set the Artist value
gobjCD.Fields("ArtistId").Value = lngArtistId
' let's set the Genre value
gobjCD.Fields("GenreId").Value = lngGenreId
' if we have an cd name, let's set the value
If Not IsEmpty(strCDName) Then
gobjCD.Fields("CDName").Value = strCDName
End If
' if we have an year, let's set the value
If Not IsEmpty(lngYear) Then
gobjCD.Fields("Year").Value = lngYear
End If
' if we have an price, let's set the value
If Not IsEmpty(curPrice) Then
gobjCD.Fields("Price").Value = curPrice
End If
' update the recordset
gobjCD.Update
' set the return value
AddCD = lngCDId
' if we had an error, let's set
' the return value to a negative value
If Err.Number <> 0 Then
AddCD = -1 * Abs(Err.Number)
End If
End Function




The only structural difference between AddCD and AddGenre or AddArtist is that validation is completed on the values of Year and Price. These fields are checked as to whether they have values. If these fields have valid values, the recordset's field values are set. If not, they are left empty, resulting in a NULL.

Updating the CreateData Function to Add Artists and CDs




Now that the AddArtist and AddCD functions are implemented, the CreateData function in Listing 7.16 can add artists and CDs to the database when it's creating the sample data.

Listing 7.16 modGlobal.bas: Adding Artists and CDs




Private Function CreateData() As Boolean
Dim lngRockId As Long
Dim lngBlueGrassId As Long
Dim lngHeavyMetalId As Long
Dim lngTomPettyId As Long
Dim lngAllisonKrausId As Long
Dim lngMetallicaId As Long
On Error Resume Next
' set the return value to an
' error condition, this allows
' early exit if an error occurs
CreateData = False
' let's add genres
lngRockId = AddGenre("Rock")
' check the return value, should be
' greater than zero
If lngRockId < 0 Then
Exit Function
End If
lngBlueGrassId = AddGenre("Bluegrass")
' check the return value, should be
' greater than zero
If lngBlueGrassId < 0 Then
Exit Function
End If
lngHeavyMetalId = AddGenre("Heavy Metal")
' check the return value, should be
' greater than zero
If lngHeavyMetalId < 0 Then
Exit Function
End If
' let's add some artists
lngTomPettyId = AddArtist("Tom Petty", lngRockId)
' check the return value, should be
' greater than zero
If lngTomPettyId < 0 Then
Exit Function
End If
lngAllisonKrausId = AddArtist("Allison Kraus", lngBlueGrassId)
' check the return value, should be
' greater than zero
If lngAllisonKrausId < 0 Then
Exit Function
End If
lngMetallicaId = AddArtist("Metallica", lngHeavyMetalId)
' check the return value, should be
' greater than zero
If lngMetallicaId < 0 Then
Exit Function
End If
' add cds for Tom Petty
If AddCD(lngTomPettyId, lngRockId, _
"Damn the Torpedos", 1989, 10.99) < 0 Then
Exit Function
End If
If AddCD(lngTomPettyId, lngRockId, _
"Tom Petty Live", 1988, 12.99) < 0 Then
Exit Function
End If
' add cds for Allison Kraus
If AddCD(lngAllisonKrausId, lngBlueGrassId, _
"Best Of Allison Kraus", 1998, 12.99) < 0 Then
Exit Function
End If
If AddCD(lngAllisonKrausId, lngBlueGrassId, _
"Long and Blue", 1999, 15.99) < 0 Then
Exit Function
End If
' add cds for Metallica
If AddCD(lngMetallicaId, lngHeavyMetalId, _
"Master Of The Puppets", 1986, 9.99) < 0 Then
Exit Function
End If
If AddCD(lngMetallicaId, lngHeavyMetalId, _
"Metallica", 1983, 12.99) < 0 Then
Exit Function
End If
If Err.Number = 0 Then
CreateData = True
End If
End Function




The first new section of code simply Dims some new variables to store the artist's primary keys. This follows the same methodology as when CreateData retained the primary key values from the added Genre records:

Dim lngTomPettyId As Long
Dim lngAllisonKrausId As Long
Dim lngMetallicaId As Long




The next change adds the three artists of the sample data, "Tom Petty," "Allison Kraus," and "Metallica." It accomplishes this by calling the AddArtist function. It checks the return value of the AddArtist function. If it's an invalid key, it exits the function, returning the default return value, which was set at the top of the function.

Notice the local variable for the Genre primary key as the second parameter to AddArtist. This stores the GenreId in the Artist table; this is used to set the default genre in the CD form (frmCD) when creating a new CD for the artist.

' let's add some artists
lngTomPettyId = AddArtist("Tom Petty", lngRockId)
' check the return value, should be
' greater than zero
If lngTomPettyId < 0 Then
Exit Function
End If
lngAllisonKrausId = AddArtist("Allison Kraus", lngBlueGrassId)
' check the return value, should be
' greater than zero
If lngAllisonKrausId < 0 Then
Exit Function
End If
lngMetallicaId = AddArtist("Metallica", lngHeavyMetalId)
' check the return value, should be
' greater than zero
If lngMetallicaId < 0 Then
Exit Function
End If




Another Trip Around the Block




Your application should be ready for another testafter all, it should now create all the sample data. There's one catch, because your database is already created, the CreateData function won't automatically execute. CreateData is called from EnsureDatabaseExists and RecreateDatabase (which isn't implemented yet). Your options include placing a call to CreateData at the end of the Form.Load event (this will create duplicate genres and can eventually create errors) or deleting the cd.cdb from your emulator or device. Or, you can jump ahead and complete PopulateMenus, the Menu.Click event, and RecreateDatabase.

Using the InitializeGridVariables Function




Recall the InitializeGridVariables function that we stubbed out early on. Here it is in Listing 7.17. A few things happen here, none that are really important to ADOCE.

Listing 7.17 modGlobal.bas: Setting Up the Grid's Variables




Function InitializeGridVariables() As Boolean
On Error Resume Next
' set the return value to error condition
InitializeGridVariables = False
' set the column width of the
' artist grid to the grid width
grdArtist.ColWidth(0) = grdArtist.Width
' set up the cd grid variables
gstrCDCols(0, 0) = "CDName"     ' field name
gstrCDCols(1, 0) = "Name"       ' caption
gstrCDCols(0, 1) = "Year"       ' field name
gstrCDCols(1, 1) = "Year"       ' caption
gstrCDCols(0, 2) = "Price"      ' field name
gstrCDCols(1, 2) = "Price"      ' caption
gstrCDCols(0, 3) = "GenreName"  ' field name
gstrCDCols(1, 3) = "Genre"      ' caption
' set the grid's first column width to half
' of the grid's width
grdCD.ColWidth(0) = grdCD.Width / 2
' set the current sort order to the first column (CDName)
glngCDSortCol = 0
' check for errors, if none, return True
If Err.Number = 0 Then
InitializeGridVariables = True
End If
End Function




Set the ColWidth of the first column in the artist grid to the full width of the grid.

' set the column width of the
' artist grid to the grid width
grdArtist.ColWidth(0) = grdArtist.Width




Then an array is initialized. This array serves two purposes: it provides the captions for the header (first row) of the CD grid and the names of the respective column from the CD table. The CD field name is used for sorting the CD records by different columns. This re-sorting occurs when the user taps the caption (header) of the CD grid.

' set up the cd grid variables
gstrCDCols(0, 0) = "CDName"     ' field name
gstrCDCols(1, 0) = "Name"       ' caption
gstrCDCols(0, 1) = "Year"       ' field name
gstrCDCols(1, 1) = "Year"       ' caption
gstrCDCols(0, 2) = "Price"      ' field name
gstrCDCols(1, 2) = "Price"      ' caption
gstrCDCols(0, 3) = "GenreName"  ' field name
gstrCDCols(1, 3) = "Genre"      ' caption




The CD grid's first column is set to half the width of the CD grid. This can only be accomplished at runtime.

' set the grid's first column width to half
' of the grid's width
grdCD.ColWidth(0) = grdCD.Width / 2




The public variable (glngCDSortCol) that retains the current sort column of the CD is set to the first column (CDName). This variable is really an index to the array (gstrCDCols).

' set the current sort order to the first column (CDName)
glngCDSortCol = 0




Using the PopulateArtistGrid Function to See the Data




Now that there is data to display, let's get it into that artist grid (see Listing 7.18).

Listing 7.18 Displaying the Artists




Function PopulateArtistGrid() As Boolean
On Error Resume Next
PopulateArtistGrid = False
' if the artist table is open, let's close
' it so we can reopen it under the right conditions
If gobjArtist.State = adStateOpen Then
gobjArtist.Close
End If
' open the table
' ordering by the artistname
gobjArtist.Open "SELECT * FROM artist ORDER BY ArtistName", _
gobjConnection, adOpenForwardOnly, adLockReadOnly
' clear the grids
grdArtist.Rows = 0
grdCD.Rows = 0
grdArtist.Clear
grdCD.Clear
' go top in artist table
gobjArtist.MoveFirst
Do While Not gobjArtist.EOF
' add the artist to the grid
grdArtist.AddItem gobjArtist.Fields("ArtistName")
' next record
gobjArtist.MoveNext
Loop
' check for errors, if none, return True
If Err.Number = 0 Then
PopulateArtistGrid = True
End If
End Function




PopulateArtistGrid does the standard setup with the Error trap and sets the return value:

On Error Resume Next
PopulateArtistGrid = False




Then, the function closes the artist Recordset object if it's open:

' if the artist table is open, let's close
' it so we can reopen it under the right conditions
If gobjArtist.State = adStateOpen Then
gobjArtist.Close
End If




Next, the artist Recordset is reopened using a standard SQL statement that has an ORDER BY clause. This orders the artist data by the ArtistName column. Notice, the recordset specifies a forward-only (adOpenForward) cursor for the CursorType parameter. This recordset is only scrolled through from top to bottom, so any other cursor would consume unnecessary resources. The recordset is also specified to be read-only by including adLockReadOnly for the LockType parameter. There is no update from the artist grid, so again, it's using resources efficiently.

' open the table
' ordering by the artistname
gobjArtist.Open "SELECT * FROM artist ORDER BY ArtistName", _
gobjConnection, adOpenForwardOnly, adLockReadOnly




The next segment of code deals directly with the grids. It's not the scope of this chapter to go into the grids, but basically the grids are both cleared. There is a bug in the grid control that if the rows aren't set to zero before calling Clear, sometimes the device locks up.

' clear the grids
grdArtist.Rows = 0
grdCD.Rows = 0
grdArtist.Clear
grdCD.Clear




Before the grid is populated, the recordset is ensured to be on the first record by issuing the MoveFirst method. Then, the recordset is cycled through and each record's ArtistName value is added to the grid, using the grid's AddItem method. AddItem adds a row to the grid and sets the first column of that row to the value of the parameter passed to it.

After adding the ArtistName to the grid, you need to store the primary key value of the artist record with the row. The grid's RowData collection provides this functionality. The artist record's primary key value allows the application to display the respective CDs in the CD grid. This is implemented in the next two steps.

After the record is processed, the recordset moves to the next record by using the MoveNext method:

' go top in artist table
gobjArtist.MoveFirst
Do While Not gobjArtist.EOF
' add the artist to the grid
grdArtist.AddItem gobjArtist.Fields("ArtistName")
' set the RowData to the primary key of the artist
grdArtist.RowData(grdArtist.Rows - 1) = _
gobjArtist.Fields("ArtistID").Value
' next record
gobjArtist.MoveNext
Loop
' check for errors, if none, return True
If Err.Number = 0 Then
PopulateArtistGrid = True
End If




Once More Around the Block: Let's See Those Artists




At this point, execute your application. You should see the artists populated in the grid.

What Artist? Which CDs?




Now that the artist grid displays the artists, let's get their respective CDs into the CD grid. The functionality of displaying all the CDs for an artist is implemented when a row in the artist grid is tapped. Let's implement the Grid.Click event, grdArtist_Click:

Private Sub grdArtist_Click()
' each time the artist
glngArtistId = grdArtist.RowData(grdArtist.Row)
' refreshes the cd grid for the currently select artist
RefreshCDGrid
End Sub




Remember that public variable glngArtistId declared for the form (frmMain)? This is where the application stores the "current artist." The "current artist" is the artist that has been tapped. The RefreshCDGrid function uses glngArtistId (refer to Listing 7.4) to filter the CD recordset to the CD records that have the ArtistId foreign key value that matches.

Using the RefreshCDGrid Function: Finally, Those CDs!




The RefreshCDGrid function refreshes the CD grid with the CDs that belong to the "current artist." Remember, "current artist" is stored as the primary key value of the artist record in the public variable glngArtistId.

The RefreshCDGrid function in Listing 7.19

column, CDId, isn't displayed.
• 
  

  Adds a header row to the grid, sets the captions, and makes them bold.

• 
  

  Cycles through the CD recordset and adds the CD records to the grid, while setting the RowData to the primary key value of the CD record.




Listing 7.19 Displaying the CDs




Function RefreshCDGrid() As Boolean
Dim strSortField As String
Dim i As Long
Dim strSQL As String
On Error Resume Next
RefreshCDGrid = False
' close the cd recordset if it is open
If gobjCD.State = adStateOpen Then
gobjCD.Close
End If
' build a sql string
strSQL = "SELECT "
' add the columns
For i = 0 To UBound(gstrCDCols, 2)
strSQL = strSQL & gstrCDCols(0, i) & ","
Next
' add the id for the RowData
strSQL = strSQL & "CDId"
' select the data
' sort by the current sort order
strSQL = strSQL & " FROM cd INNER JOIN " _
& " Genre ON cd.GenreId = genre.GenreID "
' let's filter that data to the current artist
strSQL = strSQL & "WHERE ArtistId = " & CStr(glngArtistId)
' and sort that data by the current sort order
strSQL = strSQL & " ORDER BY " & gstrCDCols(0, glngCDSortCol)
' open the recordset, forward only - read only
gobjCD.Open strSQL, gobjConnection, adOpenForwardOnly, adLockReadOnly
' clear the grid
grdCD.Clear
' set the rows to one to cover the column name cells
grdCD.Rows = 1
' set the col count to the field count
' minus one for the CDId (PK field)
grdCD.Cols = gobjCD.Fields.Count - 1
' set up the header row
' move to the first row - 0 based
grdCD.Row = 0
For i = 0 To UBound(gstrCDCols, 2)
'add the label to the grid
grdCD.TextMatrix(0, i) = gstrCDCols(1, i)
' bold the labels
grdCD.Col = i
grdCD.CellFontBold = True
Next
' put the col back to the left
grdCD.Col = 0
' add the records
Do While Not gobjCD.EOF
' add the cd name and row at the same time
grdCD.AddItem gobjCD.Fields("CDName").Value
' set each of the columns values
For i = 0 To UBound(gstrCDCols, 2)
grdCD.TextMatrix(grdCD.Rows - 1, i) = _
gobjCD.Fields(gstrCDCols(0, i)).Value
Next
' set the rowdata to the primary key
grdCD.RowData(grdCD.Rows - 1) = gobjCD.Fields("CDId").Value
' move to next record
gobjCD.MoveNext
Loop
' check for errors, if none, return True
If Err.Number = 0 Then
RefreshCDGrid = True
End If
End Function




The first block of code to examine builds a standard SQL string that retrieves the data from the CD table. The array gstrCDCols is used as the repository of the CD table's displayed columns. The fields are stored in the 0 element of the array. The CD table's primary key is added to the SQL string as well. Although it won't be displayed, it's added to the column list so it can be used to populate the RowData collection of the CD grid. The CD primary key value is used to open the CD edit form if the user taps the CD record.

' build a sql string
strSQL = "SELECT "
' add the columns
For i = 0 To UBound(gstrCDCols, 2)
strSQL = strSQL & gstrCDCols(0, i) & ","
Next
' add the id for the RowData
strSQL = strSQL & "CDId"
' select the data
' sort by the current sort order
strSQL = strSQL & " FROM cd INNER JOIN " _
& " Genre ON cd.GenreId = genre.GenreID "




The SQL string is then appended with the proper syntax to filter the recordset to the "current artist." Remember, current artist is stored in the public variable glngArtistId and is set from the artist grid's RowData when the user select an artist record.

' let's filter that data to the current artist
strSQL = strSQL & "WHERE ArtistId = " & CStr(glngArtistId)




Next, to sort the recordset, an ORDER BY clause is added to the SQL string. The particular sort order is determined by the current value of the public variable glngCDSortCol, which is an index to a row of the array gstrCDCols.

' and sort that data by the current sort order
strSQL = strSQL & " ORDER BY " & gstrCDCols(0, glngCDSortCol)




The CD recordset is then opened using this SQL string. Notice that again, like the recordset for the artist grid, it's opened forward only and read-only. These options are specified by using the ADOCE enumeration values adOpenForwardOnly and adLockReadOnly for the CursorType and LockType parameters, respectively.

' open the recordset, forward only - read only
gobjCD.Open strSQL, gobjConnection, adOpenForwardOnly, adLockReadOnly




The grid is then cleared and the column count (Cols) is set to the number of fields in the recordset minus one (so that it doesn't display the primary key field CDId).

' clear the grid
grdCD.Clear
' set the rows to one to cover the column name cells
grdCD.Rows = 1
' set the col count to the field count
' minus one for the CDId (PK field)
grdCD.Cols = gobjCD.Fields.Count - 1




The header row is then added to the grid. The current row is set to be the first row of the grid by setting the Row property to 0 (the grid's rows are zero-based). A loop cycles through the columns and sets the text of each cell in that first row. The current column is set so that the CellFontBold property will apply, and the cell is set to be bold. The current column is then restored to the first column by using the Col property.

' set up the header row
' move to the first row - 0 based
grdCD.Row = 0
For i = 0 To UBound(gstrCDCols, 2)
'add the label to the grid
grdCD.TextMatrix(0, i) = gstrCDCols(1, i)
' bold the labels
grdCD.Col = i
grdCD.CellFontBold = True
Next
' put the col back to the left
grdCD.Col = 0




Lastly, the data is populated onto the grid. As with the artist grid, you cycle through the records. For each record, you append a new row to the grid by using the AddItem method of the grid. The call to AddItem also sets the value of the first column equal to CDName. Moving across the grid, each row of the gstrCDCols array is queried for its column name and the value of the corresponding Field in the recordset is applied to the row. And, the RowData is set to the primary key value of the record. Last, the recordset is advanced by using the MoveNext method.

' add the records
Do While Not gobjCD.EOF
' add the cd name and row at the same time
grdCD.AddItem gobjCD.Fields("CDName").Value
' set each of the columns values
For i = 0 To UBound(gstrCDCols, 2)
grdCD.TextMatrix(grdCD.Rows - 1, i) = _
gobjCD.Fields(gstrCDCols(0, i)).Value
Next
' set the rowdata to the primary key
grdCD.RowData(grdCD.Rows - 1) = gobjCD.Fields("CDId").Value
' move to next record
gobjCD.MoveNext
Loop




Are We There Yet?




Start your application and tap the different rows in the artist grid. Does the grid refresh? Does it have the correct records in it?

Tapping the CD Grid




When the user taps a particular CD record in the CD grid, the application should display that CD record for editing. To accomplish this, implement the Grid.Click event (see Listing 7.20) for the CD grid.

Listing 7.20 Implementing the Grid.Click Event




Private Sub grdCD_Click()
If grdCD.Row = 0 Then
' the user clicked the first row
' let's change the sort order and
' refresh the cd grid
glngCDSortCol = grdCD.Col
RefreshCDGrid
Else
' if the user clicked on a cd
' let's open the cd form
frmCD.CDOpen grdCD.RowData(grdCD.Row), glngArtistId, CDFORM_MODE_EDIT
End If
End Sub




First, the subroutine checks the current row of the CD grid (grdCD). When the grid control is tapped, it sets the current Row (and Col) to the row and column tapped.

If the header row is tapped, the Row value will be 0. If Row = 0, the value of glngCDSortCol (the current sort order for the grid) is set. Then, the CD grid is refreshed to reflect this new sort order.

If any other Row is tapped, the CD form (frmCD) is opened using a custom subroutine, CDOpen. The primary key value, the "current artist" and the Const CDFORM_MODE_EDIT are passed as parameters. For this to work, you need to implement the CD form.