Updating ADOR Recordset Efficiently

[vbsJer]

I am new to vbScript and working with a recordset of music file names. My program presents numbered and

sorted lists of files on the console, in panels of 55 items per page, anywhere from 60 to 17,000+ files

depending on program options chosen. The recordset is re-built when a different list is chosen,

for example, MP3-only, MIDI-only, both types, Favorites-only. The numbers added to the list are used to

identify the song file by providing the information needed to move to the DataList record to get the file name.

 

Here is sample code that creates the recordset:

Set DataList = CreateObject(“ADOR.Recordset”)

DataList.Fields.Append “Music File”, adVarChar, MaxCharacters

DataList.Open

Do While Not objStream.AtEndOfStream

filename = objStream.ReadLine

DataList.AddNew

DataList (“MusicFile”) = filename

DataList.Update

Loop

 

The program has the option of saving a list of favorites, and they are stored in a text file of song file names: _Favorites.m3u

What I want to add is a + (plus mark) to the console list, in front of each song file that has been selected as a favorite.

 

So my question is: can my DataList recordset with an added favorites flag field be updated efficiently

each time the recordset is loaded, and can it be updated efficiently when favorites are removed from the favorites list.

 

Let’s say that the file “Zing.mp3” is one of the favorites. What is the best way to move to the record to update the flag field?

I can easily move to and update the field when adding a new favorite because I know the record position in the recordset.

When removing a favorite or re-building the DataList, updating the flag field at that time is the tricky part of this scheme.

[justsomeguy]

I guess one way would be to read all of the favorites and store those in a data structure like an array or dictionary, and then when you're building the recordset you can check if each item is in the list of favorites and set the field appropriately.

[vbsJer]

Your suggestion to use a dictionary object to navigate a recordset works. The dictionary records the first record position of each alpha character.

So let's say I want to remove "Prelude.mp3" as a favorite. Knowing from the dictionary that "P" starts at record 733, I can move to record #733

on the recordset and begin comparing file names at that point until I either find the record and change favFlag from "+" to " ", or I've reached

letter "Q", in which case no change is necessary because Prelude.mp3 is not in the current recordset.

 

The point is, doing a partial search of the sorted recordset of filenames when initializing flags for display or removing flags from the display,

seems more efficient than searching the recordset from top to bottom for each item. The dictionary is also used as a tool to navigate

and display data beginning with a specific letter of the alphabet.This has been solved. Thanks for your part in the solution.