By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

IO errors with "folder watcher" program

P: n/a
I'm having a lot of trouble with "file in use" errors in my "folder watcher"
project. Starting and stopping the watcher and reading my XML file work
fine. Once the watcher is started, I'm reading the text files from the
watched folder line by line into variables and then posting them to a SQL
table.

All of the code for the form is shown below. And it works fine except for 2
issues.

First issue: In the Finally of the Try for teh SQL code I have this:

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()

I get this type of intellisense warning: "Variable sqlComm is used before it
has been assigned a value. A null reference exception could result at
runtime." What do I need to rearange to avoid this problem? Sometimes it
causes crash and sometimes not. If the file cannot be opened for some
reason, don't I want to free the sqlComm variable if the code fails?

Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder. They
all get processed exactly as I expect. Everything below appears to run just
fine. Then, without restartign the watcher program, I copy 4 different files
into the watched folder. This time only 2 of them are processed. The other 2
generate the error below:

"The process cannot access the file 'D:\FilesToImport\217068F01.txt' because
it is being used by another process."

Well it's not in use by anything I'm doing manually. I get this error pretty
consistently when I do what I described above. Sometimes 2 files don't work
and sometimes only 1. Sometimes they both process fine but that's very rare.
Usually at least one of them fails. I have no idea why. It doesn't depend on
the particular file either. It's always the first batch (whether it's 1 file
or 30) processes fine after I start up the watcher exe. Then when I drop any
number of additional files into the watched folder 1 or more of them
generate the error above.

I sure hope someone can point out the problems so I can get this running.
I'm pretty frustrated with it right now.

Thanks,

Keith
Here's all the code for this form:
Imports System
Imports System.Xml
Imports System.IO
Imports System.Diagnostics
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Public Class NoticeImport

Private watchFolder As FileSystemWatcher
Private dataSource As String, _
dataBase As String, _
userName As String, _
password As String, _
monitoredCustomer As String, _
textFilesLocation As String, _
fntName As String, _
fntSize As Double, _
clipWidth As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub btnStartWatching_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnStartWatching.Click

watchFolder = New System.IO.FileSystemWatcher()

'Get the db login info and the location of the input text files from
the xml file

Dim sAppPathAndName As String = Application.ExecutablePath
Dim sAppPathOnly As String = Mid(sAppPathAndName, 1,
InStr(sAppPathAndName, "FNTNoticeImport.exe", CompareMethod.Text) - 1)

textFilesLocation = ""

Try

Dim xrdr As New XmlTextReader(sAppPathOnly &
"FNTNoticeImportSettings.xml")
xrdr.WhitespaceHandling = WhitespaceHandling.None

While xrdr.Read()

If String.Compare(xrdr.Name, "dataSource", True) = 0 Then
dataSource = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "dataBase", True) = 0 Then
dataBase = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "userName", True) = 0 Then
userName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "password", True) = 0 Then
password = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "monitoredCustomer", True) = 0
Then
monitoredCustomer = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "textFilesLocation", True) = 0
Then
textFilesLocation = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntName", True) = 0 Then
fntName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntSize", True) = 0 Then
fntSize = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "clipWidth", True) = 0 Then
clipWidth = xrdr.ReadElementString() 'clip width is in
hundredths of an inch
End If

End While

xrdr.Close()

'tell the watcher where to watch
watchFolder.Path = textFilesLocation
Me.Text = "Monitoring for Customer: " & monitoredCustomer
txtWatchFolder.Text = "Watching Folder: " & textFilesLocation

'add a list of Filter we want to specify
'make sure you use OR for each Filter as we need to all of those

watchFolder.NotifyFilter = IO.NotifyFilters.DirectoryName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.FileName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.Attributes
' add the handler to each event
AddHandler watchFolder.Changed, AddressOf logChange
AddHandler watchFolder.Created, AddressOf logChange
AddHandler watchFolder.Deleted, AddressOf logChange

' add the rename handler as the signature is different
AddHandler watchFolder.Renamed, AddressOf logrename

'Set this property to true to start watching
watchFolder.EnableRaisingEvents = True
watchFolder.IncludeSubdirectories = False
watchFolder.Filter = "*.txt"

btnStartWatching.Enabled = False
btnStopWatching.Enabled = True

Catch ex As XmlException
MsgBox("XML Error " & ex.Message & " occurred. Cannot start
watching process.")
Exit Sub

Catch ex As Exception
MsgBox("Error " & ex.Message & " occurred. Cannot start watching
process.")
Exit Sub

End Try

End Sub

Private Sub logChange(ByVal source As Object, ByVal e As
System.IO.FileSystemEventArgs)

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Changed Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
modified" & vbCrLf
'End If

If e.ChangeType = IO.WatcherChangeTypes.Created Then
'txtFolderActivity.Text &= e.Name & " received at " & Now() &
vbCrLf
If InStr(e.Name, ".txt", CompareMethod.Text) Then
Call ImportTextFiles(e.Name)
'Debug.Print(e.Name)
End If
End If

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Deleted Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
deleted" & vbCrLf
'End If

End Sub

Public Sub logrename(ByVal source As Object, ByVal e As
System.IO.RenamedEventArgs)
'Don't use this for now
'txtFolderActivity.Text &= "File" & e.OldName & " has been renamed
to " & e.Name & vbCrLf
End Sub

Private Sub btnStopWatching_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnStopWatching.Click
' Stop watching the folder
watchFolder.EnableRaisingEvents = False
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub ImportTextFiles(ByVal sFileToImport As String)

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
Dim srLine As String
Dim iFileLineCount As Integer, _
iNoticeTextLineCount As Integer
Dim bInNoticeText As Boolean
Dim AdTypeCode As String, _
ImportType As String, _
AttorneyFileNum As String, _
AttyOfficeName As String, _
MortgagorFirstName As String, _
MortgagorLastName As String, _
PropAddress1 As String, _
PropAddress2 As String, _
PropCity As String, _
PropState As String, _
PropZip As String, _
FirstPubDate As String, _
NumWeeksToRun As String, _
LastPubDate As String, _
County As String, _
DateOfSale As String, _
FullNoticeText As String, _
NoticeTextHeight As Double, _
WholeImportFile As String

ImportType = ""
AttorneyFileNum = ""
AttyOfficeName = ""
MortgagorFirstName = ""
MortgagorLastName = ""
PropAddress1 = ""
PropAddress2 = ""
PropCity = ""
PropState = ""
PropZip = ""
FirstPubDate = ""
NumWeeksToRun = ""
LastPubDate = ""
County = ""
DateOfSale = ""
FullNoticeText = ""
WholeImportFile = ""

bInNoticeText = False
iFileLineCount = 0
iNoticeTextLineCount = 0

Dim sr As StreamReader

'Open the text file
Try
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

'Read the text file line by line
Try
Do
srLine = sr.ReadLine()

iFileLineCount += 1
If iFileLineCount = 1 Then
WholeImportFile = srLine
Else
WholeImportFile = WholeImportFile & vbCrLf & srLine
End If

'TODO 2008-08-13 - AdTypeCode will need to come from import
file at some point
AdTypeCode = "M"
'If InStr(srLine, "Ad Tpe:", CompareMethod.Text) Then
' AdTypeCode = Trim(Mid(srLine, 8))
'End If

If InStr(srLine, "Publication Notice:", CompareMethod.Text)
Then
ImportType = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "File Number:", CompareMethod.Text) Then
AttorneyFileNum = Trim(Mid(srLine, 13))
End If

'TODO 2008-08-13 - AttorneyName will need to come from
import file at some point when we use this for other attorneys
If InStr(srLine, "Team:", CompareMethod.Text) Then
AttyOfficeName = Trim(Mid(srLine, 6))
AttyOfficeName = "Trott & Trott P.C. (team " &
AttyOfficeName & ")"
End If

If InStr(srLine, "Mortgagor First:", CompareMethod.Text)
Then
MortgagorFirstName = Trim(Mid(srLine, 17))
End If

If InStr(srLine, "Mortgagor Last:", CompareMethod.Text) Then
MortgagorLastName = Trim(Mid(srLine, 16))
End If

If InStr(srLine, "Property Address 1:", CompareMethod.Text)
Then
PropAddress1 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "Property Address 2:", CompareMethod.Text)
Then
PropAddress2 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "City:", CompareMethod.Text) Then
PropCity = Trim(Mid(srLine, 6))
End If

If InStr(srLine, "State:", CompareMethod.Text) Then
PropState = Trim(Mid(srLine, 7))
End If

If InStr(srLine, "ZIP:", CompareMethod.Text) Then
PropZip = Trim(Mid(srLine, 5))
End If

If InStr(srLine, "First Publication Date:",
CompareMethod.Text) Then
FirstPubDate = Trim(Mid(srLine, 24))
End If

If InStr(srLine, "Number Of Insertions:",
CompareMethod.Text) Then
NumWeeksToRun = Trim(Mid(srLine, 22))
End If

If InStr(srLine, "Last Pub Date:", CompareMethod.Text) Then
LastPubDate = Trim(Mid(srLine, 15))
End If

If InStr(srLine, "County:", CompareMethod.Text) Then
County = Trim(Mid(srLine, 8))
End If

If InStr(srLine, "Sale Date:", CompareMethod.Text) Then
DateOfSale = Trim(Mid(srLine, 11))
End If

'TODO 2008-08-07 - only do the following if it's new or
correction (not if cancel)
If InStr(srLine, "*** Begin Notice ***", CompareMethod.Text)
0 Then
'skip 1 line and read the rest into the FullNoticeText
variable
srLine = sr.ReadLine()
iNoticeTextLineCount = 1 'we're on line one now and will
get it into FullNoticeText below
WholeImportFile = WholeImportFile & vbCrLf & srLine
'have to do this here because we're skipping a line. if we don't then the
similar line above won't catch the skipped line
bInNoticeText = True
End If

If bInNoticeText Then
If InStr(srLine, "*** End Notice ***",
CompareMethod.Text) = 0 Then
If iNoticeTextLineCount = 1 Then
FullNoticeText = srLine
Else
FullNoticeText = FullNoticeText & vbCrLf &
srLine
End If
iNoticeTextLineCount += 1
End If
End If

Loop Until InStr(srLine, "*** End Notice ***",
CompareMethod.Text) <0
'If this is a correction, then there is info AFTER the "*** End
Notice ***" line that we need to read into the WholeImportFile variable
If String.Compare(ImportType, "Correct", True) = 0 Then
WholeImportFile = WholeImportFile & vbCrLf & sr.ReadToEnd()
End If

Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to read file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally
'do not need sr after all the above reading is done
sr.Close()
sr.Dispose()
End Try
'get the height in decimal inches (rounded to 2 places)
Try
If FullNoticeText <"" Then
NoticeTextHeight = (dfc.NoticeTextHeight(FullNoticeText,
fntName, fntSize, clipWidth))
Else
NoticeTextHeight = 0
End If
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while calling dll to get text height on file " & sFileToImport & ". File NOT
imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand

'get the data into the database
Try

sqlConn = New SqlConnection("Data Source=" & dataSource &
";Initial Catalog=" & dataBase & ";User Id=" & userName & ";Password=" &
password & ";")
sqlConn.Open()

sqlComm = New SqlCommand("sp_ImportNotices", sqlConn)
sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Value = 0
sqlComm.Parameters("RETURN_VALUE").Direction =
ParameterDirection.ReturnValue
sqlComm.Parameters.Add("@AdTypeCode", SqlDbType.VarChar,
50).Value = IIf(AdTypeCode = "", DBNull.Value, AdTypeCode)
sqlComm.Parameters.Add("@ImportType", SqlDbType.VarChar,
50).Value = IIf(ImportType = "", DBNull.Value, ImportType)
sqlComm.Parameters.Add("@AttorneyFileNum", SqlDbType.VarChar,
50).Value = IIf(AttorneyFileNum = "", DBNull.Value, AttorneyFileNum)
sqlComm.Parameters.Add("@AttyOfficeName", SqlDbType.VarChar,
200).Value = IIf(AttyOfficeName = "", DBNull.Value, AttyOfficeName)
sqlComm.Parameters.Add("@MortgagorFirstName", SqlDbType.VarChar,
50).Value = IIf(MortgagorFirstName = "", DBNull.Value, MortgagorFirstName)
sqlComm.Parameters.Add("@MortgagorLastName", SqlDbType.VarChar,
50).Value = IIf(MortgagorLastName = "", DBNull.Value, MortgagorLastName)
sqlComm.Parameters.Add("@PropAddress1", SqlDbType.VarChar,
100).Value = IIf(PropAddress1 = "", DBNull.Value, PropAddress1)
sqlComm.Parameters.Add("@PropAddress2", SqlDbType.VarChar,
100).Value = IIf(PropAddress2 = "", DBNull.Value, PropAddress2)
sqlComm.Parameters.Add("@PropCity", SqlDbType.VarChar,
100).Value = IIf(PropCity = "", DBNull.Value, PropCity)
sqlComm.Parameters.Add("@PropState", SqlDbType.VarChar,
50).Value = IIf(PropState = "", DBNull.Value, PropState)
sqlComm.Parameters.Add("@PropZip", SqlDbType.VarChar, 20).Value
= IIf(PropZip = "", DBNull.Value, PropZip)
sqlComm.Parameters.Add("@FirstPubDate", SqlDbType.VarChar,
50).Value = IIf(FirstPubDate = "", DBNull.Value, FirstPubDate)
sqlComm.Parameters.Add("@NumWeeksToRun", SqlDbType.VarChar,
10).Value = IIf(NumWeeksToRun = "", DBNull.Value, NumWeeksToRun)
sqlComm.Parameters.Add("@LastPubDate", SqlDbType.VarChar,
50).Value = IIf(LastPubDate = "", DBNull.Value, LastPubDate)
sqlComm.Parameters.Add("@County", SqlDbType.VarChar, 50).Value =
IIf(County = "", DBNull.Value, County)
sqlComm.Parameters.Add("@DateOfSale", SqlDbType.VarChar,
50).Value = IIf(DateOfSale = "", DBNull.Value, DateOfSale)
sqlComm.Parameters.Add("@FullNoticeText", SqlDbType.Text).Value
= IIf(FullNoticeText = "", DBNull.Value, FullNoticeText)
sqlComm.Parameters.Add("@NoticeTextHeight", SqlDbType.Decimal,
5).Value = CDbl(NoticeTextHeight)
sqlComm.Parameters.Add("@WholeImportFile", SqlDbType.Text).Value
= IIf(WholeImportFile = "", DBNull.Value, WholeImportFile)
sqlComm.Parameters.Add("@TextFileName", SqlDbType.VarChar,
500).Value = sFileToImport
sqlComm.Parameters.Add("@ReasonsNotPosted", SqlDbType.VarChar,
500).Value = DBNull.Value
sqlComm.Parameters("@ReasonsNotPosted").Direction =
ParameterDirection.Output

sqlComm.ExecuteNonQuery()

If sqlComm.Parameters("RETURN_VALUE").Value <0 Then
txtFolderActivity.Text &= "SQL Error " &
sqlComm.Parameters("RETURN_VALUE").Value.ToString & " on file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") &
")." & vbCrLf
Exit Sub
End If

Catch ex As SqlException
txtFolderActivity.Text &= "SQL Error " & ex.Message & " occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport &
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport &
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()
End Try

'if the "ImportedNoticeFiles" folder does not yet exist, create it
Try
If Not Directory.Exists(textFilesLocation &
"ImportedNoticeFiles") Then
Directory.CreateDirectory(textFilesLocation &
"ImportedNoticeFiles")
'don't go on until the folder has been created
Do Until Directory.Exists(textFilesLocation &
"ImportedNoticeFiles")
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
attempting to create ImportedNoticeFiles folder while importing file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") &
")." & vbCrLf
End Try

'store the finished file in the ImportedNoticeFiles folder for
someone to manually review (delete it first if for some reason it already
exists)
Try
If File.Exists(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport) Then
File.Delete(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport)
'Need to wait for the file to be deleted before moving on.
Do Until Not File.Exists(textFilesLocation &
"ImportedNoticeFiles\" & sFileToImport)
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "File " & sFileToImport & " was
imported but IO Error " & ex.Message & " occurred while attempting to delete
the previously imported file with the same name (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

Try
File.Move(textFilesLocation & sFileToImport, textFilesLocation &
"ImportedNoticeFiles\" & sFileToImport)
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & " occurred
while attempting to move file " & sFileToImport & " to the
ImportedNoticeFiles folder. File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

txtFolderActivity.Text &= "File " & sFileToImport & " has been
imported successfully and has been moved to the ImportedNoticeFiles
subfolder (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf

End Sub

End Class
Aug 26 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Keith G Hicks wrote:
I'm having a lot of trouble with "file in use" errors in my "folder watcher"
project. Starting and stopping the watcher and reading my XML file work
fine.
Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder. They
all get processed exactly as I expect. Everything below appears to run just
fine. Then, without restartign the watcher program, I copy 4 different files
into the watched folder. This time only 2 of them are processed. The other 2
generate the error below:
Is it possible that the FileSystemWatcher is "jumping on" the /same/
file twice in rapid succession?

I've noticed that the FileSystemWatcher has a nasty habit of raising
multiple events when you might only expect one and doing so in a very
short space of time. I suspect the FileSystemWatcher has its own
Thread, so straight away you're into the nastiness of cross-threading
and race conditions.

Try putting a SyncLock inside the method that reads the file content and
see if that helps any, as in:

Private Sub ImportTextFiles(ByVal sFileToImport As String)
Dim lockObject as New Object ' always a Private variable

SyncLock lockObject

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
. . .

End SyncLock

HTH,
Phill W.
Aug 27 '08 #2

P: n/a
Thanks for the suggestion Phil but SyncLock didn't do it. And you could be
right. I read that FileSystemWatcher can raising multiple events somewhere.
It could be using a single file more than once very quickly. What confuses
me is that it *never* does that the first time around. It always works just
fine. It's onlly after that when there's a problem.

"Phill W." <p-.-a-.-w-a-r-d-@-o-p-e-n-.-a-c-.-u-kwrote in message
news:g9**********@south.jnrs.ja.net...
Keith G Hicks wrote:
I'm having a lot of trouble with "file in use" errors in my "folder
watcher"
project. Starting and stopping the watcher and reading my XML file work
fine.
Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder.
They
all get processed exactly as I expect. Everything below appears to run
just
fine. Then, without restartign the watcher program, I copy 4 different
files
into the watched folder. This time only 2 of them are processed. The
other 2
generate the error below:

Is it possible that the FileSystemWatcher is "jumping on" the /same/
file twice in rapid succession?

I've noticed that the FileSystemWatcher has a nasty habit of raising
multiple events when you might only expect one and doing so in a very
short space of time. I suspect the FileSystemWatcher has its own
Thread, so straight away you're into the nastiness of cross-threading
and race conditions.

Try putting a SyncLock inside the method that reads the file content and
see if that helps any, as in:

Private Sub ImportTextFiles(ByVal sFileToImport As String)
Dim lockObject as New Object ' always a Private variable

SyncLock lockObject

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
. . .

End SyncLock

HTH,
Phill W.

Aug 27 '08 #3

P: n/a
Any Antivirus prog is running?
Wolfgang

"Keith G Hicks" <kr*@comcast.netschrieb im Newsbeitrag
news:e1**************@TK2MSFTNGP06.phx.gbl...
I'm having a lot of trouble with "file in use" errors in my "folder
watcher"
project. Starting and stopping the watcher and reading my XML file work
fine. Once the watcher is started, I'm reading the text files from the
watched folder line by line into variables and then posting them to a SQL
table.

All of the code for the form is shown below. And it works fine except for
2
issues.

First issue: In the Finally of the Try for teh SQL code I have this:

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()

I get this type of intellisense warning: "Variable sqlComm is used before
it
has been assigned a value. A null reference exception could result at
runtime." What do I need to rearange to avoid this problem? Sometimes it
causes crash and sometimes not. If the file cannot be opened for some
reason, don't I want to free the sqlComm variable if the code fails?

Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder. They
all get processed exactly as I expect. Everything below appears to run
just
fine. Then, without restartign the watcher program, I copy 4 different
files
into the watched folder. This time only 2 of them are processed. The other
2
generate the error below:

"The process cannot access the file 'D:\FilesToImport\217068F01.txt'
because
it is being used by another process."

Well it's not in use by anything I'm doing manually. I get this error
pretty
consistently when I do what I described above. Sometimes 2 files don't
work
and sometimes only 1. Sometimes they both process fine but that's very
rare.
Usually at least one of them fails. I have no idea why. It doesn't depend
on
the particular file either. It's always the first batch (whether it's 1
file
or 30) processes fine after I start up the watcher exe. Then when I drop
any
number of additional files into the watched folder 1 or more of them
generate the error above.

I sure hope someone can point out the problems so I can get this running.
I'm pretty frustrated with it right now.

Thanks,

Keith
Here's all the code for this form:
Imports System
Imports System.Xml
Imports System.IO
Imports System.Diagnostics
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Public Class NoticeImport

Private watchFolder As FileSystemWatcher
Private dataSource As String, _
dataBase As String, _
userName As String, _
password As String, _
monitoredCustomer As String, _
textFilesLocation As String, _
fntName As String, _
fntSize As Double, _
clipWidth As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub btnStartWatching_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnStartWatching.Click

watchFolder = New System.IO.FileSystemWatcher()

'Get the db login info and the location of the input text files
from
the xml file

Dim sAppPathAndName As String = Application.ExecutablePath
Dim sAppPathOnly As String = Mid(sAppPathAndName, 1,
InStr(sAppPathAndName, "FNTNoticeImport.exe", CompareMethod.Text) - 1)

textFilesLocation = ""

Try

Dim xrdr As New XmlTextReader(sAppPathOnly &
"FNTNoticeImportSettings.xml")
xrdr.WhitespaceHandling = WhitespaceHandling.None

While xrdr.Read()

If String.Compare(xrdr.Name, "dataSource", True) = 0 Then
dataSource = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "dataBase", True) = 0 Then
dataBase = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "userName", True) = 0 Then
userName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "password", True) = 0 Then
password = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "monitoredCustomer", True) = 0
Then
monitoredCustomer = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "textFilesLocation", True) = 0
Then
textFilesLocation = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntName", True) = 0 Then
fntName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntSize", True) = 0 Then
fntSize = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "clipWidth", True) = 0 Then
clipWidth = xrdr.ReadElementString() 'clip width is in
hundredths of an inch
End If

End While

xrdr.Close()

'tell the watcher where to watch
watchFolder.Path = textFilesLocation
Me.Text = "Monitoring for Customer: " & monitoredCustomer
txtWatchFolder.Text = "Watching Folder: " & textFilesLocation

'add a list of Filter we want to specify
'make sure you use OR for each Filter as we need to all of
those

watchFolder.NotifyFilter = IO.NotifyFilters.DirectoryName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.FileName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.Attributes
' add the handler to each event
AddHandler watchFolder.Changed, AddressOf logChange
AddHandler watchFolder.Created, AddressOf logChange
AddHandler watchFolder.Deleted, AddressOf logChange

' add the rename handler as the signature is different
AddHandler watchFolder.Renamed, AddressOf logrename

'Set this property to true to start watching
watchFolder.EnableRaisingEvents = True
watchFolder.IncludeSubdirectories = False
watchFolder.Filter = "*.txt"

btnStartWatching.Enabled = False
btnStopWatching.Enabled = True

Catch ex As XmlException
MsgBox("XML Error " & ex.Message & " occurred. Cannot start
watching process.")
Exit Sub

Catch ex As Exception
MsgBox("Error " & ex.Message & " occurred. Cannot start
watching
process.")
Exit Sub

End Try

End Sub

Private Sub logChange(ByVal source As Object, ByVal e As
System.IO.FileSystemEventArgs)

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Changed Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
modified" & vbCrLf
'End If

If e.ChangeType = IO.WatcherChangeTypes.Created Then
'txtFolderActivity.Text &= e.Name & " received at " & Now() &
vbCrLf
If InStr(e.Name, ".txt", CompareMethod.Text) Then
Call ImportTextFiles(e.Name)
'Debug.Print(e.Name)
End If
End If

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Deleted Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
deleted" & vbCrLf
'End If

End Sub

Public Sub logrename(ByVal source As Object, ByVal e As
System.IO.RenamedEventArgs)
'Don't use this for now
'txtFolderActivity.Text &= "File" & e.OldName & " has been renamed
to " & e.Name & vbCrLf
End Sub

Private Sub btnStopWatching_Click(ByVal sender As System.Object, ByVal
e
As System.EventArgs) Handles btnStopWatching.Click
' Stop watching the folder
watchFolder.EnableRaisingEvents = False
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub ImportTextFiles(ByVal sFileToImport As String)

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
Dim srLine As String
Dim iFileLineCount As Integer, _
iNoticeTextLineCount As Integer
Dim bInNoticeText As Boolean
Dim AdTypeCode As String, _
ImportType As String, _
AttorneyFileNum As String, _
AttyOfficeName As String, _
MortgagorFirstName As String, _
MortgagorLastName As String, _
PropAddress1 As String, _
PropAddress2 As String, _
PropCity As String, _
PropState As String, _
PropZip As String, _
FirstPubDate As String, _
NumWeeksToRun As String, _
LastPubDate As String, _
County As String, _
DateOfSale As String, _
FullNoticeText As String, _
NoticeTextHeight As Double, _
WholeImportFile As String

ImportType = ""
AttorneyFileNum = ""
AttyOfficeName = ""
MortgagorFirstName = ""
MortgagorLastName = ""
PropAddress1 = ""
PropAddress2 = ""
PropCity = ""
PropState = ""
PropZip = ""
FirstPubDate = ""
NumWeeksToRun = ""
LastPubDate = ""
County = ""
DateOfSale = ""
FullNoticeText = ""
WholeImportFile = ""

bInNoticeText = False
iFileLineCount = 0
iNoticeTextLineCount = 0

Dim sr As StreamReader

'Open the text file
Try
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

'Read the text file line by line
Try
Do
srLine = sr.ReadLine()

iFileLineCount += 1
If iFileLineCount = 1 Then
WholeImportFile = srLine
Else
WholeImportFile = WholeImportFile & vbCrLf & srLine
End If

'TODO 2008-08-13 - AdTypeCode will need to come from import
file at some point
AdTypeCode = "M"
'If InStr(srLine, "Ad Tpe:", CompareMethod.Text) Then
' AdTypeCode = Trim(Mid(srLine, 8))
'End If

If InStr(srLine, "Publication Notice:", CompareMethod.Text)
Then
ImportType = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "File Number:", CompareMethod.Text) Then
AttorneyFileNum = Trim(Mid(srLine, 13))
End If

'TODO 2008-08-13 - AttorneyName will need to come from
import file at some point when we use this for other attorneys
If InStr(srLine, "Team:", CompareMethod.Text) Then
AttyOfficeName = Trim(Mid(srLine, 6))
AttyOfficeName = "Trott & Trott P.C. (team " &
AttyOfficeName & ")"
End If

If InStr(srLine, "Mortgagor First:", CompareMethod.Text)
Then
MortgagorFirstName = Trim(Mid(srLine, 17))
End If

If InStr(srLine, "Mortgagor Last:", CompareMethod.Text)
Then
MortgagorLastName = Trim(Mid(srLine, 16))
End If

If InStr(srLine, "Property Address 1:", CompareMethod.Text)
Then
PropAddress1 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "Property Address 2:", CompareMethod.Text)
Then
PropAddress2 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "City:", CompareMethod.Text) Then
PropCity = Trim(Mid(srLine, 6))
End If

If InStr(srLine, "State:", CompareMethod.Text) Then
PropState = Trim(Mid(srLine, 7))
End If

If InStr(srLine, "ZIP:", CompareMethod.Text) Then
PropZip = Trim(Mid(srLine, 5))
End If

If InStr(srLine, "First Publication Date:",
CompareMethod.Text) Then
FirstPubDate = Trim(Mid(srLine, 24))
End If

If InStr(srLine, "Number Of Insertions:",
CompareMethod.Text) Then
NumWeeksToRun = Trim(Mid(srLine, 22))
End If

If InStr(srLine, "Last Pub Date:", CompareMethod.Text) Then
LastPubDate = Trim(Mid(srLine, 15))
End If

If InStr(srLine, "County:", CompareMethod.Text) Then
County = Trim(Mid(srLine, 8))
End If

If InStr(srLine, "Sale Date:", CompareMethod.Text) Then
DateOfSale = Trim(Mid(srLine, 11))
End If

'TODO 2008-08-07 - only do the following if it's new or
correction (not if cancel)
If InStr(srLine, "*** Begin Notice ***",
CompareMethod.Text)
>0 Then
'skip 1 line and read the rest into the FullNoticeText
variable
srLine = sr.ReadLine()
iNoticeTextLineCount = 1 'we're on line one now and
will
get it into FullNoticeText below
WholeImportFile = WholeImportFile & vbCrLf & srLine
'have to do this here because we're skipping a line. if we don't then the
similar line above won't catch the skipped line
bInNoticeText = True
End If

If bInNoticeText Then
If InStr(srLine, "*** End Notice ***",
CompareMethod.Text) = 0 Then
If iNoticeTextLineCount = 1 Then
FullNoticeText = srLine
Else
FullNoticeText = FullNoticeText & vbCrLf &
srLine
End If
iNoticeTextLineCount += 1
End If
End If

Loop Until InStr(srLine, "*** End Notice ***",
CompareMethod.Text) <0
'If this is a correction, then there is info AFTER the "*** End
Notice ***" line that we need to read into the WholeImportFile variable
If String.Compare(ImportType, "Correct", True) = 0 Then
WholeImportFile = WholeImportFile & vbCrLf & sr.ReadToEnd()
End If

Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to read file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally
'do not need sr after all the above reading is done
sr.Close()
sr.Dispose()
End Try
'get the height in decimal inches (rounded to 2 places)
Try
If FullNoticeText <"" Then
NoticeTextHeight = (dfc.NoticeTextHeight(FullNoticeText,
fntName, fntSize, clipWidth))
Else
NoticeTextHeight = 0
End If
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while calling dll to get text height on file " & sFileToImport & ". File
NOT
imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand

'get the data into the database
Try

sqlConn = New SqlConnection("Data Source=" & dataSource &
";Initial Catalog=" & dataBase & ";User Id=" & userName & ";Password=" &
password & ";")
sqlConn.Open()

sqlComm = New SqlCommand("sp_ImportNotices", sqlConn)
sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Value = 0
sqlComm.Parameters("RETURN_VALUE").Direction =
ParameterDirection.ReturnValue
sqlComm.Parameters.Add("@AdTypeCode", SqlDbType.VarChar,
50).Value = IIf(AdTypeCode = "", DBNull.Value, AdTypeCode)
sqlComm.Parameters.Add("@ImportType", SqlDbType.VarChar,
50).Value = IIf(ImportType = "", DBNull.Value, ImportType)
sqlComm.Parameters.Add("@AttorneyFileNum", SqlDbType.VarChar,
50).Value = IIf(AttorneyFileNum = "", DBNull.Value, AttorneyFileNum)
sqlComm.Parameters.Add("@AttyOfficeName", SqlDbType.VarChar,
200).Value = IIf(AttyOfficeName = "", DBNull.Value, AttyOfficeName)
sqlComm.Parameters.Add("@MortgagorFirstName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorFirstName = "", DBNull.Value, MortgagorFirstName)
sqlComm.Parameters.Add("@MortgagorLastName", SqlDbType.VarChar,
50).Value = IIf(MortgagorLastName = "", DBNull.Value, MortgagorLastName)
sqlComm.Parameters.Add("@PropAddress1", SqlDbType.VarChar,
100).Value = IIf(PropAddress1 = "", DBNull.Value, PropAddress1)
sqlComm.Parameters.Add("@PropAddress2", SqlDbType.VarChar,
100).Value = IIf(PropAddress2 = "", DBNull.Value, PropAddress2)
sqlComm.Parameters.Add("@PropCity", SqlDbType.VarChar,
100).Value = IIf(PropCity = "", DBNull.Value, PropCity)
sqlComm.Parameters.Add("@PropState", SqlDbType.VarChar,
50).Value = IIf(PropState = "", DBNull.Value, PropState)
sqlComm.Parameters.Add("@PropZip", SqlDbType.VarChar, 20).Value
= IIf(PropZip = "", DBNull.Value, PropZip)
sqlComm.Parameters.Add("@FirstPubDate", SqlDbType.VarChar,
50).Value = IIf(FirstPubDate = "", DBNull.Value, FirstPubDate)
sqlComm.Parameters.Add("@NumWeeksToRun", SqlDbType.VarChar,
10).Value = IIf(NumWeeksToRun = "", DBNull.Value, NumWeeksToRun)
sqlComm.Parameters.Add("@LastPubDate", SqlDbType.VarChar,
50).Value = IIf(LastPubDate = "", DBNull.Value, LastPubDate)
sqlComm.Parameters.Add("@County", SqlDbType.VarChar, 50).Value
=
IIf(County = "", DBNull.Value, County)
sqlComm.Parameters.Add("@DateOfSale", SqlDbType.VarChar,
50).Value = IIf(DateOfSale = "", DBNull.Value, DateOfSale)
sqlComm.Parameters.Add("@FullNoticeText", SqlDbType.Text).Value
= IIf(FullNoticeText = "", DBNull.Value, FullNoticeText)
sqlComm.Parameters.Add("@NoticeTextHeight", SqlDbType.Decimal,
5).Value = CDbl(NoticeTextHeight)
sqlComm.Parameters.Add("@WholeImportFile",
SqlDbType.Text).Value
= IIf(WholeImportFile = "", DBNull.Value, WholeImportFile)
sqlComm.Parameters.Add("@TextFileName", SqlDbType.VarChar,
500).Value = sFileToImport
sqlComm.Parameters.Add("@ReasonsNotPosted", SqlDbType.VarChar,
500).Value = DBNull.Value
sqlComm.Parameters("@ReasonsNotPosted").Direction =
ParameterDirection.Output

sqlComm.ExecuteNonQuery()

If sqlComm.Parameters("RETURN_VALUE").Value <0 Then
txtFolderActivity.Text &= "SQL Error " &
sqlComm.Parameters("RETURN_VALUE").Value.ToString & " on file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss")
&
")." & vbCrLf
Exit Sub
End If

Catch ex As SqlException
txtFolderActivity.Text &= "SQL Error " & ex.Message & "
occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport &
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport &
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()
End Try

'if the "ImportedNoticeFiles" folder does not yet exist, create it
Try
If Not Directory.Exists(textFilesLocation &
"ImportedNoticeFiles") Then
Directory.CreateDirectory(textFilesLocation &
"ImportedNoticeFiles")
'don't go on until the folder has been created
Do Until Directory.Exists(textFilesLocation &
"ImportedNoticeFiles")
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
attempting to create ImportedNoticeFiles folder while importing file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss")
&
")." & vbCrLf
End Try

'store the finished file in the ImportedNoticeFiles folder for
someone to manually review (delete it first if for some reason it already
exists)
Try
If File.Exists(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport) Then
File.Delete(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport)
'Need to wait for the file to be deleted before moving on.
Do Until Not File.Exists(textFilesLocation &
"ImportedNoticeFiles\" & sFileToImport)
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "File " & sFileToImport & " was
imported but IO Error " & ex.Message & " occurred while attempting to
delete
the previously imported file with the same name (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

Try
File.Move(textFilesLocation & sFileToImport, textFilesLocation
&
"ImportedNoticeFiles\" & sFileToImport)
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & " occurred
while attempting to move file " & sFileToImport & " to the
ImportedNoticeFiles folder. File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

txtFolderActivity.Text &= "File " & sFileToImport & " has been
imported successfully and has been moved to the ImportedNoticeFiles
subfolder (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf

End Sub

End Class


Aug 27 '08 #4

P: n/a
Yes. AVG. And Zone Alarm firewall.
"Wolfgang Hauer" <ha***@DELETETHATsysdat.atwrote in message
news:Od**************@TK2MSFTNGP03.phx.gbl...
Any Antivirus prog is running?
Wolfgang

"Keith G Hicks" <kr*@comcast.netschrieb im Newsbeitrag
news:e1**************@TK2MSFTNGP06.phx.gbl...
I'm having a lot of trouble with "file in use" errors in my "folder
watcher"
project. Starting and stopping the watcher and reading my XML file work
fine. Once the watcher is started, I'm reading the text files from the
watched folder line by line into variables and then posting them to a
SQL
table.

All of the code for the form is shown below. And it works fine except
for
2
issues.

First issue: In the Finally of the Try for teh SQL code I have this:

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()

I get this type of intellisense warning: "Variable sqlComm is used
before
it
has been assigned a value. A null reference exception could result at
runtime." What do I need to rearange to avoid this problem? Sometimes it
causes crash and sometimes not. If the file cannot be opened for some
reason, don't I want to free the sqlComm variable if the code fails?

Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder.
They
all get processed exactly as I expect. Everything below appears to run
just
fine. Then, without restartign the watcher program, I copy 4 different
files
into the watched folder. This time only 2 of them are processed. The
other
2
generate the error below:

"The process cannot access the file 'D:\FilesToImport\217068F01.txt'
because
it is being used by another process."

Well it's not in use by anything I'm doing manually. I get this error
pretty
consistently when I do what I described above. Sometimes 2 files don't
work
and sometimes only 1. Sometimes they both process fine but that's very
rare.
Usually at least one of them fails. I have no idea why. It doesn't
depend
on
the particular file either. It's always the first batch (whether it's 1
file
or 30) processes fine after I start up the watcher exe. Then when I drop
any
number of additional files into the watched folder 1 or more of them
generate the error above.

I sure hope someone can point out the problems so I can get this
running.
I'm pretty frustrated with it right now.

Thanks,

Keith
Here's all the code for this form:
Imports System
Imports System.Xml
Imports System.IO
Imports System.Diagnostics
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Public Class NoticeImport

Private watchFolder As FileSystemWatcher
Private dataSource As String, _
dataBase As String, _
userName As String, _
password As String, _
monitoredCustomer As String, _
textFilesLocation As String, _
fntName As String, _
fntSize As Double, _
clipWidth As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub btnStartWatching_Click(ByVal sender As System.Object,
ByVal
e As System.EventArgs) Handles btnStartWatching.Click

watchFolder = New System.IO.FileSystemWatcher()

'Get the db login info and the location of the input text files
from
the xml file

Dim sAppPathAndName As String = Application.ExecutablePath
Dim sAppPathOnly As String = Mid(sAppPathAndName, 1,
InStr(sAppPathAndName, "FNTNoticeImport.exe", CompareMethod.Text) - 1)

textFilesLocation = ""

Try

Dim xrdr As New XmlTextReader(sAppPathOnly &
"FNTNoticeImportSettings.xml")
xrdr.WhitespaceHandling = WhitespaceHandling.None

While xrdr.Read()

If String.Compare(xrdr.Name, "dataSource", True) = 0 Then
dataSource = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "dataBase", True) = 0 Then
dataBase = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "userName", True) = 0 Then
userName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "password", True) = 0 Then
password = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "monitoredCustomer", True) =
0
Then
monitoredCustomer = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "textFilesLocation", True) =
0
Then
textFilesLocation = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntName", True) = 0 Then
fntName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntSize", True) = 0 Then
fntSize = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "clipWidth", True) = 0 Then
clipWidth = xrdr.ReadElementString() 'clip width is
in
hundredths of an inch
End If

End While

xrdr.Close()

'tell the watcher where to watch
watchFolder.Path = textFilesLocation
Me.Text = "Monitoring for Customer: " & monitoredCustomer
txtWatchFolder.Text = "Watching Folder: " & textFilesLocation

'add a list of Filter we want to specify
'make sure you use OR for each Filter as we need to all of
those

watchFolder.NotifyFilter = IO.NotifyFilters.DirectoryName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.FileName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.Attributes
' add the handler to each event
AddHandler watchFolder.Changed, AddressOf logChange
AddHandler watchFolder.Created, AddressOf logChange
AddHandler watchFolder.Deleted, AddressOf logChange

' add the rename handler as the signature is different
AddHandler watchFolder.Renamed, AddressOf logrename

'Set this property to true to start watching
watchFolder.EnableRaisingEvents = True
watchFolder.IncludeSubdirectories = False
watchFolder.Filter = "*.txt"

btnStartWatching.Enabled = False
btnStopWatching.Enabled = True

Catch ex As XmlException
MsgBox("XML Error " & ex.Message & " occurred. Cannot start
watching process.")
Exit Sub

Catch ex As Exception
MsgBox("Error " & ex.Message & " occurred. Cannot start
watching
process.")
Exit Sub

End Try

End Sub

Private Sub logChange(ByVal source As Object, ByVal e As
System.IO.FileSystemEventArgs)

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Changed Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
modified" & vbCrLf
'End If

If e.ChangeType = IO.WatcherChangeTypes.Created Then
'txtFolderActivity.Text &= e.Name & " received at " & Now() &
vbCrLf
If InStr(e.Name, ".txt", CompareMethod.Text) Then
Call ImportTextFiles(e.Name)
'Debug.Print(e.Name)
End If
End If

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Deleted Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
deleted" & vbCrLf
'End If

End Sub

Public Sub logrename(ByVal source As Object, ByVal e As
System.IO.RenamedEventArgs)
'Don't use this for now
'txtFolderActivity.Text &= "File" & e.OldName & " has been
renamed
to " & e.Name & vbCrLf
End Sub

Private Sub btnStopWatching_Click(ByVal sender As System.Object,
ByVal
e
As System.EventArgs) Handles btnStopWatching.Click
' Stop watching the folder
watchFolder.EnableRaisingEvents = False
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub ImportTextFiles(ByVal sFileToImport As String)

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
Dim srLine As String
Dim iFileLineCount As Integer, _
iNoticeTextLineCount As Integer
Dim bInNoticeText As Boolean
Dim AdTypeCode As String, _
ImportType As String, _
AttorneyFileNum As String, _
AttyOfficeName As String, _
MortgagorFirstName As String, _
MortgagorLastName As String, _
PropAddress1 As String, _
PropAddress2 As String, _
PropCity As String, _
PropState As String, _
PropZip As String, _
FirstPubDate As String, _
NumWeeksToRun As String, _
LastPubDate As String, _
County As String, _
DateOfSale As String, _
FullNoticeText As String, _
NoticeTextHeight As Double, _
WholeImportFile As String

ImportType = ""
AttorneyFileNum = ""
AttyOfficeName = ""
MortgagorFirstName = ""
MortgagorLastName = ""
PropAddress1 = ""
PropAddress2 = ""
PropCity = ""
PropState = ""
PropZip = ""
FirstPubDate = ""
NumWeeksToRun = ""
LastPubDate = ""
County = ""
DateOfSale = ""
FullNoticeText = ""
WholeImportFile = ""

bInNoticeText = False
iFileLineCount = 0
iNoticeTextLineCount = 0

Dim sr As StreamReader

'Open the text file
Try
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

'Read the text file line by line
Try
Do
srLine = sr.ReadLine()

iFileLineCount += 1
If iFileLineCount = 1 Then
WholeImportFile = srLine
Else
WholeImportFile = WholeImportFile & vbCrLf & srLine
End If

'TODO 2008-08-13 - AdTypeCode will need to come from
import
file at some point
AdTypeCode = "M"
'If InStr(srLine, "Ad Tpe:", CompareMethod.Text) Then
' AdTypeCode = Trim(Mid(srLine, 8))
'End If

If InStr(srLine, "Publication Notice:",
CompareMethod.Text)
Then
ImportType = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "File Number:", CompareMethod.Text) Then
AttorneyFileNum = Trim(Mid(srLine, 13))
End If

'TODO 2008-08-13 - AttorneyName will need to come from
import file at some point when we use this for other attorneys
If InStr(srLine, "Team:", CompareMethod.Text) Then
AttyOfficeName = Trim(Mid(srLine, 6))
AttyOfficeName = "Trott & Trott P.C. (team " &
AttyOfficeName & ")"
End If

If InStr(srLine, "Mortgagor First:", CompareMethod.Text)
Then
MortgagorFirstName = Trim(Mid(srLine, 17))
End If

If InStr(srLine, "Mortgagor Last:", CompareMethod.Text)
Then
MortgagorLastName = Trim(Mid(srLine, 16))
End If

If InStr(srLine, "Property Address 1:",
CompareMethod.Text)
Then
PropAddress1 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "Property Address 2:",
CompareMethod.Text)
Then
PropAddress2 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "City:", CompareMethod.Text) Then
PropCity = Trim(Mid(srLine, 6))
End If

If InStr(srLine, "State:", CompareMethod.Text) Then
PropState = Trim(Mid(srLine, 7))
End If

If InStr(srLine, "ZIP:", CompareMethod.Text) Then
PropZip = Trim(Mid(srLine, 5))
End If

If InStr(srLine, "First Publication Date:",
CompareMethod.Text) Then
FirstPubDate = Trim(Mid(srLine, 24))
End If

If InStr(srLine, "Number Of Insertions:",
CompareMethod.Text) Then
NumWeeksToRun = Trim(Mid(srLine, 22))
End If

If InStr(srLine, "Last Pub Date:", CompareMethod.Text)
Then
LastPubDate = Trim(Mid(srLine, 15))
End If

If InStr(srLine, "County:", CompareMethod.Text) Then
County = Trim(Mid(srLine, 8))
End If

If InStr(srLine, "Sale Date:", CompareMethod.Text) Then
DateOfSale = Trim(Mid(srLine, 11))
End If

'TODO 2008-08-07 - only do the following if it's new or
correction (not if cancel)
If InStr(srLine, "*** Begin Notice ***",
CompareMethod.Text)
0 Then
'skip 1 line and read the rest into the
FullNoticeText
variable
srLine = sr.ReadLine()
iNoticeTextLineCount = 1 'we're on line one now and
will
get it into FullNoticeText below
WholeImportFile = WholeImportFile & vbCrLf & srLine
'have to do this here because we're skipping a line. if we don't then
the
similar line above won't catch the skipped line
bInNoticeText = True
End If

If bInNoticeText Then
If InStr(srLine, "*** End Notice ***",
CompareMethod.Text) = 0 Then
If iNoticeTextLineCount = 1 Then
FullNoticeText = srLine
Else
FullNoticeText = FullNoticeText & vbCrLf &
srLine
End If
iNoticeTextLineCount += 1
End If
End If

Loop Until InStr(srLine, "*** End Notice ***",
CompareMethod.Text) <0
'If this is a correction, then there is info AFTER the "***
End
Notice ***" line that we need to read into the WholeImportFile variable
If String.Compare(ImportType, "Correct", True) = 0 Then
WholeImportFile = WholeImportFile & vbCrLf &
sr.ReadToEnd()
End If

Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to read file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally
'do not need sr after all the above reading is done
sr.Close()
sr.Dispose()
End Try
'get the height in decimal inches (rounded to 2 places)
Try
If FullNoticeText <"" Then
NoticeTextHeight = (dfc.NoticeTextHeight(FullNoticeText,
fntName, fntSize, clipWidth))
Else
NoticeTextHeight = 0
End If
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while calling dll to get text height on file " & sFileToImport & ". File
NOT
imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand

'get the data into the database
Try

sqlConn = New SqlConnection("Data Source=" & dataSource &
";Initial Catalog=" & dataBase & ";User Id=" & userName & ";Password=" &
password & ";")
sqlConn.Open()

sqlComm = New SqlCommand("sp_ImportNotices", sqlConn)
sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Value =
0
sqlComm.Parameters("RETURN_VALUE").Direction =
ParameterDirection.ReturnValue
sqlComm.Parameters.Add("@AdTypeCode", SqlDbType.VarChar,
50).Value = IIf(AdTypeCode = "", DBNull.Value, AdTypeCode)
sqlComm.Parameters.Add("@ImportType", SqlDbType.VarChar,
50).Value = IIf(ImportType = "", DBNull.Value, ImportType)
sqlComm.Parameters.Add("@AttorneyFileNum", SqlDbType.VarChar,
50).Value = IIf(AttorneyFileNum = "", DBNull.Value, AttorneyFileNum)
sqlComm.Parameters.Add("@AttyOfficeName", SqlDbType.VarChar,
200).Value = IIf(AttyOfficeName = "", DBNull.Value, AttyOfficeName)
sqlComm.Parameters.Add("@MortgagorFirstName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorFirstName = "", DBNull.Value,
MortgagorFirstName)
sqlComm.Parameters.Add("@MortgagorLastName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorLastName = "", DBNull.Value, MortgagorLastName)
sqlComm.Parameters.Add("@PropAddress1", SqlDbType.VarChar,
100).Value = IIf(PropAddress1 = "", DBNull.Value, PropAddress1)
sqlComm.Parameters.Add("@PropAddress2", SqlDbType.VarChar,
100).Value = IIf(PropAddress2 = "", DBNull.Value, PropAddress2)
sqlComm.Parameters.Add("@PropCity", SqlDbType.VarChar,
100).Value = IIf(PropCity = "", DBNull.Value, PropCity)
sqlComm.Parameters.Add("@PropState", SqlDbType.VarChar,
50).Value = IIf(PropState = "", DBNull.Value, PropState)
sqlComm.Parameters.Add("@PropZip", SqlDbType.VarChar,
20).Value
= IIf(PropZip = "", DBNull.Value, PropZip)
sqlComm.Parameters.Add("@FirstPubDate", SqlDbType.VarChar,
50).Value = IIf(FirstPubDate = "", DBNull.Value, FirstPubDate)
sqlComm.Parameters.Add("@NumWeeksToRun", SqlDbType.VarChar,
10).Value = IIf(NumWeeksToRun = "", DBNull.Value, NumWeeksToRun)
sqlComm.Parameters.Add("@LastPubDate", SqlDbType.VarChar,
50).Value = IIf(LastPubDate = "", DBNull.Value, LastPubDate)
sqlComm.Parameters.Add("@County", SqlDbType.VarChar,
50).Value
=
IIf(County = "", DBNull.Value, County)
sqlComm.Parameters.Add("@DateOfSale", SqlDbType.VarChar,
50).Value = IIf(DateOfSale = "", DBNull.Value, DateOfSale)
sqlComm.Parameters.Add("@FullNoticeText",
SqlDbType.Text).Value
= IIf(FullNoticeText = "", DBNull.Value, FullNoticeText)
sqlComm.Parameters.Add("@NoticeTextHeight",
SqlDbType.Decimal,
5).Value = CDbl(NoticeTextHeight)
sqlComm.Parameters.Add("@WholeImportFile",
SqlDbType.Text).Value
= IIf(WholeImportFile = "", DBNull.Value, WholeImportFile)
sqlComm.Parameters.Add("@TextFileName", SqlDbType.VarChar,
500).Value = sFileToImport
sqlComm.Parameters.Add("@ReasonsNotPosted",
SqlDbType.VarChar,
500).Value = DBNull.Value
sqlComm.Parameters("@ReasonsNotPosted").Direction =
ParameterDirection.Output

sqlComm.ExecuteNonQuery()

If sqlComm.Parameters("RETURN_VALUE").Value <0 Then
txtFolderActivity.Text &= "SQL Error " &
sqlComm.Parameters("RETURN_VALUE").Value.ToString & " on file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss")
&
")." & vbCrLf
Exit Sub
End If

Catch ex As SqlException
txtFolderActivity.Text &= "SQL Error " & ex.Message & "
occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport
&
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." &
vbCrLf
Exit Sub
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport
&
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." &
vbCrLf
Exit Sub
Finally

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()
End Try

'if the "ImportedNoticeFiles" folder does not yet exist, create
it
Try
If Not Directory.Exists(textFilesLocation &
"ImportedNoticeFiles") Then
Directory.CreateDirectory(textFilesLocation &
"ImportedNoticeFiles")
'don't go on until the folder has been created
Do Until Directory.Exists(textFilesLocation &
"ImportedNoticeFiles")
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
attempting to create ImportedNoticeFiles folder while importing file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss")
&
")." & vbCrLf
End Try

'store the finished file in the ImportedNoticeFiles folder for
someone to manually review (delete it first if for some reason it
already
exists)
Try
If File.Exists(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport) Then
File.Delete(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport)
'Need to wait for the file to be deleted before moving
on.
Do Until Not File.Exists(textFilesLocation &
"ImportedNoticeFiles\" & sFileToImport)
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "File " & sFileToImport & " was
imported but IO Error " & ex.Message & " occurred while attempting to
delete
the previously imported file with the same name (" & Format(Now(),
"m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

Try
File.Move(textFilesLocation & sFileToImport,
textFilesLocation
&
"ImportedNoticeFiles\" & sFileToImport)
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
occurred
while attempting to move file " & sFileToImport & " to the
ImportedNoticeFiles folder. File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

txtFolderActivity.Text &= "File " & sFileToImport & " has been
imported successfully and has been moved to the ImportedNoticeFiles
subfolder (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf

End Sub

End Class


Aug 27 '08 #5

P: n/a
Well I added the following looping strategy (hack) to hopefully solve the
problem of FileWatcherSystem class trying to fire events multiple times and
it seems to be working better:

'Open the text file

Dim iNumTries As Int16
iNumTries = 0
TryOpeningFile:
If iNumTries 10 Then
Exit Sub
End If
Try
iNumTries += 1
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
System.Threading.Thread.Sleep(500)
GoTo TryOpeningFile
'Exit Sub
End Try
"Keith G Hicks" <kr*@comcast.netwrote in message
news:e1**************@TK2MSFTNGP06.phx.gbl...
I'm having a lot of trouble with "file in use" errors in my "folder
watcher"
project. Starting and stopping the watcher and reading my XML file work
fine. Once the watcher is started, I'm reading the text files from the
watched folder line by line into variables and then posting them to a SQL
table.

All of the code for the form is shown below. And it works fine except for
2
issues.

First issue: In the Finally of the Try for teh SQL code I have this:

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()

I get this type of intellisense warning: "Variable sqlComm is used before
it
has been assigned a value. A null reference exception could result at
runtime." What do I need to rearange to avoid this problem? Sometimes it
causes crash and sometimes not. If the file cannot be opened for some
reason, don't I want to free the sqlComm variable if the code fails?

Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder. They
all get processed exactly as I expect. Everything below appears to run
just
fine. Then, without restartign the watcher program, I copy 4 different
files
into the watched folder. This time only 2 of them are processed. The other
2
generate the error below:

"The process cannot access the file 'D:\FilesToImport\217068F01.txt'
because
it is being used by another process."

Well it's not in use by anything I'm doing manually. I get this error
pretty
consistently when I do what I described above. Sometimes 2 files don't
work
and sometimes only 1. Sometimes they both process fine but that's very
rare.
Usually at least one of them fails. I have no idea why. It doesn't depend
on
the particular file either. It's always the first batch (whether it's 1
file
or 30) processes fine after I start up the watcher exe. Then when I drop
any
number of additional files into the watched folder 1 or more of them
generate the error above.

I sure hope someone can point out the problems so I can get this running.
I'm pretty frustrated with it right now.

Thanks,

Keith
Here's all the code for this form:
Imports System
Imports System.Xml
Imports System.IO
Imports System.Diagnostics
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Public Class NoticeImport

Private watchFolder As FileSystemWatcher
Private dataSource As String, _
dataBase As String, _
userName As String, _
password As String, _
monitoredCustomer As String, _
textFilesLocation As String, _
fntName As String, _
fntSize As Double, _
clipWidth As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub btnStartWatching_Click(ByVal sender As System.Object,
ByVal
e As System.EventArgs) Handles btnStartWatching.Click

watchFolder = New System.IO.FileSystemWatcher()

'Get the db login info and the location of the input text files
from
the xml file

Dim sAppPathAndName As String = Application.ExecutablePath
Dim sAppPathOnly As String = Mid(sAppPathAndName, 1,
InStr(sAppPathAndName, "FNTNoticeImport.exe", CompareMethod.Text) - 1)

textFilesLocation = ""

Try

Dim xrdr As New XmlTextReader(sAppPathOnly &
"FNTNoticeImportSettings.xml")
xrdr.WhitespaceHandling = WhitespaceHandling.None

While xrdr.Read()

If String.Compare(xrdr.Name, "dataSource", True) = 0 Then
dataSource = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "dataBase", True) = 0 Then
dataBase = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "userName", True) = 0 Then
userName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "password", True) = 0 Then
password = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "monitoredCustomer", True) =
0
Then
monitoredCustomer = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "textFilesLocation", True) =
0
Then
textFilesLocation = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntName", True) = 0 Then
fntName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntSize", True) = 0 Then
fntSize = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "clipWidth", True) = 0 Then
clipWidth = xrdr.ReadElementString() 'clip width is in
hundredths of an inch
End If

End While

xrdr.Close()

'tell the watcher where to watch
watchFolder.Path = textFilesLocation
Me.Text = "Monitoring for Customer: " & monitoredCustomer
txtWatchFolder.Text = "Watching Folder: " & textFilesLocation

'add a list of Filter we want to specify
'make sure you use OR for each Filter as we need to all of
those
>
watchFolder.NotifyFilter = IO.NotifyFilters.DirectoryName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.FileName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.Attributes
' add the handler to each event
AddHandler watchFolder.Changed, AddressOf logChange
AddHandler watchFolder.Created, AddressOf logChange
AddHandler watchFolder.Deleted, AddressOf logChange

' add the rename handler as the signature is different
AddHandler watchFolder.Renamed, AddressOf logrename

'Set this property to true to start watching
watchFolder.EnableRaisingEvents = True
watchFolder.IncludeSubdirectories = False
watchFolder.Filter = "*.txt"

btnStartWatching.Enabled = False
btnStopWatching.Enabled = True

Catch ex As XmlException
MsgBox("XML Error " & ex.Message & " occurred. Cannot start
watching process.")
Exit Sub

Catch ex As Exception
MsgBox("Error " & ex.Message & " occurred. Cannot start
watching
process.")
Exit Sub

End Try

End Sub

Private Sub logChange(ByVal source As Object, ByVal e As
System.IO.FileSystemEventArgs)

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Changed Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
modified" & vbCrLf
'End If

If e.ChangeType = IO.WatcherChangeTypes.Created Then
'txtFolderActivity.Text &= e.Name & " received at " & Now() &
vbCrLf
If InStr(e.Name, ".txt", CompareMethod.Text) Then
Call ImportTextFiles(e.Name)
'Debug.Print(e.Name)
End If
End If

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Deleted Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
deleted" & vbCrLf
'End If

End Sub

Public Sub logrename(ByVal source As Object, ByVal e As
System.IO.RenamedEventArgs)
'Don't use this for now
'txtFolderActivity.Text &= "File" & e.OldName & " has been renamed
to " & e.Name & vbCrLf
End Sub

Private Sub btnStopWatching_Click(ByVal sender As System.Object, ByVal
e
As System.EventArgs) Handles btnStopWatching.Click
' Stop watching the folder
watchFolder.EnableRaisingEvents = False
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub ImportTextFiles(ByVal sFileToImport As String)

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
Dim srLine As String
Dim iFileLineCount As Integer, _
iNoticeTextLineCount As Integer
Dim bInNoticeText As Boolean
Dim AdTypeCode As String, _
ImportType As String, _
AttorneyFileNum As String, _
AttyOfficeName As String, _
MortgagorFirstName As String, _
MortgagorLastName As String, _
PropAddress1 As String, _
PropAddress2 As String, _
PropCity As String, _
PropState As String, _
PropZip As String, _
FirstPubDate As String, _
NumWeeksToRun As String, _
LastPubDate As String, _
County As String, _
DateOfSale As String, _
FullNoticeText As String, _
NoticeTextHeight As Double, _
WholeImportFile As String

ImportType = ""
AttorneyFileNum = ""
AttyOfficeName = ""
MortgagorFirstName = ""
MortgagorLastName = ""
PropAddress1 = ""
PropAddress2 = ""
PropCity = ""
PropState = ""
PropZip = ""
FirstPubDate = ""
NumWeeksToRun = ""
LastPubDate = ""
County = ""
DateOfSale = ""
FullNoticeText = ""
WholeImportFile = ""

bInNoticeText = False
iFileLineCount = 0
iNoticeTextLineCount = 0

Dim sr As StreamReader

'Open the text file
Try
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

'Read the text file line by line
Try
Do
srLine = sr.ReadLine()

iFileLineCount += 1
If iFileLineCount = 1 Then
WholeImportFile = srLine
Else
WholeImportFile = WholeImportFile & vbCrLf & srLine
End If

'TODO 2008-08-13 - AdTypeCode will need to come from
import
file at some point
AdTypeCode = "M"
'If InStr(srLine, "Ad Tpe:", CompareMethod.Text) Then
' AdTypeCode = Trim(Mid(srLine, 8))
'End If

If InStr(srLine, "Publication Notice:",
CompareMethod.Text)
Then
ImportType = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "File Number:", CompareMethod.Text) Then
AttorneyFileNum = Trim(Mid(srLine, 13))
End If

'TODO 2008-08-13 - AttorneyName will need to come from
import file at some point when we use this for other attorneys
If InStr(srLine, "Team:", CompareMethod.Text) Then
AttyOfficeName = Trim(Mid(srLine, 6))
AttyOfficeName = "Trott & Trott P.C. (team " &
AttyOfficeName & ")"
End If

If InStr(srLine, "Mortgagor First:", CompareMethod.Text)
Then
MortgagorFirstName = Trim(Mid(srLine, 17))
End If

If InStr(srLine, "Mortgagor Last:", CompareMethod.Text)
Then
MortgagorLastName = Trim(Mid(srLine, 16))
End If

If InStr(srLine, "Property Address 1:",
CompareMethod.Text)
Then
PropAddress1 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "Property Address 2:",
CompareMethod.Text)
Then
PropAddress2 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "City:", CompareMethod.Text) Then
PropCity = Trim(Mid(srLine, 6))
End If

If InStr(srLine, "State:", CompareMethod.Text) Then
PropState = Trim(Mid(srLine, 7))
End If

If InStr(srLine, "ZIP:", CompareMethod.Text) Then
PropZip = Trim(Mid(srLine, 5))
End If

If InStr(srLine, "First Publication Date:",
CompareMethod.Text) Then
FirstPubDate = Trim(Mid(srLine, 24))
End If

If InStr(srLine, "Number Of Insertions:",
CompareMethod.Text) Then
NumWeeksToRun = Trim(Mid(srLine, 22))
End If

If InStr(srLine, "Last Pub Date:", CompareMethod.Text)
Then
LastPubDate = Trim(Mid(srLine, 15))
End If

If InStr(srLine, "County:", CompareMethod.Text) Then
County = Trim(Mid(srLine, 8))
End If

If InStr(srLine, "Sale Date:", CompareMethod.Text) Then
DateOfSale = Trim(Mid(srLine, 11))
End If

'TODO 2008-08-07 - only do the following if it's new or
correction (not if cancel)
If InStr(srLine, "*** Begin Notice ***",
CompareMethod.Text)
0 Then
'skip 1 line and read the rest into the FullNoticeText
variable
srLine = sr.ReadLine()
iNoticeTextLineCount = 1 'we're on line one now and
will
get it into FullNoticeText below
WholeImportFile = WholeImportFile & vbCrLf & srLine
'have to do this here because we're skipping a line. if we don't then the
similar line above won't catch the skipped line
bInNoticeText = True
End If

If bInNoticeText Then
If InStr(srLine, "*** End Notice ***",
CompareMethod.Text) = 0 Then
If iNoticeTextLineCount = 1 Then
FullNoticeText = srLine
Else
FullNoticeText = FullNoticeText & vbCrLf &
srLine
End If
iNoticeTextLineCount += 1
End If
End If

Loop Until InStr(srLine, "*** End Notice ***",
CompareMethod.Text) <0
'If this is a correction, then there is info AFTER the "***
End
Notice ***" line that we need to read into the WholeImportFile variable
If String.Compare(ImportType, "Correct", True) = 0 Then
WholeImportFile = WholeImportFile & vbCrLf &
sr.ReadToEnd()
End If

Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to read file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally
'do not need sr after all the above reading is done
sr.Close()
sr.Dispose()
End Try
'get the height in decimal inches (rounded to 2 places)
Try
If FullNoticeText <"" Then
NoticeTextHeight = (dfc.NoticeTextHeight(FullNoticeText,
fntName, fntSize, clipWidth))
Else
NoticeTextHeight = 0
End If
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while calling dll to get text height on file " & sFileToImport & ". File
NOT
imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand

'get the data into the database
Try

sqlConn = New SqlConnection("Data Source=" & dataSource &
";Initial Catalog=" & dataBase & ";User Id=" & userName & ";Password=" &
password & ";")
sqlConn.Open()

sqlComm = New SqlCommand("sp_ImportNotices", sqlConn)
sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Value =
0
sqlComm.Parameters("RETURN_VALUE").Direction =
ParameterDirection.ReturnValue
sqlComm.Parameters.Add("@AdTypeCode", SqlDbType.VarChar,
50).Value = IIf(AdTypeCode = "", DBNull.Value, AdTypeCode)
sqlComm.Parameters.Add("@ImportType", SqlDbType.VarChar,
50).Value = IIf(ImportType = "", DBNull.Value, ImportType)
sqlComm.Parameters.Add("@AttorneyFileNum", SqlDbType.VarChar,
50).Value = IIf(AttorneyFileNum = "", DBNull.Value, AttorneyFileNum)
sqlComm.Parameters.Add("@AttyOfficeName", SqlDbType.VarChar,
200).Value = IIf(AttyOfficeName = "", DBNull.Value, AttyOfficeName)
sqlComm.Parameters.Add("@MortgagorFirstName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorFirstName = "", DBNull.Value, MortgagorFirstName)
sqlComm.Parameters.Add("@MortgagorLastName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorLastName = "", DBNull.Value, MortgagorLastName)
sqlComm.Parameters.Add("@PropAddress1", SqlDbType.VarChar,
100).Value = IIf(PropAddress1 = "", DBNull.Value, PropAddress1)
sqlComm.Parameters.Add("@PropAddress2", SqlDbType.VarChar,
100).Value = IIf(PropAddress2 = "", DBNull.Value, PropAddress2)
sqlComm.Parameters.Add("@PropCity", SqlDbType.VarChar,
100).Value = IIf(PropCity = "", DBNull.Value, PropCity)
sqlComm.Parameters.Add("@PropState", SqlDbType.VarChar,
50).Value = IIf(PropState = "", DBNull.Value, PropState)
sqlComm.Parameters.Add("@PropZip", SqlDbType.VarChar,
20).Value
= IIf(PropZip = "", DBNull.Value, PropZip)
sqlComm.Parameters.Add("@FirstPubDate", SqlDbType.VarChar,
50).Value = IIf(FirstPubDate = "", DBNull.Value, FirstPubDate)
sqlComm.Parameters.Add("@NumWeeksToRun", SqlDbType.VarChar,
10).Value = IIf(NumWeeksToRun = "", DBNull.Value, NumWeeksToRun)
sqlComm.Parameters.Add("@LastPubDate", SqlDbType.VarChar,
50).Value = IIf(LastPubDate = "", DBNull.Value, LastPubDate)
sqlComm.Parameters.Add("@County", SqlDbType.VarChar, 50).Value
=
IIf(County = "", DBNull.Value, County)
sqlComm.Parameters.Add("@DateOfSale", SqlDbType.VarChar,
50).Value = IIf(DateOfSale = "", DBNull.Value, DateOfSale)
sqlComm.Parameters.Add("@FullNoticeText",
SqlDbType.Text).Value
= IIf(FullNoticeText = "", DBNull.Value, FullNoticeText)
sqlComm.Parameters.Add("@NoticeTextHeight", SqlDbType.Decimal,
5).Value = CDbl(NoticeTextHeight)
sqlComm.Parameters.Add("@WholeImportFile",
SqlDbType.Text).Value
= IIf(WholeImportFile = "", DBNull.Value, WholeImportFile)
sqlComm.Parameters.Add("@TextFileName", SqlDbType.VarChar,
500).Value = sFileToImport
sqlComm.Parameters.Add("@ReasonsNotPosted", SqlDbType.VarChar,
500).Value = DBNull.Value
sqlComm.Parameters("@ReasonsNotPosted").Direction =
ParameterDirection.Output

sqlComm.ExecuteNonQuery()

If sqlComm.Parameters("RETURN_VALUE").Value <0 Then
txtFolderActivity.Text &= "SQL Error " &
sqlComm.Parameters("RETURN_VALUE").Value.ToString & " on file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss")
&
")." & vbCrLf
Exit Sub
End If

Catch ex As SqlException
txtFolderActivity.Text &= "SQL Error " & ex.Message & "
occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport &
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport &
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()
End Try

'if the "ImportedNoticeFiles" folder does not yet exist, create it
Try
If Not Directory.Exists(textFilesLocation &
"ImportedNoticeFiles") Then
Directory.CreateDirectory(textFilesLocation &
"ImportedNoticeFiles")
'don't go on until the folder has been created
Do Until Directory.Exists(textFilesLocation &
"ImportedNoticeFiles")
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
attempting to create ImportedNoticeFiles folder while importing file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss")
&
")." & vbCrLf
End Try

'store the finished file in the ImportedNoticeFiles folder for
someone to manually review (delete it first if for some reason it already
exists)
Try
If File.Exists(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport) Then
File.Delete(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport)
'Need to wait for the file to be deleted before moving on.
Do Until Not File.Exists(textFilesLocation &
"ImportedNoticeFiles\" & sFileToImport)
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "File " & sFileToImport & " was
imported but IO Error " & ex.Message & " occurred while attempting to
delete
the previously imported file with the same name (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

Try
File.Move(textFilesLocation & sFileToImport, textFilesLocation
&
"ImportedNoticeFiles\" & sFileToImport)
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
occurred
while attempting to move file " & sFileToImport & " to the
ImportedNoticeFiles folder. File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

txtFolderActivity.Text &= "File " & sFileToImport & " has been
imported successfully and has been moved to the ImportedNoticeFiles
subfolder (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf

End Sub

End Class


Aug 27 '08 #6

P: n/a
Still unpredictable. It helped but didn't completely solve the problem. Ugh.
:-(

"Keith G Hicks" <kr*@comcast.netwrote in message
news:uv**************@TK2MSFTNGP03.phx.gbl...
Well I added the following looping strategy (hack) to hopefully solve the
problem of FileWatcherSystem class trying to fire events multiple times
and
it seems to be working better:

'Open the text file

Dim iNumTries As Int16
iNumTries = 0
TryOpeningFile:
If iNumTries 10 Then
Exit Sub
End If
Try
iNumTries += 1
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & "
occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
System.Threading.Thread.Sleep(500)
GoTo TryOpeningFile
'Exit Sub
End Try
"Keith G Hicks" <kr*@comcast.netwrote in message
news:e1**************@TK2MSFTNGP06.phx.gbl...
I'm having a lot of trouble with "file in use" errors in my "folder
watcher"
project. Starting and stopping the watcher and reading my XML file work
fine. Once the watcher is started, I'm reading the text files from the
watched folder line by line into variables and then posting them to a
SQL
table.

All of the code for the form is shown below. And it works fine except
for
2
issues.

First issue: In the Finally of the Try for teh SQL code I have this:

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()

I get this type of intellisense warning: "Variable sqlComm is used
before
it
has been assigned a value. A null reference exception could result at
runtime." What do I need to rearange to avoid this problem? Sometimes it
causes crash and sometimes not. If the file cannot be opened for some
reason, don't I want to free the sqlComm variable if the code fails?

Second issue (and this is the big problem really): IO errors. I run the
program and then copy and paste 4 text files into the watched folder.
They
all get processed exactly as I expect. Everything below appears to run
just
fine. Then, without restartign the watcher program, I copy 4 different
files
into the watched folder. This time only 2 of them are processed. The
other
2
generate the error below:

"The process cannot access the file 'D:\FilesToImport\217068F01.txt'
because
it is being used by another process."

Well it's not in use by anything I'm doing manually. I get this error
pretty
consistently when I do what I described above. Sometimes 2 files don't
work
and sometimes only 1. Sometimes they both process fine but that's very
rare.
Usually at least one of them fails. I have no idea why. It doesn't
depend
on
the particular file either. It's always the first batch (whether it's 1
file
or 30) processes fine after I start up the watcher exe. Then when I drop
any
number of additional files into the watched folder 1 or more of them
generate the error above.

I sure hope someone can point out the problems so I can get this
running.
I'm pretty frustrated with it right now.

Thanks,

Keith
Here's all the code for this form:
Imports System
Imports System.Xml
Imports System.IO
Imports System.Diagnostics
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Public Class NoticeImport

Private watchFolder As FileSystemWatcher
Private dataSource As String, _
dataBase As String, _
userName As String, _
password As String, _
monitoredCustomer As String, _
textFilesLocation As String, _
fntName As String, _
fntSize As Double, _
clipWidth As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub btnStartWatching_Click(ByVal sender As System.Object,
ByVal
e As System.EventArgs) Handles btnStartWatching.Click

watchFolder = New System.IO.FileSystemWatcher()

'Get the db login info and the location of the input text files
from
the xml file

Dim sAppPathAndName As String = Application.ExecutablePath
Dim sAppPathOnly As String = Mid(sAppPathAndName, 1,
InStr(sAppPathAndName, "FNTNoticeImport.exe", CompareMethod.Text) - 1)

textFilesLocation = ""

Try

Dim xrdr As New XmlTextReader(sAppPathOnly &
"FNTNoticeImportSettings.xml")
xrdr.WhitespaceHandling = WhitespaceHandling.None

While xrdr.Read()

If String.Compare(xrdr.Name, "dataSource", True) = 0
Then
dataSource = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "dataBase", True) = 0 Then
dataBase = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "userName", True) = 0 Then
userName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "password", True) = 0 Then
password = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "monitoredCustomer", True)
=
0
Then
monitoredCustomer = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "textFilesLocation", True)
=
0
Then
textFilesLocation = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntName", True) = 0 Then
fntName = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "fntSize", True) = 0 Then
fntSize = xrdr.ReadElementString()
End If

If String.Compare(xrdr.Name, "clipWidth", True) = 0 Then
clipWidth = xrdr.ReadElementString() 'clip width is
in
hundredths of an inch
End If

End While

xrdr.Close()

'tell the watcher where to watch
watchFolder.Path = textFilesLocation
Me.Text = "Monitoring for Customer: " & monitoredCustomer
txtWatchFolder.Text = "Watching Folder: " &
textFilesLocation

'add a list of Filter we want to specify
'make sure you use OR for each Filter as we need to all of
those

watchFolder.NotifyFilter = IO.NotifyFilters.DirectoryName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.FileName
watchFolder.NotifyFilter = watchFolder.NotifyFilter Or
IO.NotifyFilters.Attributes
' add the handler to each event
AddHandler watchFolder.Changed, AddressOf logChange
AddHandler watchFolder.Created, AddressOf logChange
AddHandler watchFolder.Deleted, AddressOf logChange

' add the rename handler as the signature is different
AddHandler watchFolder.Renamed, AddressOf logrename

'Set this property to true to start watching
watchFolder.EnableRaisingEvents = True
watchFolder.IncludeSubdirectories = False
watchFolder.Filter = "*.txt"

btnStartWatching.Enabled = False
btnStopWatching.Enabled = True

Catch ex As XmlException
MsgBox("XML Error " & ex.Message & " occurred. Cannot start
watching process.")
Exit Sub

Catch ex As Exception
MsgBox("Error " & ex.Message & " occurred. Cannot start
watching
process.")
Exit Sub

End Try

End Sub

Private Sub logChange(ByVal source As Object, ByVal e As
System.IO.FileSystemEventArgs)

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Changed Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
modified" & vbCrLf
'End If

If e.ChangeType = IO.WatcherChangeTypes.Created Then
'txtFolderActivity.Text &= e.Name & " received at " & Now()
&
vbCrLf
If InStr(e.Name, ".txt", CompareMethod.Text) Then
Call ImportTextFiles(e.Name)
'Debug.Print(e.Name)
End If
End If

'Don't use this for now
'If e.ChangeType = IO.WatcherChangeTypes.Deleted Then
' txtFolderActivity.Text &= "File " & e.FullPath & " has been
deleted" & vbCrLf
'End If

End Sub

Public Sub logrename(ByVal source As Object, ByVal e As
System.IO.RenamedEventArgs)
'Don't use this for now
'txtFolderActivity.Text &= "File" & e.OldName & " has been
renamed
to " & e.Name & vbCrLf
End Sub

Private Sub btnStopWatching_Click(ByVal sender As System.Object,
ByVal
e
As System.EventArgs) Handles btnStopWatching.Click
' Stop watching the folder
watchFolder.EnableRaisingEvents = False
btnStartWatching.Enabled = True
btnStopWatching.Enabled = False
Me.Text = "Monitoring for Customer: None"
txtWatchFolder.Text = "Watching Folder: None "
End Sub

Private Sub ImportTextFiles(ByVal sFileToImport As String)

'Process the file

Dim dfc As New DlnpFntCalcs.DlnpFntCalcs
Dim srLine As String
Dim iFileLineCount As Integer, _
iNoticeTextLineCount As Integer
Dim bInNoticeText As Boolean
Dim AdTypeCode As String, _
ImportType As String, _
AttorneyFileNum As String, _
AttyOfficeName As String, _
MortgagorFirstName As String, _
MortgagorLastName As String, _
PropAddress1 As String, _
PropAddress2 As String, _
PropCity As String, _
PropState As String, _
PropZip As String, _
FirstPubDate As String, _
NumWeeksToRun As String, _
LastPubDate As String, _
County As String, _
DateOfSale As String, _
FullNoticeText As String, _
NoticeTextHeight As Double, _
WholeImportFile As String

ImportType = ""
AttorneyFileNum = ""
AttyOfficeName = ""
MortgagorFirstName = ""
MortgagorLastName = ""
PropAddress1 = ""
PropAddress2 = ""
PropCity = ""
PropState = ""
PropZip = ""
FirstPubDate = ""
NumWeeksToRun = ""
LastPubDate = ""
County = ""
DateOfSale = ""
FullNoticeText = ""
WholeImportFile = ""

bInNoticeText = False
iFileLineCount = 0
iNoticeTextLineCount = 0

Dim sr As StreamReader

'Open the text file
Try
sr = New StreamReader(textFilesLocation & sFileToImport)
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to open file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

'Read the text file line by line
Try
Do
srLine = sr.ReadLine()

iFileLineCount += 1
If iFileLineCount = 1 Then
WholeImportFile = srLine
Else
WholeImportFile = WholeImportFile & vbCrLf & srLine
End If

'TODO 2008-08-13 - AdTypeCode will need to come from
import
file at some point
AdTypeCode = "M"
'If InStr(srLine, "Ad Tpe:", CompareMethod.Text) Then
' AdTypeCode = Trim(Mid(srLine, 8))
'End If

If InStr(srLine, "Publication Notice:",
CompareMethod.Text)
Then
ImportType = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "File Number:", CompareMethod.Text)
Then
AttorneyFileNum = Trim(Mid(srLine, 13))
End If

'TODO 2008-08-13 - AttorneyName will need to come from
import file at some point when we use this for other attorneys
If InStr(srLine, "Team:", CompareMethod.Text) Then
AttyOfficeName = Trim(Mid(srLine, 6))
AttyOfficeName = "Trott & Trott P.C. (team " &
AttyOfficeName & ")"
End If

If InStr(srLine, "Mortgagor First:", CompareMethod.Text)
Then
MortgagorFirstName = Trim(Mid(srLine, 17))
End If

If InStr(srLine, "Mortgagor Last:", CompareMethod.Text)
Then
MortgagorLastName = Trim(Mid(srLine, 16))
End If

If InStr(srLine, "Property Address 1:",
CompareMethod.Text)
Then
PropAddress1 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "Property Address 2:",
CompareMethod.Text)
Then
PropAddress2 = Trim(Mid(srLine, 20))
End If

If InStr(srLine, "City:", CompareMethod.Text) Then
PropCity = Trim(Mid(srLine, 6))
End If

If InStr(srLine, "State:", CompareMethod.Text) Then
PropState = Trim(Mid(srLine, 7))
End If

If InStr(srLine, "ZIP:", CompareMethod.Text) Then
PropZip = Trim(Mid(srLine, 5))
End If

If InStr(srLine, "First Publication Date:",
CompareMethod.Text) Then
FirstPubDate = Trim(Mid(srLine, 24))
End If

If InStr(srLine, "Number Of Insertions:",
CompareMethod.Text) Then
NumWeeksToRun = Trim(Mid(srLine, 22))
End If

If InStr(srLine, "Last Pub Date:", CompareMethod.Text)
Then
LastPubDate = Trim(Mid(srLine, 15))
End If

If InStr(srLine, "County:", CompareMethod.Text) Then
County = Trim(Mid(srLine, 8))
End If

If InStr(srLine, "Sale Date:", CompareMethod.Text) Then
DateOfSale = Trim(Mid(srLine, 11))
End If

'TODO 2008-08-07 - only do the following if it's new or
correction (not if cancel)
If InStr(srLine, "*** Begin Notice ***",
CompareMethod.Text)
0 Then
'skip 1 line and read the rest into the
FullNoticeText
variable
srLine = sr.ReadLine()
iNoticeTextLineCount = 1 'we're on line one now and
will
get it into FullNoticeText below
WholeImportFile = WholeImportFile & vbCrLf & srLine
'have to do this here because we're skipping a line. if we don't then
the
similar line above won't catch the skipped line
bInNoticeText = True
End If

If bInNoticeText Then
If InStr(srLine, "*** End Notice ***",
CompareMethod.Text) = 0 Then
If iNoticeTextLineCount = 1 Then
FullNoticeText = srLine
Else
FullNoticeText = FullNoticeText & vbCrLf &
srLine
End If
iNoticeTextLineCount += 1
End If
End If

Loop Until InStr(srLine, "*** End Notice ***",
CompareMethod.Text) <0
'If this is a correction, then there is info AFTER the "***
End
Notice ***" line that we need to read into the WholeImportFile variable
If String.Compare(ImportType, "Correct", True) = 0 Then
WholeImportFile = WholeImportFile & vbCrLf &
sr.ReadToEnd()
End If

Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while trying to read file " & sFileToImport & ". File NOT imported (" &
Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
Finally
'do not need sr after all the above reading is done
sr.Close()
sr.Dispose()
End Try
'get the height in decimal inches (rounded to 2 places)
Try
If FullNoticeText <"" Then
NoticeTextHeight = (dfc.NoticeTextHeight(FullNoticeText,
fntName, fntSize, clipWidth))
Else
NoticeTextHeight = 0
End If
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while calling dll to get text height on file " & sFileToImport & ". File
NOT
imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand

'get the data into the database
Try

sqlConn = New SqlConnection("Data Source=" & dataSource &
";Initial Catalog=" & dataBase & ";User Id=" & userName & ";Password=" &
password & ";")
sqlConn.Open()

sqlComm = New SqlCommand("sp_ImportNotices", sqlConn)
sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Value
=
0
sqlComm.Parameters("RETURN_VALUE").Direction =
ParameterDirection.ReturnValue
sqlComm.Parameters.Add("@AdTypeCode", SqlDbType.VarChar,
50).Value = IIf(AdTypeCode = "", DBNull.Value, AdTypeCode)
sqlComm.Parameters.Add("@ImportType", SqlDbType.VarChar,
50).Value = IIf(ImportType = "", DBNull.Value, ImportType)
sqlComm.Parameters.Add("@AttorneyFileNum",
SqlDbType.VarChar,
50).Value = IIf(AttorneyFileNum = "", DBNull.Value, AttorneyFileNum)
sqlComm.Parameters.Add("@AttyOfficeName", SqlDbType.VarChar,
200).Value = IIf(AttyOfficeName = "", DBNull.Value, AttyOfficeName)
sqlComm.Parameters.Add("@MortgagorFirstName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorFirstName = "", DBNull.Value,
MortgagorFirstName)
sqlComm.Parameters.Add("@MortgagorLastName",
SqlDbType.VarChar,
50).Value = IIf(MortgagorLastName = "", DBNull.Value, MortgagorLastName)
sqlComm.Parameters.Add("@PropAddress1", SqlDbType.VarChar,
100).Value = IIf(PropAddress1 = "", DBNull.Value, PropAddress1)
sqlComm.Parameters.Add("@PropAddress2", SqlDbType.VarChar,
100).Value = IIf(PropAddress2 = "", DBNull.Value, PropAddress2)
sqlComm.Parameters.Add("@PropCity", SqlDbType.VarChar,
100).Value = IIf(PropCity = "", DBNull.Value, PropCity)
sqlComm.Parameters.Add("@PropState", SqlDbType.VarChar,
50).Value = IIf(PropState = "", DBNull.Value, PropState)
sqlComm.Parameters.Add("@PropZip", SqlDbType.VarChar,
20).Value
= IIf(PropZip = "", DBNull.Value, PropZip)
sqlComm.Parameters.Add("@FirstPubDate", SqlDbType.VarChar,
50).Value = IIf(FirstPubDate = "", DBNull.Value, FirstPubDate)
sqlComm.Parameters.Add("@NumWeeksToRun", SqlDbType.VarChar,
10).Value = IIf(NumWeeksToRun = "", DBNull.Value, NumWeeksToRun)
sqlComm.Parameters.Add("@LastPubDate", SqlDbType.VarChar,
50).Value = IIf(LastPubDate = "", DBNull.Value, LastPubDate)
sqlComm.Parameters.Add("@County", SqlDbType.VarChar,
50).Value
=
IIf(County = "", DBNull.Value, County)
sqlComm.Parameters.Add("@DateOfSale", SqlDbType.VarChar,
50).Value = IIf(DateOfSale = "", DBNull.Value, DateOfSale)
sqlComm.Parameters.Add("@FullNoticeText",
SqlDbType.Text).Value
= IIf(FullNoticeText = "", DBNull.Value, FullNoticeText)
sqlComm.Parameters.Add("@NoticeTextHeight",
SqlDbType.Decimal,
5).Value = CDbl(NoticeTextHeight)
sqlComm.Parameters.Add("@WholeImportFile",
SqlDbType.Text).Value
= IIf(WholeImportFile = "", DBNull.Value, WholeImportFile)
sqlComm.Parameters.Add("@TextFileName", SqlDbType.VarChar,
500).Value = sFileToImport
sqlComm.Parameters.Add("@ReasonsNotPosted",
SqlDbType.VarChar,
500).Value = DBNull.Value
sqlComm.Parameters("@ReasonsNotPosted").Direction =
ParameterDirection.Output

sqlComm.ExecuteNonQuery()

If sqlComm.Parameters("RETURN_VALUE").Value <0 Then
txtFolderActivity.Text &= "SQL Error " &
sqlComm.Parameters("RETURN_VALUE").Value.ToString & " on file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss")
&
")." & vbCrLf
Exit Sub
End If

Catch ex As SqlException
txtFolderActivity.Text &= "SQL Error " & ex.Message & "
occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport
&
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." &
vbCrLf
Exit Sub
Catch ex As Exception
txtFolderActivity.Text &= "Error " & ex.Message & " occurred
while executing SQL procedure sp_ImportNotices on file " & sFileToImport
&
". File NOT imported (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." &
vbCrLf
Exit Sub
Finally

sqlComm.Connection.Close()
sqlComm.Dispose()
sqlConn.Dispose()
End Try

'if the "ImportedNoticeFiles" folder does not yet exist, create
it
Try
If Not Directory.Exists(textFilesLocation &
"ImportedNoticeFiles") Then
Directory.CreateDirectory(textFilesLocation &
"ImportedNoticeFiles")
'don't go on until the folder has been created
Do Until Directory.Exists(textFilesLocation &
"ImportedNoticeFiles")
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
attempting to create ImportedNoticeFiles folder while importing file " &
sFileToImport & ". File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss")
&
")." & vbCrLf
End Try

'store the finished file in the ImportedNoticeFiles folder for
someone to manually review (delete it first if for some reason it
already
exists)
Try
If File.Exists(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport) Then
File.Delete(textFilesLocation & "ImportedNoticeFiles\" &
sFileToImport)
'Need to wait for the file to be deleted before moving
on.
Do Until Not File.Exists(textFilesLocation &
"ImportedNoticeFiles\" & sFileToImport)
System.Threading.Thread.Sleep(500)
Loop
End If
Catch ex As IOException
txtFolderActivity.Text &= "File " & sFileToImport & " was
imported but IO Error " & ex.Message & " occurred while attempting to
delete
the previously imported file with the same name (" & Format(Now(),
"m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

Try
File.Move(textFilesLocation & sFileToImport,
textFilesLocation
&
"ImportedNoticeFiles\" & sFileToImport)
Catch ex As IOException
txtFolderActivity.Text &= "IO Error " & ex.Message & "
occurred
while attempting to move file " & sFileToImport & " to the
ImportedNoticeFiles folder. File NOT imported (" & Format(Now(), "m/d/yy
hh:mm:ss") & ")." & vbCrLf
Exit Sub
End Try

txtFolderActivity.Text &= "File " & sFileToImport & " has been
imported successfully and has been moved to the ImportedNoticeFiles
subfolder (" & Format(Now(), "m/d/yy hh:mm:ss") & ")." & vbCrLf

End Sub

End Class


Aug 28 '08 #7

P: n/a
Keith G Hicks wrote:
Still unpredictable. It helped but didn't completely solve the
problem. Ugh. :-(
I think you will run into trouble as long as you try to process files within the
FileWatcher event handler.

A better strategy is to use the events to build a ToDo list for yourself, and to
start a timer on say a 0.5 sec interval. When the timer fires, turn it off and
process one file from the list. If there are more entries in the list, turn on
the timer again.

This decouples file processing from notification. Unless you do this, you may
miss some notifications. The file processing should anticipate that it may not
be able to open the file, in which case it simply turns on the timer again, and
tries again after another 0.5 second.

I have a Windows service monitoring an SMTP drop folder, and this kind of
decoupling was essential to get it to work right. Goes like clockwork now.
Aug 28 '08 #8

P: n/a
Thanks for the feedback Steve. In fact that was what I first set up before I
found out about the FileSystemWatcher class. I had set up a routine to look
for text files in the folder (just using the DIR funciton) and then store
the file names in an array. Then I'd loop through the array to get the
contents of each file. I discovered the FileSystemWatcher class before I got
around to putting the timer into the mix so I abandoned my earlier code. I
went to bed last night thinking I would probably have to do something like
what you suggested but was not sure how to handle the problem of files
coming in while processing was happening. Your suggestion about turning off
the timer solves that.

Just so I know I'm clear, are you suggesting using a timer INSTEAD OF the
FileSystemWatcher class altogether or somehow in conjunction with it? If the
former, I know what I need to do. If the later, I'm not entirely sure how
I'd combine the efforts of a timer along with the FileSystemWatcher class.
Do you mean to use the FileSystemWatcher to build the "todo" list and then
turn off the FileSystemWatcher and the timer? I'm a little fuzzy on the
specifics.

Thanks again,

Keith

"Steve Gerrard" <my********@comcast.netwrote in message
news:Af******************************@comcast.com. ..
Keith G Hicks wrote:
Still unpredictable. It helped but didn't completely solve the
problem. Ugh. :-(

I think you will run into trouble as long as you try to process files
within the
FileWatcher event handler.

A better strategy is to use the events to build a ToDo list for yourself,
and to
start a timer on say a 0.5 sec interval. When the timer fires, turn it off
and
process one file from the list. If there are more entries in the list,
turn on
the timer again.

This decouples file processing from notification. Unless you do this, you
may
miss some notifications. The file processing should anticipate that it may
not
be able to open the file, in which case it simply turns on the timer
again, and
tries again after another 0.5 second.

I have a Windows service monitoring an SMTP drop folder, and this kind of
decoupling was essential to get it to work right. Goes like clockwork now.


Aug 28 '08 #9

P: n/a
Keith G Hicks wrote:
>
Just so I know I'm clear, are you suggesting using a timer INSTEAD OF
the FileSystemWatcher class altogether or somehow in conjunction with
it? If the former, I know what I need to do. If the later, I'm not
entirely sure how I'd combine the efforts of a timer along with the
FileSystemWatcher class. Do you mean to use the FileSystemWatcher to
build the "todo" list and then turn off the FileSystemWatcher and the
timer? I'm a little fuzzy on the specifics.
In my case, I process one file at a time, set the timer, look for a file,
process it, set the timer, until there are no files. Then I turn on the
FileSystemWatcher. At that point the service goes idle; it gets woken up when a
file shows up in the folder. When that happens, the FileSystemWatcher gets
turned off, the timer gets turned on, and the timer/file processing sequence
repeats until there are no more files again.

You may want to leave the FileSystemWatcher on, since you need to know more
about what happened (I am only looking for new files). I would have the
FileSystemWatcher event put things in a list, and a timer run some code to take
them out and do them one at a time. When there is nothing to do, turn off the
timer, and wait for the FileSystemWatcher to wake things up again. If want (or
need) to be really slick, you could do all the actual file processing in a
background worker thread, so the main thread can always be ready to process
FileSystemWatcher events.
Aug 29 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.