More About the Form and the Code
This section isn't necessary for your understanding of our test. We wanted to include it anyway because we believe that you want to see code to feel good about an example—at least, if you're at all like us, this is what you want. Otherwise, if you're not interested in much code or explanations, you can skip this part and go directly to the section "Three Different Ways of Relating Children to Parents" in this chapter.
The Racetrack Combo Box
The racetrack combo box in the upper left corner of the form, now displaying the name of the Jägersro Galopp racetrack, is a DataCombo control. You'll find it in the Microsoft DataList Controls 6.0 (OLE DB) component. In the form, we've bound this control to the adcTracks ADO data control in the lower left part of the form. The following properties are the only two that are relevant to our test:
| Property | Value |
|---|---|
| RowSource | adcTracks |
| ListField | TrackName |
The RowSource property binds the DataCombo control to the adcTracks ADO data control. The ListField property specifies that the list's content should come from the TrackName field in the recordset contained in the data control. Setting these properties makes the communication between the combo box and the data control automatic; you don't have to write any code to fill the combo box with data. All you have to do is make sure that the data control's recordset contains the data needed. This is what the following code takes care of:
Private Sub Form_Load() Dim objTracks As HierarchyTest Set objTracks = CreateObject("HierarchyServer.HierarchyTest") Set adcTracks.Recordset = objTracks.GetAllTracks setupRaceGrid setupEntrantsGrid optRelate(0) = True Me.Show dcmbTracks.SetFocus End Sub |
It's the two lines printed in boldface type that indirectly fill the track combo. The first line creates a business object in the server project shown in Figure 19-3. The name of the business object is HierarchyTest, and it's defined in a project named HierarchyServer. As you can see in Figure 19-3, this project contains the HierarchyTest class only, a class that we created to play the roles of several different business objects.
Figure 19-3. Our project group consists of two projects: a client project containing a form and a server project containing one class, one ordinary Microsoft Visual Basic module, and one Data Environment.
The second code line in the Form_Load procedure calls the GetAllTracks method of the HierarchyTest object created on the first line. The GetAllTracks method returns an ADO recordset that contains one record for each registered racetrack. The second code line then saves that recordset to the Recordset property of the adcTracks data control. Since the racetrack combo box is data bound to that data control, the names of the tracks included in the recordset automatically display themselves in the combo box without any extra code.
The GetAllTracks method
As you've already seen, the HierarchyServer project contains one class only. We were somewhat lazy when we designed the HierarchyTest class; it should really have been a Tracks class, a RaceDays class, a Races class, and a RaceEntrants class, but we made the HierarchyTest class represent them all. We did that to simplify our test.
The first time we use the HierarchyTest object is at Form_Load, where the HierarchyTest object plays the role of a Tracks object. The Form_Load event procedure calls the GetAllTracks method, which is coded as follows:
Public Function GetAllTracks() As Recordset On Error Resume Next deHierarchy.rsGetAllTracks.Close deHierarchy.GetAllTracks Set GetAllTracks = deHierarchy.rsGetAllTracks Set GetAllTracks.ActiveConnection = Nothing End Function |
As you can see, the GetAllTracks method has no real error code, and there's no code calling SetComplete, SetAbort, or any other MTS or COM+ feature. We designed it that way on purpose. For maximum simplicity and focus, we included only the core functionality of the method, and this is what you see in the preceding code. Your sharp eye might also have noticed the de prefix of the deHierarchy object name in three of the four lines in the preceding code example. We use that prefix to indicate a Data Environment object, and we'll show you how we used this Data Environment in the next section. But first we'd like to explain the GetAllTracks code.
The first real code line (after the On Error line) closes the rsGetAllTracks recordset of the deHierarchy Data Environment. This is necessary if the rsGetAllTracks recordset is already open. If you don't close it before you try to open it again (see the next line of code), you'll get an error when you try to open it. But the first time you call this method, the recordset won't be open. If you try to close a recordset that isn't open, another error occurs. We're willing to overlook this error, knowing what it is. That's the reason for having the first code line. When—not if—the Can't Close A Recordset That Is Not Open error occurs, the error is ignored and Visual Basic just continues to execute the next line. In a real application, you need more error handling, but for this demo we'll manage with this single line.
The third line just returns the recordset created by the Data Environment command object, and the fourth and last line disconnects it from the database.
The GetAllTracks Data Environment command
As we already mentioned, we used a Data Environment command object for our data access here. Since we never intended to run this component in MTS or in a COM+ application, we could get away with that, and it's a great help. Figure 19-4 is a screen shot of the deHierarchy Data Environment; it lets you see a list of its commands. The only command we expanded before taking the screen shot is the one we're especially interested in right now, the GetAllTracks command.
Figure 19-4. A Data Environment object helps us fulfill our data access needs.
The GetAllTracks command is a very simple command. It's not hierarchical, and its SELECT statement, shown in the following code snippet, is also very simple because it always fetches every racetrack entered into the database.
SELECT TrackId, TrackName FROM RaceTracks ORDER BY TrackName |
You'll soon see Data Environment commands much more advanced than this one, but what we've just shown you is all you need to return an ordered list of racetracks.
The Race Dates List Box
The race dates list box is a DataList control, which, like the racetrack combo box, you'll find in the Microsoft DataList Controls 6.0 (OLE DB) component. We've bound it to the adcDates ADO data control, present with the other data controls at the bottom of the form. Of the race dates DataList control's properties, only the following two are relevant to the purpose of our test:
| Property | Value |
|---|---|
| RowSource | adcDates |
| ListField | RaceDate |
The effect of setting these properties is similar to the effect of setting the corresponding properties of the racetrack combo box described earlier in this chapter; the list box for showing race dates is data bound to the adcDates ADO data control, and the list contains values from the RaceDate field of that data control's recordset. The race dates list box is filled every time the user selects a racetrack in the racetracks combo box. When this happens, meaning when the user clicks on the name of a racetrack, the form reacts by executing the following event procedure:
Private Sub dcmbTracks_Click(Area As Integer) If Not IsNull(dcmbTracks.SelectedItem) Then ShowRaceDates End If End Sub |
Because this is an event procedure, we don't want to put real processing code in it. We prefer to have all event procedures call named procedures, in this case the ShowRaceDates procedure. Furthermore, we use the IsNull function to filter away any irrelevant click events because such clicks are known to occur. Here's the code for the ShowRaceDates procedure:
Private Sub ShowRaceDates() Dim objDates As HierarchyTest Set objDates = CreateObject("HierarchyServer.HierarchyTest") adcTracks.Recordset.AbsolutePosition = dcmbTracks.SelectedItem Set adcDates.Recordset = objDates.GetDatesForTrack _ (adcTracks.Recordset!TrackId) Set dlstDates.RowSource = adcDates ClearRaces ClearEntrants End Sub |
As you can see from the declaration in the head of the procedure and from the first line of code after the Dim statement, we begin by creating a new object of the same class we used in the earlier example. This, as you might remember, is our HierarchyTest "chameleon" that plays so many roles. In this case, it plays the role of a RaceDates object.
The method we want to call is the GetDatesForTrack method of the HierarchyTest object. This method accepts one argument only, which is the ID of the track selected. The track ID is available in the data control's recordset, but the recordset isn't yet synchronized to the selection made by the user in the racetrack combo box. Before reading the track ID from the recordset, we must synchronize it; otherwise, we'll get the ID of any track record that happens to be current. To synchronize the recordset, making the selected track the current record, we must first read the value of the SelectedItem property of the DataList control. This property returns the ordinal number of the item selected in the list. Because that number is 1-based, meaning that the first item of the list has 1 as its ordinal number, and because the AbsolutePosition property of the recordset also is 1-based, we can use the SelectedItem property value and assign it to the AbsolutePosition property of the DataList control. This action makes the current record pointer of the recordset move to the required position to point at the same racetrack that was selected in the list box. This is what the second line of the preceding code segment does.
When the third line is executed, the selected track is also the current track in the recordset; you can therefore easily read the ID of the selected track from it. You can then send this value as an argument when calling the GetDatesForTrack method of your chameleon object. This is what the fourth line does. The same line also takes the recordset returned from the GetDatesForTrack method and saves it in the recordset property of the adcDates data control, thereby causing the race dates list box to be refreshed.
Well, it should refresh the contents of the list box, but it doesn't, at least not automatically. You must write one additional line of code that rebinds the list to the data control; doing that refreshes the bound list box. Rebinding data-bound controls to a data control is something you must do every time you replace the recordset of the data control with a new one, as we do in this case. This is the reason for the next code line. It rebinds the RowSource property of the visual control to the data control, thereby refreshing the contents of the combo box.
Please notice that we italicized replace in the preceding paragraph. We did that because replace is really a key word in this context. If the data control doesn't have a recordset, which is the case when the Form_Load event occurs, the data control automatically refreshes all the controls bound to it when it receives its first recordset. If the data control already has a recordset in its Recordset property and gets another one that replaces the first one, the data-bound controls aren't automatically refreshed. For example, the first time a user selects a racetrack he or she gets a set of race days without rebinding; the following times he or she doesn't.
We see this as a peculiarity of the binding mechanisms. We don't want to call it a bug, but that's mainly because we are such nice and polite guys. Incidentally, you only have to rebind the RowSource property of the bound control. The row source is the recordset, and this is the only thing that has changed; the rest of the binding attributes remain active.
Clearing the grids
Finally, the procedure clears the grids by calling the ClearRaces and ClearEntrants procedures. These two look a lot like each other, which you can see in the following code snippets:
Private Sub ClearRaces() grdRaces.Clear setRaceGridHeaders grdRaces.Rows = 2 End Sub Private Sub ClearEntrants() grdEntrants.Clear setEntrantGridHeaders grdEntrants.Rows = 2 End Sub |
Both of them begin by clearing the content of their respective grids, something that will also clear the headers in row 0. That's why you must restore the headers, which is done in the second code line of each procedure. Finally, each procedure makes the clearing behavior consistent by always making the grid contain two rows: one header row and one empty data row. If you exclude the last code line of each procedure, you'll wind up with a different number of empty data rows almost every time you clear the grids. Just for completeness, here's the procedure for setting the headers of the Races grid:
Private Sub setRaceGridHeaders() Dim intCol As Integer grdRaces.Row = 0 For intCol = 0 To grdRaces.Cols - 1 grdRaces.Col = intCol Select Case intCol Case 0 grdRaces.Text = "Race" Case 1 grdRaces.Text = "Distance" Case 2 grdRaces.Text = "Track Type" Case 3 grdRaces.Text = "Name of race" End Select Next End Sub |
We won't bother you with a detailed explanation of this code except to say that both grids are Microsoft FlexGrids, which you'll find in the Microsoft FlexGrid Control 6.0 component.
The GetDatesForTrack method
Now it's time to take a look at the GetDatesForTrack method of our main business component, the chameleon HierarchyTest class:
Public Function GetDatesForTrack(strTrackId As String) As Recordset On Error Resume Next deHierarchy.rsGetDatesForTrack.Close deHierarchy.GetDatesForTrack strTrackId Set GetDatesForTrack = deHierarchy.rsGetDatesForTrack Set GetDatesForTrack.ActiveConnection = Nothing End Function |
As you can see, this method is very similar to the GetAllTracks method. The only real difference is that the GetDatesForTrack method takes an argument, the ID of the selected track. This ID value is used as an argument to the GetDatesForTrack command in the Data Environment. The rest is the same. The code closes the recordset before calling the command object, just as the GetAllTracks method did. It also returns the recordset received, and it closes its connection to the database by setting its ActiveConnection property to Nothing. All this follows the same pattern as the code in the GetAllTracks method.
The GetDatesForTrack Data Environment command
The following SQL code of the Data Environment command responsible for selecting every race date for a specific track isn't much more complicated than the one we used to get all the tracks.
SELECT RaceDate, RaceDayNmbr, TrackId FROM RaceDays WHERE (TrackId = ?) ORDER BY RaceDate |
The only interesting part is the line printed in boldface type. It uses a question mark to represent the TrackId of the selected track. This causes the Data Environment command object to create a Parameter object for the TrackId. Figure 19-5 shows the Parameters tab of the Data Environment command dialog box. In the list box on the left side of the dialog box, you can see a list of the command object's parameters; for the GetDatesForTrack command shown in the figure, there's only one parameter. In the text and combo boxes to the right, you can see the properties of the parameter selected in the list box. The Data Environment command automatically creates one parameter object for each question mark entered in the WHERE clause of the command's SQL statement.
Notice how the Data Environment command object has selected the adVarChar data type, which of course corresponds to the Varchar data type in Microsoft SQL Server and other similar DBMSs. Notice also how the Data Environment has selected the proper data type (String) for the host, which, of course, is Visual Basic.
Figure 19-5. The GetDatesForTrack command object has a parameter that represents the TrackId of the selected track.
Revisiting the data access code
Let's take another look at the data access code in the GetDatesForTrack method, just to see how it uses this parameter object to get the dates for the right track:
Public Function GetDatesForTrack(strTrackId As String) As Recordset On Error Resume Next deHierarchy.rsGetDatesForTrack.Close deHierarchy.GetDatesForTrack strTrackId Set GetDatesForTrack = deHierarchy.rsGetDatesForTrack Set GetDatesForTrack.ActiveConnection = Nothing End Function |
The most interesting parts of the preceding code are printed in boldface type:
- The method takes a string argument, the ID of the track.
- It uses this argument when it calls the Data Environment command object.
This is one of the nice things about the Data Environment; it lets you call its command objects as if they were methods of the Data Environment object, and it lets you use arguments exactly as you would were deHierarchy a business object of your own creation rather than a Data Environment. Data Environment goes a long way toward simplifying things for you when it comes to data access. And yet, as you'll see in this chapter and as you saw in the specific section about the combination of COM+ or MTS and the Data Environment in Chapter 14, "Using Microsoft Transaction Server," the Data Environment still has some distance to go before it's really useful in several situations.
The Races Grid
You've already seen some of the programming that concerns the Races and Entrant grids, but the code snippets we've shown you so far mainly concern the emptying and preparation of the grids. Now let's take a look at how we fill them.
Filling the Races grid
The first grid to be filled is the Races grid. You need code that fills it whenever the user selects one of the dates in the list box in the left part of the form. The following event procedure reacts to the user's click on a date in that list box:
Private Sub dlstDates_Click() If Not IsNull(dlstDates.SelectedItem) Then ShowRaces End If End Sub |
The preceding code executes when the user clicks on one of the race dates displayed in the dlstDates DataList control. The code is similar to the code taking care of clicks in the racetrack combo box. As you can see from the example, the event procedure calls the ShowRaces method every time the click is a real one, meaning one that selects a date. If the user just clicks on the control, without selecting a date, the SelectedItem property of the control returns Null. For those clicks, the preceding event procedure doesn't take any action at all. Let's take a closer look at the ShowRaces method, even though there's not any real code in it yet. But, as you can see in the following code snippet, the plan for the method is established, and we have inserted comments that lay out this plan:
Private Sub ShowRaces() Dim rs As Recordset, intRow As Integer, intCol As Integer Dim objRaces As HierarchyTest, intRelateMethod As Integer ' Establish which relate method to use. |
' Get the races together with entrants. The method declares a few variables needed for its operation. Then it takes three steps, which together form the operation of the method:
- The first step is to establish which means to use for relating parent records to child records. This is for test purposes only; we're going to test three different ways of relating races to horses. The reason for this test is to show you the vast difference in result and performance these methods give you.
- The second step is to get the races for the selected race day from the database.
- The third and final step is to make sure that the races received in the second step are properly displayed in the grid control of the form.
Let's take a look at the code for each of these steps, one by one.
Establishing the relate method
We've designed the form so that you can select from the three different methods of relating races to race entrants. You make your choice by selecting one of the option buttons near the top of the form. For example, in Figure 19-6 a parameter in the command object fetching race entrants relates to a field in the recordset containing races.
Figure 19-6. With this setting, the parent and child parts of the hierarchical recordset will be related to each other by way of parameters.
In the following version of the ShowRaces method, we've added code that finds out which method the user of the form has selected. The choice is saved as a numeric value between 0 and 2 in the intRelateMethod variable.
Private Sub ShowRaces() Dim rs As Recordset, intRow As Integer, intCol As Integer Dim objRaces As HierarchyTest, intRelateMethod As Integer ' Establish which relate method to use: For intRelateMethod = 0 To 2 If optRelate(intRelateMethod) Then Exit For End If Next ' Get the races together with entrants. |
This step uses a loop that asks each of the option button controls whether it has been selected. An option button returns True when selected, False when not selected. Because the three option buttons are placed in a frame control on the form, they all belong to the same group, so only one of them can be selected. Also, one of them must be selected, and there's no way the user can escape that. So the loop goes on until it finds the selected button, and then it stops. When the loop stops, the intRelateMethod variable reflects which of the three methods for relating races to race entrants the user selected.
Getting the races
The next step is to get the races from the database. To do that, the method must know two things: which method to use for relating race entrants to races and which race day to fetch races for. The first condition is already taken care of; now it's time to find out which races to fetch. The following version of the ShowRaces method highlights, using boldface type, the code lines that find out which races to fetch and also calls the GetRacesForDay method of a HierarchyTest object:
Private Sub ShowRaces() Dim rs As Recordset, intRow As Integer, intCol As Integer Dim objRaces As HierarchyTest, intRelateMethod As Integer ' Establish which relate method to use. |
This code looks a lot like the code used to get the racetracks data. It creates an instance of the same chameleon HierarchyTest class, it sets the absolute position in the race day recordset, and it calls the GetRacesForDay method of the object created. Two arguments go with this call:
- The first argument corresponds to the key of the RaceDays database table; it's also the content of the RaceDayNmbr field in the adcRaces recordset. This argument helps the server fetch the right set of races.
- The second argument establishes which means the business services method should use to relate race entrants to races; it's also the content of the intRelateMethod variable. This argument helps the GetRacesForDay method use the selected way to relate the race entrant records to the races records.
Displaying the races
The third and last step of the ShowRaces method displays the races fetched. We use a lot more code for this step than for all the others together. The reason is that we didn't use data binding here. We moved each piece of data from the recordset to the FlexGrid control using code. We had in principle two reasons for that:
- We decided to combine two fields from the recordsets into one column of the grid—see Case 1 in the following code example. We could have changed the SELECT statement of the command object to achieve the same result, but we didn't do that.
- We didn't want to include all the fields from the recordset in the grid, and we found it more convenient to control that by means of code than to manipulate the data binding mechanism.
Anyway, here's the code for displaying the selected races. As before, the code you should focus on is printed in boldface type:
Private Sub ShowRaces() Dim rs As Recordset, intRow As Integer, intCol As Integer Dim objRaces As HierarchyTest, intRelateMethod As Integer ' Establish which relate method to use. |
The code isn't very complicated. It begins by setting up a recordset variable that points to the recordset already available in the adcRaces data control. This action doesn't create a copy of the recordset; it just creates another variable that points to it. Two reasons made us create this variable. The first reason is the most important one; using rs rather than adcRaces.Recordset each time we refer to the recordset makes the code easier to read and understand. The second reason is less important—it's more, as we'd say in Sweden, like getting cream on the mashed apples; using rs rather than adcRaces.Recordset in a loop gives a minor performance boost. The rs variable points directly to the recordset and requires no extra COM call, whereas adcRaces.Recordset requires one extra COM call to the adcRaces control to find the recordset. As we said, the performance boost is minor because we don't run through the loop more than maybe 8 or 10 times each time the ShowRaces method is called, but as with the cream on the mashed apples, it's nice if you can get it.
After setting the rs variable to point to the recordset, the code calls the ClearRaces and Clear Entrants methods to clear the two grid controls. Then it sets the two integers intRow and intCol to 0, allowing the processing to start from the first row and the first column of the grid. With these values set, it begins to loop and works its way through the recordset until it reaches the end of it, represented by the EOF property of the recordset. For each record, the code increases the value of the intRow variable by 1; then, using the new intRow value, it adds one row to the grid and makes the new row the current row. Finally, for each row, it walks through all the columns, fetching for each column a value from the proper field in the recordset. For each turn in the loop, it uses the MoveNext method of the recordset to move to the next record. When no records remain to be read, the MoveNext action hits EOF to exit the loop.
The GetRacesForDay method
Now let's take a look at the GetRacesForDay method in the HierarchyTest object. Here goes:
Public Function GetRacesForDay(lngDayNmbr As Long, _ intCommandAlternative As Integer) As Recordset On Error Resume Next Select Case intCommandAlternative Case 0 ' Relate by parameter deHierarchy.rsGetRacesForDayParam.Close deHierarchy.GetRacesForDayParam lngDayNmbr Set GetRacesForDay = deHierarchy.rsGetRacesForDayParam Case 1 deHierarchy.rsGetRacesForDayField.Close deHierarchy.GetRacesForDayField lngDayNmbr Set GetRacesForDay = deHierarchy.rsGetRacesForDayField Case 2 Set GetRacesForDay = SpecialGetRacesForDay(lngDayNmbr) End Select End Function |
This method uses the value of the second argument—the intCommandAlternative integer—to decide which object to call to fetch the races together with their entrants.
- If the value is 0, the method makes use of the GetRacesForDayParam command in the Data Environment command object, relating parent and child to each other using a parameter.
- If the value is 1, the method takes advantage of the GetRacesForDayField command in the Data Environment, this time relating parent and child to each other through a common field in each of the two recordsets.
- If the value is 2, the method doesn't use a DataEnvironment command object at all; it calls the SpecialGetRacesForDay custom-made method to achieve the same purpose in a more efficient manner. We'll come back to that method later in this chapter.
EAN: 2147483647
Pages: 133