[thelist] combining recordsets (WAS - sorting recordset problems)

[Ken Schaefer]

Hi,

I'm not really sure that MMDDYYYY is a logical date format.

YYYYMMDD would make things much easier to sort, and has the benefit of being
an ISO standard.

In terms of adding additional records, don't "close" the recordset at the end
of the first loop. Just get a file listing from the second directory, and
keep calling .AddNew until that loop is completed as well.

Cheers
Ken

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Pringle, Ron
Sent: Friday, 6 January 2006 3:12 AM
To: 'thelist at lists.evolt.org'
Subject: [thelist] combining recordsets (WAS - sorting recordset problems)

Following on my previous post, I renamed all the files so that they properly
conform to standard date naming conventions, which makes sorting a lot
easier. The files now follow a naming convention of xxxMMDDYYYYzzz.pdf where
xxx is the 3 digit committee code and zzz is the 3 digit type code (either
agd for agenda or min for minutes).

The next problem I've encountered is:

Minutes and Agendas are in two separate directories. Currently I create two
on-the-fly recordsets to hold the results from each directory. I want to
combine the two recordsets into one by comparing the first 11 places of the
filename string, and if they match, join them into one record. This is so I
can display the Agendas and Minutes for any given date next to each other if
both exist. If only one exists, then it should just display the one with a
blank table cell for the other.

Creating recordsets on the fly is new to me, I've only ever worked with
creating recordsets from DB connections.

Exactly how do I create the combined recordset? Do I append one to the
other, or create a new recordset based on a JOIN of the two previous
recordsets?

Below is the code I've used to create the Agenda recordset (with an
identical Minutes recordset named rsFSOMinutes):

------------------------
'create recordset
Set rsFSOAgenda = Server.CreateObject("ADODB.Recordset")
With rsFSOAgenda.Fields
.Append "Name", adVarChar, 200
.Append "URL", adVarChar, 200
.Append "Size", adInteger
End With
rsFSOAgenda.Open()

'populate it
For each objFile in objFolder.Files
rsFSOAgenda.AddNew
rsFSOAgenda("Name") = objFile.Name
rsFSOAgenda("URL") = linkPath
rsFSOAgenda("Size") = objFile.Size
rsFSOAgenda.update
Next

'write results
rsFSOAgenda.Sort = "Name ASC"
rsFSOAgenda.MoveFirst()
While Not rsFSOAgenda.EOF
[...Write results in HTML Table...]
rsFSOAgenda.MoveNext()
Wend
rsFSOAgenda.close()
Set rsFSOAgenda = Nothing
--------------------------

Apologies for the long post.

Regards,
Ron
--