We help IT Professionals succeed at work.

Sort Order

shirlng
shirlng asked
on
I use fso to get the name of the files and match file description in a table.

fso automatically sort the order by filename.
If I want to sort the order from the file description instead of the filename, how do I put the files in an array and re-sort them by the file description?

Thanks in advance!


     spcustno= session("cust_code")
     Set objCTSI = Server.CreateObject("NETSERVICE.DBCONNECT")
     retStatus = objCTSI.ConnectToDB("","","","REPORT")
     set RsCustno = objCTSI.FetchRecord("Select * from rptlookup where custno='"&spcustno&"'")
     
     if not RsCustno.EOF then
          repname = "Y"
     end if
     if  RsCustno.EOF then
          repname = "N"
     end if

         
     if repname = "Y" then    
           For Each Files in List
           if UCase(left(Files.Name, 7))= "EOW" & Trim(spcustno) then
                tempRptno = left(Files.Name, 10)
                StrSQL = "Select * from rptlookup where rptno='"&tempRptno&"'"
               Set RsRpt = objCTSI.FetchRecord(StrSQL)
               if not RsRpt.EOF then
               Rsrptname = RsRpt("rptname")

           %>
                         
               <tr>
                    <td width="70%" class="clsInput">
                          <a class="clsHREFACTIVE" target="_blank" href="../eowrpt/<%=wkpath&"/"&files.name%>"><b>
                          <%=RsRpt("rptname")%></a></b>
                     </td>
                     <td width="20%" align="right"  class="clsInput" ><% = Round(Files.size/1000) & "kb, " & "(" & Round(Files.size/3600) & " sec)" %>
                    </td>
                    <td width="10%" align="right" class="clsInput" ><% = formatdate(Files.DateLastModified) %></font>
                    </td>
               </tr>
          <%    
               else%>
               <tr>
                    <td width="70%" class="clsInput">
                          <a class="clsHREFACTIVE" target="_blank" href="../eowrpt/<%=wkpath&"/"&files.name%>"><b>
                          <%=left(Files.Name, 75)%></a></b>
                     </td>
                     <td width="20%" align="right"  class="clsInput" ><% = Round(Files.size/1000) & "kb, " & "(" & Round(Files.size/3600) & " sec)" %>
                    </td>
                    <td width="10%" align="right" class="clsInput" ><% = formatdate(Files.DateLastModified) %></font>
                    </td>
               </tr>    
          <%     end if
               end if
               Next
               
                   
     end if
Comment
Watch Question

Mark FranzProject Manager
BRONZE EXPERT

Commented:
Here is what I use;

<%
'Server.ScriptTimeOut = 10000
Dim Path

subPath = "test"
Path = Server.MapPath("/"&subPath)
%>
<font size = 2 face="arial, helvetica, sans-serif">
<%
Response.Write("Using path: " & Path & "<p>")
lenPath = Len(Path)

%>
</font>
<%
Dim fs, fso, f, Folder, fc, s, File, Name, arrayFiles(5000), sTmp(1), i, j, n  
Set fs = CreateObject("Scripting.FileSystemObject")
%><font size = 1 face="arial, helvetica, sans-serif"><%
Call ScanFolders(Path)
%></font><%

Function ScanFolders(PathSpec)

Response.Write(PathSpec & "<br>")
Response.Write("<BLOCKQUOTE>")
Set f = fs.GetFolder(PathSpec)
Set fc = f.Files

'-- Fill the array
i = 0
For Each f1 in fc
     i = i+1
          arrayFiles(i) = LCase(Cstr(f1.Name))
Next
n = i
For i = 1 to n
     For j = 1 to i
          If arrayFiles(i) < arrayFiles(j) Then
               sTmp(1) = arrayFiles(i)
               arrayFiles(i) = arrayFiles(j)
               arrayFiles(j) = sTmp(1)
          End if
     Next
Next
For i = 1 to n
allFiles = UCase(arrayFiles(i))
'Response.Write PathSpec &"<br>"
'Response.Write Path &"<br>"
rPath = Replace(PathSpec, Path, "")
'Response.Write rPath &"<br>"
     If Instr(allFiles,".JPG") OR Instr(UCase(allFiles),".JPEG") Then
          Response.Write("<TR><TD><A HREF="& subPath & rPath & "\" & arrayFiles(i)  & "><Font color=red>" & arrayFiles(i) & "</a><br></font></TD></TD>")
     ElseIf  Instr(allFiles,".GIF") Then
          Response.Write("<TR><TD><A HREF="& subPath & rPath &"/"& arrayFiles(i)  & "><Font color=blue>" & arrayFiles(i) & "</a><br></font></TD></TD>")
     'ElseIf Instr(allFiles,".CSS") Then
          'Response.Write("<TR><TD><A HREF="& rPath & "\"  & arrayFiles(i)  & "><Font color=green>" & arrayFiles(i) & "</a><br></font></TD></TD>")

     End If
Next

%>
</BLOCKQUOTE>
<%
Set f = Fs.GetFolder(PathSpec)
Set fc = f.SubFolders
For Each Folder in fc
     Response.Write("<BLOCKQUOTE>")
          Call ScanFolders(PathSpec & "\" & Folder.Name)
     Response.Write("</BLOCKQUOTE>")
Next
End Function
%>
<font size = 2 face="arial, helvetica, sans-serif">
<%
Response.Write "No more files."
%>
Whoa. Heavy code mgfranz :-)

I'd do this:

Dim objFSO, objFolder, objRS

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Server.Mappath(path))
Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Fields.Append("Name", adVarChar)
objRS.Fields.Append("Size", adInteger)
objRS.Fields.Append("DateLastmodified", adDate)

For Each file in objFolder.Files
   objRS.Addnew
   objRS("Name") = file.Name
   objRS("Size") = file.Size
   objRS("DateLastmodified") = file.DateLastmodified
   objRS.Update  
Next

'Now you can sort the files in what ever method you like:
objRS.Sort = "Name DESC"
objRS.Sort = "Name ASC"
objRS.Sort = "Size"
objRS.Sort = "DateLastmodified DESC"


Commented:
Have you tried just adding the ORDER BY to the SQL String:

IE: Select * from rptlookup where custno='"&spcustno&"' ORDER BY ColumnName

placing an optional DESC at the end will give you the reverse order
Mark FranzProject Manager
BRONZE EXPERT

Commented:
I didn't think about creating a new table that holds the file names alfa, that will work nicely.
It's just a temporary RecordSet (not really a db table), but has all the functionality that a db table would give you:

objRS.Filter("datePart('d', dateLastModified) = DatePart('d', GetDate())") or, files that have been edited today ;-)

You can run any T-SQL on the RecordSet inside the Filter.
Mark FranzProject Manager
BRONZE EXPERT

Commented:
Hmm... I'll have to read-up on the use of temp RS, do they need to remain connected until the end?  Or can they be used disconnected?

Know any decent readme on them?

Author

Commented:
thanks, sorry for the delay grading
:)
Mark FranzProject Manager
BRONZE EXPERT

Commented:
Am I missing somethig here alfa?  I get an odd error;

Error Type:
Microsoft VBScript compilation (0x800A0414)
Cannot use parentheses when calling a Sub
/sort.asp, line 21, column 38
objRS.Fields.Append("Name", adVarChar)
-------------------------------------^

Here is my file;

<!--#include file="adovbs.inc"-->
<%
Dim objFSO, objFolder, objRS, Path, subPath
subPath = "test"
Path = Server.MapPath("/"&subPath)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)
Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Fields.Append("Name", adVarChar)
objRS.Fields.Append("Size", adInteger)
objRS.Fields.Append("DateLastmodified", adDate)

For Each file in objFolder.Files
  objRS.Addnew
  objRS("Name") = file.Name
  objRS("Size") = file.Size
  objRS("DateLastmodified") = file.DateLastmodified
  objRS.Update  
Next

objRS.Sort = "Name DESC, Size, DateLastmodified"
Response.Write &objRS("Name")&", "&objRS("Size")&", "&objRS("DateLastmodified")&"."
%>
Mark FranzProject Manager
BRONZE EXPERT

Commented:
Well, ignore my last...  After an hour I was able to get this to work correctly;

<%
Dim objFSO, objFolder, objRS, Path, subPath

subPath = "test"
Path = Server.MapPath("/"&subPath)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)
Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Fields.Append "Name", adVarChar, 20
objRS.Fields.Append "Size", adInteger, 6
objRS.Fields.Append "DateLastmodified", adDate, 9
objRS.Open

For Each file in objFolder.Files
  objRS.Addnew
  objRS("Name") = file.Name
  objRS("Size") = file.Size
  objRS("DateLastmodified") = file.DateLastModified
  objRS.Update  
Next

Function Pad (str, numChars)
    str = str & Space(numChars)
    Pad = Left(str, numChars)
End Function

objRS.Sort = "Name ASC, Size, DateLastmodified"
' Displays the recordset
s = ""
objRS.MoveFirst
while Not objRS.EOF
    for each fld in objRS.Fields
        s = s &"<td>"& Pad(objRS.Fields(fld.Name), 18)&"</td>"
    next
    s = s & "</tr>"
    objRS.MoveNext
wend

Response.Write s

%>

Nice trick Alfa.
Mark FranzProject Manager
BRONZE EXPERT

Commented:
If you are not worried about stripping off length, then just set s using this;

s = s &"<td>"& objRS.Fields(fld.Name)&"</td>"

Explore More ContentExplore courses, solutions, and other research materials related to this topic.