| 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. Figure 7.1. The initial form for the sample application CD Library. In the upper grid, a listing of all artists is displayed. The lower grid displays all the CDs for the currently selected artist.  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. Table 7.1. Table Definitions | Column | Type | Description | | Artist Table | | ArtistId | Int | Unique Id for the Artist record. | | ArtistName | VarChar(64) | Name of the artist. | | DefaultGenre | Int | Foreign Key to the Genre table. Default Genre when adding a new CD for the Artist. | | Genre Table | | GenreId | Int | Unique Id for the Genre record. | | GenreName | VarChar(64) | Name of the genre. | | CD Table | | CDId | Int | Unique Id for the CD record. | | CDName | VarChar(64) | Name of the CD. | | Year | Int | Year the CD was released. | | Price | Float | Price paid for the CD. | | GenreId | Int | Foreign Key to the Genre table. | | Primary Key Table (PKey) | | TableName | VarChar(20) | Name of the table. | | KeyValue | Int | Last value used for the primary key for the table. | 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. Figure 7.2. On this particular install of eVB, both versions of ADOCE3.0 and 3.1are available. This application requires only ADOCE 3.0, but is compatible with both ADOCE 3.0 and 3.1.  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. Figure 7.3. Components used in CD Library application are checked on in the Components dialog.  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. Figure 7.4. A relation diagram shows the tables and their relationships to each other.  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 -
Check if the database exists -
Create the database if it doesn't exist -
Create the tables -
Add a record to the PKey control table for each entity -
Create a new primary key value for each entity -
Add three genre records To test your CD Library application in its current state, place a breakpoint on the first executable line of the Form.Load event. This allows you to walk 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 -
Closes the CD recordset. -
Builds a SQL string (that241 includes a JOIN). -
Opens the CD recordset using the SQL string. -
Clears the CD grid. -
Sets the number of columns in the grid to the number of columns in the recordset that should be displayed. The primary key 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. | 
