![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
The Recordset object is not only powerful, it is also complex. To complicate matters, you can choose from three types of Recordsets: Table, Dynaset, and Snapshot. Each uses basically the same commands, but they interact differently with their underlying table (called the base table) or tables. The Recordset types provide the following:
Of course, my descriptions of the three types of Recordsets are rather oversimplified. Other differences exist among them that only come into play in the most complicated and demanding types of database applications, where tables contain hundreds of thousands of records. With a few exceptions, such as the Snapshot-type Recordsets inability to update records in the base table, the three types have the same capabilities. The main difference among them is in database performance (speed). Further discussion in this area is beyond the scope of an introductory book, but the amount of published information is abundant if you are interested. I will just leave you with this bit of advice: When in doubt, use a Dynaset.
Finding A Record Finding a particular record in the database table is also simplified by the Recordset objects methods. Our database program will be limited to searching the Title field, but the same methods apply to searches, or queries, on other fields. While a Recordset method performs the actual search, some additional programming is required to prepare for the search and respond to its result. This step is accomplished in the FindRecord procedure, which is shown in Listing 19.16. Listing 19.16 The FindRecord procedure. Public Sub FindRecord() Dim Template As String Dim Previous As String Template = InputBox(Find what?, Find a Title) If user selects Cancel a blank string is returned. If Template = Then Exit Sub Save the current position. Previous = Data1.Recordset.Bookmark Set up the search template and use the FindFirst method to perform the search. Template = [Title]= & Chr$(34) & Template & Chr$(34) Data1.Recordset.FindFirst Template If no match, return to original record and display a message. If Data1.Recordset.NoMatch Then Data1.Recordset.Bookmark = Previous MsgBox (No match found) End If End Sub The procedure first uses the InputBox function to ask the user what to search for. InputBox displays a dialog box with a prompt, a Text Box where the user types a response, and OK and Cancel buttons. If the user selects OK, the string entered in the Text Box is returned by the function. If the user selects Cancel, an empty string is returned, in which case the user exits the procedure. The next step is incorporating the desired search template into a command string that can be passed to the Recordset objects FindFirst method. This string consists of the name of the field you are querying (in brackets), followed by an equal sign and the string for which you are searching (in single or double quotes). For example, to find records where the Title field contains Rubber Soul (the title of a Beatles album, for those of you too young to know), you would use this command string: [Title] = Rubber Soul While either single or double quotes will work to enclose the search string, I prefer using double quotes. This permits the search string itself to contain a single quote, letting us search for other Beatles titles, such as Hard Days Night. Because you cannot enter a double quote directly into a Basic string, use the Chr$ function to return the double quote character, passing the argument of 34 (the ASCII code for the double quote). You can see how this is accomplished in the listing. The FindRecord procedure uses the Recordset objects Bookmark property to keep track of the record that is current before the search is performed. This step is necessary: If the FindFirst method finds no match, it makes the first record in the table current. If you want the record that was displayed when the search was initiated to remain displayed should the search fail, you must make a note of it and explicitly return there. The Bookmark property returns a string value that identifies the current record. Understanding the nature of this string is not necessary. Just save it. If the search fails, assign it back to the Recordsets Bookmark property to make that record current again. How do you know if the search has failed? Because the Recordset objects NoMatch property is False unless a search has failed, we can determine the search outcome by testing this property. If the search is successful, the matching record automatically becomes current and is displayed in the bound controls. The FindFirst method looks for the first record that exactly matches the search template. The FindLast method finds the last matching record, in effect searching from the end of the table. If you have used FindFirst to locate the first match, you can use the FindNext method one or more times to locate subsequent matching records.
Listing All Records As with the earlier address-list database program, we would like the capability to display an alphabetical list of all the records in the database. As before, well use a separate form with a List Box control, adding a Command Button to close the list window. As an added feature, well include code that will display the programs main form, the entire record for a title when the user clicks on that title in the list.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |