Results 1 to 18 of 18

Thread: Vb.net Exception help required urgent!

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Vb.net Exception help required urgent!

    I have a code which process each file in a folder and send the data in the file to SQL Database and then deletes the file. But often i get this Exception "The process cannot access the file 'file.csv' because it is being used by another process." Please can anyone point me out in the right direction, will really appreciate that.

    The code is shown below :

    Dim dirinfo As DirectoryInfo
    Dim allFiles() As FileInfo

    dirinfo = New DirectoryInfo("E:\SQLUPDATE\CAC")
    allFiles = dirinfo.GetFiles("*.csv")
    If allFiles.Length <> 0 Then
    Try
    For Each fl As FileInfo In allFiles
    'MsgBox(fl.FullName.ToString())
    Dim con As SqlConnection = New SqlConnection(SQL_con2)
    Dim sr As StreamReader = New StreamReader(fl.FullName)
    Dim line As String = sr.ReadLine
    Dim value() As String = line.Split(Microsoft.VisualBasic.ChrW(44))
    Dim dt As DataTable = New DataTable
    Dim row As DataRow
    For Each dc As String In value
    dt.Columns.Add(New DataColumn(dc))
    Next

    While Not sr.EndOfStream
    value = sr.ReadLine.Split(Microsoft.VisualBasic.ChrW(44))
    If (value.Length = dt.Columns.Count) Then
    row = dt.NewRow
    row.ItemArray = value
    dt.Rows.Add(row)
    End If

    End While
    Dim bc As SqlBulkCopy = New SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock)
    bc.DestinationTableName = "[DB].[dbo].[CAC_LData]"
    bc.BatchSize = dt.Rows.Count
    con.Open()
    bc.WriteToServer(dt)
    bc.Close()
    con.Close()
    sr.Close()
    System.IO.File.Delete(fl.FullName)
    sr.Dispose()
    Next
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    End If

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Vb.net Exception help required urgent!

    What line of code generates the error...
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    its not all the time...its intermittent but it does it when there are several files in the folder. i dont know whether i have followed the right sequence for closing the streamwriter and deleteing the file.

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Vb.net Exception help required urgent!

    Yeah...that really answered my question

    I'm guessing it is the delete. I removed the DB code and put in a using to eliminate the dispose. My guess is something really does have the file. When does it occur? When you are running tests over and over again? When users are running it? The basic loop seems good to me.

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dirinfo As DirectoryInfo
            Dim allFiles() As FileInfo
    
            dirinfo = New DirectoryInfo("C:\CAC")
            allFiles = dirinfo.GetFiles("*.xlsx")
            If allFiles.Length <> 0 Then
                Try
                    For Each fl As FileInfo In allFiles
                        Using sr As StreamReader = New StreamReader(fl.FullName)
                            Dim line As String = sr.ReadLine
                            Dim value() As String = line.Split(Microsoft.VisualBasic.ChrW(44))
                            sr.Close()
                            System.IO.File.Delete(fl.FullName)
                        End Using
                    Next
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        End Sub
    Please remember next time...elections matter!

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    basically these files are being sent to this folder from FTP client. So i am thinking if FTP client has not finished transfering the file and it starts to process the file then this problem occurs. What do you suggest how can i kill this problem. Yes this loop goes on and on until all files in a folder are processed.

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Vb.net Exception help required urgent!

    It amazes me you are just mentioning that now

    One thing you can do is before you start the DB copy process check is to make sure you have exclusive control of the folder. I can find a link if you are not sure what to use. But, if the files are coming in a dynamic way where FTP can grab the folder anytime, I think you will need to copy it off first. A lot depends on how the FTP process is set up. For example we copy the folder off the FTP server and process that. The software we have moves the original folder off the server to a back up. If you want to elaborate about your process maybe someone can offer suggestions.
    Please remember next time...elections matter!

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    Thanks Mate for you reply...Sorry for not mentioning that before as it just came in my mind that FTP might be engaging the folder. Yes, the FTP can grab the folder anytime as you said its set in dynamic mode. Can you send me the link so i can look through what i need to do to copy the folder.Also i am thinking we will still have same issue while copying the folder as it will still be dynamically engaged by the FTP server?

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Vb.net Exception help required urgent!

    I think you are always going to have access issues as long as the file can come in at any time and FTP has exclusive locks. I may not be able to help you. We have software where we set up an "FTP job" that is constantly looking for files. At set times they are copied off. Then we process the copies. Probably because of the issues you are having. There are plenty of links to check if you can get exclusive control, I'll post one shortly, but if FTP grabs it during the copy the processes will fail unless the shares are compatible. Deleting then will always be an issue. Sit tight and see if someone with more experience than I chimes in.
    Please remember next time...elections matter!

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    Yes you are absolutely Right. By the way thanks a lot for your help. Lolz...yes i am sitting very tight until i kill this issue or hopefully someone will prove himself as a star

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Vb.net Exception help required urgent!

    Does the FTP lock the folder or just the file? I would assume just the file.
    And is ftp creating a new file each time? Meaning that a locked file is a file that's still in the process of being created? I would assume so.
    And finally, if you run this process while a file is being created, is it ok to defer the import and delete of that file to the DB until the next time the process is run? Again, I would assume so.

    I've made three fairly large assumptions there but if they're all correct the solution is actually quite easy I think:-
    1. Open the file in exclusive mode (use FileShare.None) - that will force the error to occur at the point of opening rather than deleting.
    2. Put a try catch block inside the loop and check for that specific exception. If you get that exception, simply move onto the next file in the loop.

    That way you will only progress with the import if the file is not being locked by the FTP process and at that point you also know you're going to be able to delete it. If you fail to get the lock it's because the FTP process is creating the file in which case just defer importing it until the next time this runs.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    yes, your three big assumptions are absolutely correct. I think you have hit the spot with that one. Its a really good solution but i am confused about how am i going to implement this in my code as i am using the streamreader to read the array of files.

  12. #12
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Vb.net Exception help required urgent!

    Quote Originally Posted by bmf073 View Post
    yes, your three big assumptions are absolutely correct. I think you have hit the spot with that one. Its a really good solution but i am confused about how am i going to implement this in my code as i am using the streamreader to read the array of files.
    FunkyDextor posted a viable solution but I personally shy away for handling errors like they are "technically" OK. In this case if you can't get exclusive control, assume FTP has it, you will pick it up later, and just move on. That seems like asking for trouble. There must be some control surrounding the FTP files coming in and giving you a chance to get exclusive control. I would try and go down that path first. Find out how to set up a maintenance window and process withing that if possible. On the flip side I work in bi shops that have tools and processes like that set up. I don't know your situation.
    Please remember next time...elections matter!

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Vb.net Exception help required urgent!

    That seems like asking for trouble
    Good point. There are certainly risks and you should be aware of them. The most obvious one is that you can't truly distinguish between a file that's locked by the FTP process and one that's locked by, for example, a user who opened it, didn't bother to close it and just left on a three week vacation. With that in mind my solution should be viewed as exactly what it is: a way of processing all free files in a directory and ignoring those which are not free. If that doesn't suffice then it's not the right solution.

    Of course, you could also add logging, warnings etc to help identify problem files but you'll only be dealing with the problems you've thought of, not the ones you haven't

    If you still think it's the right solution then here's how to open a file with an exclusive lock
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    I don't really want to go that route then because it can cause more problems for me.I am also thinking about instead of deleting that file may be if we can move it to different folder and delete it afterwards? But i don't know whether we will have the same issue of file being engaged?

  15. #15
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Vb.net Exception help required urgent!

    i don't know whether we will have the same issue of file being engaged?
    You almost certainly will.

    Personally I wouldn't bother moving the file if you're going to delete it afterwards anyway, it won't give you any benefit. There might be some mileage in moving it if you plan on keeping it for a while. Having a "quarantine" area like this is quite common because it allows you to recover (probably with some manual effort) if something goes wrong.



    Basically you've got two ways of going:-
    You can try to pre-empt and avoid the situation where a file is locked. Tyson's suggestion to use a maintenance window is an example of this. In that world, any time you encounter a locked file you know there's a problem, you can send out emails to adminstrators, set off warning sirens etc and get someone to deal with it.
    Or you can accept that there are valid reason for a file to be locked and accept them. My suggestion of deferring the import of locked files is an example of this. A slightly more sophisticated example might be to reattempt the import after an apropriate time delay and set off the sirens if it's still locked.

    Either way, though, you have to accept that files are going to be locked and you need to work with that.
    Last edited by FunkyDexter; Nov 5th, 2014 at 10:46 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Vb.net Exception help required urgent!

    Hi guys,

    How about if i put the thread to sleep for 1 sec lets say after getting all file names in the array and after 1 sec delay i start the while loop. By that time the FTP server should have finished writing to the file. Any suggestions about this?



    Dim dirinfo As DirectoryInfo
    Dim allFiles() As FileInfo

    dirinfo = New DirectoryInfo("E:\SQLUPDATE\CAC")
    allFiles = dirinfo.GetFiles("*.csv")

    Thread.Sleep(1000)

    If allFiles.Length <> 0 Then
    Try
    For Each fl As FileInfo In allFiles
    'MsgBox(fl.FullName.ToString())
    Dim con As SqlConnection = New SqlConnection(SQL_con2)
    Dim sr As StreamReader = New StreamReader(fl.FullName)
    Dim line As String = sr.ReadLine
    Dim value() As String = line.Split(Microsoft.VisualBasic.ChrW(44))
    Dim dt As DataTable = New DataTable
    Dim row As DataRow
    For Each dc As String In value
    dt.Columns.Add(New DataColumn(dc))
    Next

    While Not sr.EndOfStream
    value = sr.ReadLine.Split(Microsoft.VisualBasic.ChrW(44))
    If (value.Length = dt.Columns.Count) Then
    row = dt.NewRow
    row.ItemArray = value
    dt.Rows.Add(row)
    End If

    End While
    Dim bc As SqlBulkCopy = New SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock)
    bc.DestinationTableName = "[DB].[dbo].[CAC_LData]"
    bc.BatchSize = dt.Rows.Count
    con.Open()
    bc.WriteToServer(dt)
    bc.Close()
    con.Close()
    sr.Close()
    System.IO.File.Delete(fl.FullName)
    sr.Dispose()
    Next
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    End If

  17. #17
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Vb.net Exception help required urgent!

    The operative words are "server should have"

    Can you really count on that? I don't know what kind of environment you are in but I prefer copying off the data regardless if it is going to be deleted or not. As was mentioned you never know when may need to look at the data, reprocess it, prove it arrived, and all that kind of jazz.

    Here is briefly how one I set up worked:

    1. The files arrived via FTP and I never knew how many would be coming or when.
    2. I set up a job that ran once a day. The FTP software copied the data from the FTP server to a production share. There were no sharing issues.
    3. I would process the file and write a “job started” and “job ended” log to a DB table.
    4. If any error were encountered the program sent emails based on table entries to support personnel and logged what happened and where.
    5. About twenty minutes after the job was scheduled another job ran and read the log file. If it didn’t detect via the log the job ran successfully it also sent out emails. That was in case something happened like a severe error or the like.
    6. The same job in step 5 read the backup folders and deleted anything past a certain that was stored in a table.

    Seems like a lot of work but up front work coding and notifying for errors and the like will be much less painful than coding for expected errors, hoping the timing is right, and crossing your fingers. Unless you want tied to that process you should make it hands off and proactive. Our environments might be completely different though.
    Please remember next time...elections matter!

  18. #18
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Vb.net Exception help required urgent!

    somewhere around post #10 I stopped reading and decided to offer a possible solution that I've used in the past when dealing files received via FTP.
    We use a FileSystemWatcher to watch the inbound folder on our server. It has a FileCreated event that will let you know when a file is created. It does not let you know when it's done writing to the file though. So what we would do is take the full file name (including path) and put it into a List(Of String) ... then we start a timer (or re-start it if it's already running). In the Tick event of the timer (through experimentation in this case we found that the amount of time it takes to write a file of the largest expected size was about 37 seconds, so we used a timer set for 45 seconds) we would then loop through the List(Of String) and process each file. When done, we'd then move the file off to an archive folder based on the client.

    I've used that technique a couple times since, it seems to work reasonable well.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width