File System Object question

fogofogo · November 29th, 2005, 11:25 AM

Hello All,

I have a script that checks a database for records and saves each record set as an asp file. The script uses the "FileSystemObject" to do this and is run once a month, so I can create an archive. (I have been asked to do it this way for SEO reasons)

In my database there is a field for date, and this date is entered as dd/mm/yyyy. My question is if its possible to have the FileSystemObject create a folder for each month and save the asp files into it?

Heres what my code looks like:

Code:

<%

Dim Connection, Recordset
Dim sSQL, sConnString
Dim sFSO
Dim sThisPage
Dim TempPath
Dim f

'declare SQL statement that will query the database
sSQL = "SELECT * FROM DeHavillandNews"

'define the connection string, specify database 
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & Server.MapPath("casinonewsxml.mdb") 


'create an ADO connection and recordset
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
connection.Open sConnString

'Open the recordset object, execute the SQL statement
recordset.Open sSQL, connection

'create an instance of the FileSystemObject
SET sFSO = Server.CreateObject("Scripting.FileSystemObject")

'Loop through all your records in the database
DO WHILE NOT recordset.EOF

'Map a path to you unique file name based on the id in the database
sThisPage = Server.Mappath("htmlpages/"& recordset("ItemID") &".asp")

'Check if the file exists already and delete it
TempPath=sThisPage 
IF sFSO.FileExists(TempPath) = TRUE THEN
sFSO.DeleteFile(TempPath)
END IF

'Create a new file with the same name and fill it with content
Set f = sFSO.CreateTextFile(sThisPage, true)
f.write "<p class='newshead'>"& vbcrlf
f.write recordset("Heading") & vbcrlf
f.write "</p><p class='newstext'>"& vbcrlf
f.write recordset("Contents") & vbcrlf
f.write "</p>"& vbcrlf

'Begin the loop
recordset.MoveNext
LOOP

%>

Thanks guys - I really appreciate any input or help.

BrianWren · December 8th, 2005, 04:14 PM

Absolutely! (In the following, you might have to make some adjustments for trailing back slashes, etc.)
I tried to make all the changes I made boldface, but read carefully...
I substituted .WriteLine for .Write in locations where it was clear that the contents of what was being written would not have any linefeeds of carriage returns. So I left ("Heading") and ("Contents") as .Write.

Code:

    Dim cnctn, rs             ' Â» Both Connection and Recordset are words
    Dim sSQL, sConnString     '   naming specific items in the data access
    Dim sThisPage             '   object models;  itâs good idea to stay
    Dim TempPath              '   away from such names...
    Dim fso
    Dim Mnth
    Dim f
    Dim s   ' Temp string
    Dim x   ' Throw away variable to catch the created folder
            ' when using sFSO.CreateFolder() As Folder

    ' Declare SQL stmnt that will query the db
    sSQL = "SELECT * FROM DeHavillandNews"

    ' Define the connection string, specify db driver
    ' and the location of db
    sConnString  = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
                   "Data Source=" & Server.MapPath("casinonewsxml.mdb") 


    ' Create an ADO connection & recordset
    Set cnctn = Server.CreateObject("ADODB.Connection")
    Set rs    = Server.CreateObject("ADODB.Recordset")

    ' Open the connection to the db
    cnctn.Open sConnString

    ' Open the recordset, execute the SQL stmnt
    rs.Open sSQL, cnctn

    ' Create an instance of the FileSystemObject
    Set fso = Server.CreateObject("Scripting.FileSystemObject")

    ' Loop through all the records in the db
    Do While Not rs.EOF

        ' Get the month from the recordset
        Mnth = Mid(rs("DtFld"), 4, 2)   ' (Iâm assuming a field name...)
        ' If DtFld is a true date field, use VBâs date manipulation
        ' Mnth = CStr(Month(rs("DtFld")))

        ' Check for the existence of the folder.
        ' Create it if non-existent.
        s = Server.MapPath("htmlpages/a.txt"      ' Generate file path & name
        s = Left(s, Len(s) - 5)                   ' Strip the file name
        If Not fso.FolderExists(s) Then           ' Look for it
            Set x = fso.CreateFolder("s" & Mnth)  ' Create it
            Set x = Nothing                       ' Release the reference
        End If

        ' Map a path to a unique file name based on the id in the db
        sThisPage = Server.MapPath("htmlpages/" & Mnth & "/" & rs("ItemID") & ".asp")

        ' Check whether the file exists already;
        ' delete it if so.
     '  TempPath = sThisPage    ' Doesnât appear that TempPage differs from sThisPage . . .
        If fso.FileExists(sThisPage) Then
            fso.DeleteFile(sThisPage)
        End If

        ' Create a new file & fill it with content
        Set f = fso.CreateTextFile(sThisPage, True)

        f.WriteLine "<p class='newshead'>"
        f.Write     rs("Heading") & vbCrLf
        f.WriteLine "</p><p class='newstext'>"
        f.Write     rs("Contents") & vbCrLf
        f.WriteLine "</p>"
        f.Close
        Set f = Nothing

        rs.MoveNext

    Loop