[RESOLVED] FSO loosing / in path when Inserting in DB

[n1ghtowl]

I am trying to write a chunk of code to recursivly read a directory from a given location and add it to a DB. I have most of it working. I do however have 2 problems.
1. for some reason it is dropping all the / from the path. d:/pics/fun becomes dicsfun
2. part way down the list it has both the path and filename in the db.

ie. d:/pics/fun/001.bmp goes in as:
filename currentpath
001.bmp dicsfun001.bmp

[ganeshmoorthy]

could you post your code...

[n1ghtowl]

being overly tired i forgot to post the code. here it is.

Option Explicit
Dim fso As New FileSystemObject 'The file system object
Dim ParFolder As Folder 'parent folder variable
Dim n As Long 'for counting
 
Public filelist() As String 'array To hold the list of files With path
Public Function FindFile(Optional ByVal sFol As String, Optional ByVal NumberFiles As Long)
    Dim CurFile As File
    Dim CurFolder As Folder
    Dim NFiles As Long
    Set ParFolder = fso.GetFolder(sFol)
    
    NFiles = ParFolder.Files.Count
    If NFiles > 0 Then ' checks if dir has any files
    For Each CurFile In ParFolder.Files
' fix bad file/folder names
filename = Replace(CurFile.Name, "'", "''")
foldername = Replace(CurFile.Path, "'", "''")
'add to db
sqlstr = "insert into smfiles(filename, path, filesize)values('" & filename & "', '" & foldername & "', '" & CurFile.Size & "')"
conn.Execute (sqlstr)
 
     filecount = filecount + 1
        Next
     mainfrm.filecounttxt.Text = filecount
     mainfrm.Refresh
    
    End If
 
    For Each CurFolder In ParFolder.SubFolders 'IF SUBFOLDERS OF CURRENT FOLDER ARE THERE
        FindFile CurFolder 'call itself To Get the files of subfolders
    Next
End Function

tanks for any help.

[ganeshmoorthy]

it worked fine for me...i tried with msgbox like this

Public Function FindFile(Optional ByVal sFol As String, Optional ByVal NumberFiles As Long)
    Dim CurFile As File
    Dim CurFolder As Folder
    Dim NFiles As Long
    Set ParFolder = fso.GetFolder(sFol)
    Dim FileName As String
    Dim FolderName As String
    Dim SqlStr As String
    
    NFiles = ParFolder.Files.Count
    If NFiles > 0 Then ' checks if dir has any files
        For Each CurFile In ParFolder.Files
            ' fix bad file/folder names
            FileName = Replace(CurFile.Name, "'", "''")
            FolderName = Replace(CurFile.Path, "'", "''")
            'display it
            MsgBox FileName & ", " & FolderName & ", " & CurFile.Size
 
            filecount = filecount + 1
        Next
    End If
 
    For Each CurFolder In ParFolder.SubFolders 'IF SUBFOLDERS OF CURRENT FOLDER ARE THERE
        FindFile CurFolder 'call itself To Get the files of subfolders
    Next
End Function

[n1ghtowl]

thank you for the replies. yes the msg box worked for me as well. I should have mentioned earlyer that the DB was MySQL. Im not sure right now if that would have made a difference. I did figure out how to make it work for the DB. I just needed another replace.

foldername = Replace(foldername, "\", "\\")

if any one knows of other possible chars that may be a problem please let me know.
again thank you.

[n1ghtowl]

I also found the other problem.

FolderName = Replace(CurFile.Path, "'", "''")

should have been

FolderName = Replace(CurFile.ParentFolder, "'", "''")