Disconnected Recordsets

I'm working on a script that is grabbing the UNC path, the date last
modified, and the creator owner for all folders and files in a specific
directory and feeding this data into a disconnected recordset. Pretty
much using the code as given in the Windows 2000 scripting guide to
populate the recordset and do a sort. The goal is to sort by
creator/owner and then feed each unique user ID into an array to be
used later in the script. Since a creator owner can appear in the
Owner column for each file they own, I need to have a way to have that
ID only appear in the array once. SQL has a "distinct" clause which
would do the trick, but I have no idea how to do this in a disconnected
recordset. Any samples I've seen in the MSDN are for VB and I'm not
sure how to translate those into script. My guess is that SQL command
would look something like "SELECT DISTINCT [column] FROM [table]".
However, I don't know which method/property to use to create the SQL
command, and a disconnected recordset doesn't seem to have a table
name...

Bob
Wed Jul 12 13:27:02 CDT 2006

blackdog777 wrote:
> I'm working on a script that is grabbing the UNC path, the date last
> modified, and the creator owner for all folders and files in a
> specific directory and feeding this data into a disconnected
> recordset.
> Pretty much using the code as given in the Windows 2000
> scripting guide to populate the recordset and do a sort. The goal is
> to sort by creator/owner and then feed each unique user ID into an
> array to be used later in the script. Since a creator owner can
> appear in the Owner column for each file they own, I need to have a
> way to have that ID only appear in the array once. SQL has a
> "distinct" clause which would do the trick, but I have no idea how to
> do this in a disconnected recordset. Any samples I've seen in the
> MSDN are for VB and I'm not sure how to translate those into script.
> My guess is that SQL command would look something like "SELECT
> DISTINCT [column] FROM [table]". However, I don't know which
> method/property to use to create the SQL command, and a disconnected
> recordset doesn't seem to have a table name...

You can't run a sql statement against a recordset, so eliminate that
from your thought process now. Here is what you need to do:

1. Sort the recordset
rs.Sort = "ID"

2. create two variables: curval and newval, as well as the dynamic array
Dim curval, newval, arID()

3. Assign the user ID from the first record to curval and add it to your
array
curval = rs("ID")
ReDim arID(0)
arID(0) = curval

4. Loop through the recordset.
do until rs.eof
In the loop:
a. Assign the user ID of the current record to newval
newval = rs("ID")

b. Compare curval to newval and, if they are different
if newval <> curval then
i. Assign the value in newval to curval
curval=newval

ii. Add the value in curval to your array
redim preserve arID(ubound(arID) + 1 )
arID(ubound(arID)) = curval
end if
rs.movenext
next

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

blackdog777
Wed Jul 12 14:35:42 CDT 2006


Bob Barrows [MVP] wrote:
> blackdog777 wrote:
> > I'm working on a script that is grabbing the UNC path, the date last
> > modified, and the creator owner for all folders and files in a
> > specific directory and feeding this data into a disconnected
> > recordset.
> > Pretty much using the code as given in the Windows 2000
> > scripting guide to populate the recordset and do a sort. The goal is
> > to sort by creator/owner and then feed each unique user ID into an
> > array to be used later in the script. Since a creator owner can
> > appear in the Owner column for each file they own, I need to have a
> > way to have that ID only appear in the array once. SQL has a
> > "distinct" clause which would do the trick, but I have no idea how to
> > do this in a disconnected recordset. Any samples I've seen in the
> > MSDN are for VB and I'm not sure how to translate those into script.
> > My guess is that SQL command would look something like "SELECT
> > DISTINCT [column] FROM [table]". However, I don't know which
> > method/property to use to create the SQL command, and a disconnected
> > recordset doesn't seem to have a table name...
>
> You can't run a sql statement against a recordset, so eliminate that
> from your thought process now. Here is what you need to do:
>
> 1. Sort the recordset
> rs.Sort = "ID"
>
> 2. create two variables: curval and newval, as well as the dynamic array
> Dim curval, newval, arID()
>
> 3. Assign the user ID from the first record to curval and add it to your
> array
> curval = rs("ID")
> ReDim arID(0)
> arID(0) = curval
>
> 4. Loop through the recordset.
> do until rs.eof
> In the loop:
> a. Assign the user ID of the current record to newval
> newval = rs("ID")
>
> b. Compare curval to newval and, if they are different
> if newval <> curval then
> i. Assign the value in newval to curval
> curval=newval
>
> ii. Add the value in curval to your array
> redim preserve arID(ubound(arID) + 1 )
> arID(ubound(arID)) = curval
> end if
> rs.movenext
> next
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.


Thanks for the fast reply. I Was beginning to think that my lack of
knowledge of databases was leading me astray. Your suggestion will
work nicely.