首先,这个问题已经被解决了
method
我的设想。
我有一个程序,将检查一个完整的excel表,并检查其内容,如果有任何错误,是在excel表上发现,一个程序将火灾和发送电子邮件给有关人员。
在测试过程中,该程序运行良好。每个错误只发送一封邮件。如果该表有5个错误,系统将发送5封电子邮件关于上述错误。这是至关重要的,因为这里的错误是高度优先的,应该总是完美的。
然后我添加了一个代码,以了解详细信息。不过,在测试期间,这里没有发送电子邮件。我发现我的程序是不停地发送电子邮件。如果我没有删除排队的邮件,可能会发送4000多封邮件。好在我只是在用测试服务器。现在,我正在检查可能发生了什么导致了这种现象。我能用什么对策?
我必须为这个发布一个很长的代码。
问我你是否需要其他信息。
'My Main procedure
Sub CheckDataOfExcel(ByVal locs As String)
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim location As String
Dim isData As Boolean = False
Dim m_CountTo As Integer = 0
'Progress Bar STEP
Dim PBStep As Integer
filename = ""
globalloc = ""
'Gets Files from Folders
Dim counting As Integer = 0
Dim csvcount As String() = Directory.GetFiles(locs, "*.xls")
Dim ToUploadCSV As String
For counting = 0 To csvcount.Count - 1
CheckListofCSV.Clear()
filename = ""
location = csvcount(counting).ToString
globalloc = location
ToUploadCSV = Path.GetFileName(location).ToString
Dim ListLines As New List(Of String)
Dim ListLinesNEW As New List(Of String)
'Read the file
'If data is retrieved
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open(location)
xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
'Use this code to read the data from EXCEL
'Check if file is already uploaded
If ShowExclUpld(xlWorkSheet.Cells(62, 2).value, xlWorkSheet.Cells(3, 2).value, xlWorkSheet.Cells(4, 2).value) <> "" Then
MsgBox("A file named " & ShowExclUpld(xlWorkSheet.Cells(62, 2).value, xlWorkSheet.Cells(3, 2).value, xlWorkSheet.Cells(4, 2).value) & " was uploaded containing similar data of " & ToUploadCSV & " please check.")
Else
'ROWS
Dim i As Integer = 5
'COLUMNS
Dim col As Integer = 2
'SAMPLING COUNT FROM EXCEL
Dim SampQTYXcel As Integer = 0
'below code to check data only
Do While Len(xlWorkSheet.Cells(i, col).value) > 0
'**DATA DETAILS**
'Loop starts here
'Product Property Sub Header
For i = 5 To 10
'Check Dimension Here
'If Spec is correct, do nothing.
'I think I should just return the list of all the PPF,Part number,Dimension that
'Create procedure to check SPecs? then get the file name and file location
'along with the PPFNo, PartNo, and Dimension name
checkSpecData(xlWorkSheet.Cells(3, 2).value, xlWorkSheet.Cells(5, col).value, xlWorkSheet.Cells(62, 2).value, _
ToUploadCSV, location, xlWorkSheet.Cells(6, col).value, xlWorkSheet.Cells(7, col).value, xlWorkSheet.Cells(8, col).value, i)
'Now I should get a flag to tell the system not to upload this filename anymore...
Next
'TASK:Create POKAYOKE for Sample Size Checking
'1) Encoded Sample Size my be within the upper and lower limit specification
'2) Encoded Sample Size encoded must be equal to the Sample Size requirement encode from the database
'Use these data for POKAYOKE regarding Specification upper and lower limits
Dim XLUprLmt As Decimal = xlWorkSheet.Cells(7, col).value
Dim XLLwrLmt As Decimal = xlWorkSheet.Cells(8, col).value
If ExlErrorFlag = ToUploadCSV Then
'Sample Data 10 onwards
Do While Len(xlWorkSheet.Cells(i, col).value) > 0
SampQTYXcel = SampQTYXcel + 1
If xlWorkSheet.Cells(i, col).value > XLUprLmt Or xlWorkSheet.Cells(i, col).value < XLLwrLmt Then
MsgBox(createEmailMsgForSamplErr(SampQTYXcel, xlWorkSheet.Cells(i, col).value))
End If
i = i + 1
Loop
'Excel data has more data than Database QTY then REPORT
If SampQTYXcel > SamplQTY Then
MsgBox("ERROR")
End If
'Add 1 to move to next column
col = col + 1
i = 5
SampQTYXcel = 0
End If
Loop
xlApp.DisplayAlerts = False
xlWorkBook.Save()
End If
releaseObject(xlWorkSheet.UsedRange)
releaseObject(xlWorkSheet)
xlWorkBook.Close()
releaseObject(xlWorkBook)
xlApp.Quit()
releaseObject(xlApp)
xlApp = Nothing
xlWorkBook = Nothing
xlWorkSheet = Nothing
Cleanup()
Next
End Sub
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter procedure [dbo].[checkData]
@PartNo as nvarchar(20),
@DimName as nvarchar(20),
@PPFNo as nvarchar(20),
@FileName as nvarchar(20),
@FileLocation as nvarchar(150),
@SpectoTest as nvarchar(20),
@UprLmtToTest as nvarchar(10),
@LwrLmtToTest as nvarchar(10) ,
@flag as integer
as
begin
Declare @TheEmailBody as nvarchar(max)
if @flag = 5
begin
if cast(@SpectoTest as decimal(18,4)) <> (select cast(specification as decimal(18,4)) from DimensionMaster where PartNo = @PartNo and DimensionName = @DimName)
begin
set @TheEmailBody = '
---------------------------------------------------------------------------------------------------------------------------------
Part Number : '+@PartNo+ '
Dimension Name: '+@DimName+ '
PPF Number : '+@PPFNo+ '
File Name : '+@FileName+ '
File Location : '+@FileLocation+'
Error: Different Specification
Specification from EXCEL: '+@SpectoTest+'
Specification from DB: '+ (select specification from DimensionMaster where PartNo = @PartNo and DimensionName = @DimName) +'
This is a system generated email
---------------------------------------------------------------------------------------------------------------------------------
'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SendSQLMail',
@recipients = 'someone@somewhere.com',
@subject = 'TEST',
@body = @TheEmailBody,
@body_format = 'text'
select @FileName
end
end
else if @flag = 9
begin
if cast(@UprLmtToTest as decimal(18,4)) <> (select cast(UpperLimit as decimal(18,4)) + cast(specification as decimal(18,4)) from DimensionMaster where PartNo = @PartNo and DimensionName = @DimName)
begin
set @TheEmailBody = '
---------------------------------------------------------------------------------------------------------------------------------
Part Number : '+@PartNo+ '
Dimension Name: '+@DimName+ '
PPF Number : '+@PPFNo+ '
File Name : '+@FileName+ '
File Location : '+@FileLocation+'
Error: Different UpperLimit
UpperLimit from EXCEL: '+@UprLmtToTest+'
UpperLimit from DB: '+ cast((select cast(UpperLimit as decimal(18,3)) + cast(specification as decimal(18,3)) from DimensionMaster where PartNo = @PartNo and DimensionName = @DimName) as nvarchar(max)) +'
This is a system generated email
---------------------------------------------------------------------------------------------------------------------------------
'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SendSQLMail',
@recipients = 'someone@somewhere.com',
@subject = 'TEST',
@body = @TheEmailBody ,
@body_format = 'text'
select @FileName
end
end
else if @flag = 10
begin
if cast(@LwrLmtToTest as decimal(18,4)) <> (select cast(Specification as decimal(18,4)) - cast(lowerlimit as decimal(18,4)) from DimensionMaster where PartNo = @PartNo and DimensionName = @DimName)
begin
set @TheEmailBody = '
---------------------------------------------------------------------------------------------------------------------------------
Part Number : '+@PartNo+ '
Dimension Name: '+@DimName+ '
PPF Number : '+@PPFNo+ '
File Name : '+@FileName+ '
File Location : '+@FileLocation+'
Error: Different LowerLimit
LowerLimit from EXCEL: '+@LwrLmtToTest+'
LowerLimit from DB: '+ cast((select cast(Specification as decimal(18,3)) - cast(lowerlimit as decimal(18,3)) from DimensionMaster where PartNo = @PartNo and DimensionName = @DimName) as nvarchar(max)) +'
This is a system generated email
---------------------------------------------------------------------------------------------------------------------------------
'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SendSQLMail',
@recipients = 'someone@somewhere.com',
@subject = 'TEST',
@body = @TheEmailBody,
@body_format = 'text'
select @FileName
end
end
end
