Hi expert,
Thanks Cor. I thought about using datareader this morning, so I
wrote the following code by using datareader, by using
While dtrBatchName.Read().....End While
However, I got error: Invalid attempt to read when reader is closed on
"While dtrBatchName.Read() " statement while it's attempting to read
the 2nd filename.
How can I make sure the datareader is opened in this while loop?
Public Function ProcessRET(ByRef strFilepath As String)
Dim BatchNum As Int32
Dim sEmpID As String
Dim EFNum As Int64
Dim nErrorCode As Int32
Dim EpayConnection As New SqlConnection
Dim GPConnection As New SqlConnection
Dim cmdBatchName As New SqlCommand
Dim dtrBatchName As SqlDataReader
Dim strRetFileName As String
EpayConnection.ConnectionString = strEpayDBConn
With cmdBatchName
.Connection = EpayConnection
.CommandText = "SELECT File_Name FROM EPay_Batch_Table
Where File_Status=2"
End With
EpayConnection.Open()
dtrBatchName = cmdBatchName.ExecuteReader()
While dtrBatchName.Read()
Try
strRetFileName = dtrBatchName("File_Name")
Catch ex As Exception
MsgBox("No return file while all file status <>
2", MsgBoxStyle.Information)
End Try
Dim ReturnFileName As String =
strRetFileName.Replace(".in", ".RET")
EpayConnection.Close()
Dim flRetBatch As File
Dim stRetBatch As FileStream
Dim smRetBatch As StreamReader
'Open the text file into a stream reader
Dim sr As New StreamReader(strFilepath &
ReturnFileName)
Dim sRetLine As String
Dim sBatchNum As String
Dim sRecordType As String
Dim sEfundNum As String
Try
Dim lines() As String
Dim contents As String
Dim lineNum As Integer = 0
sRetLine = sr.ReadLine
'Return file heading record
If Left$(sRetLine, 1) = "H" Then
sBatchNum = (Mid$(sRetLine, 73, 13))
BatchNum = Convert.ToInt32(sBatchNum)
End If
While sr.Peek > -1
sRetLine = sr.ReadLine
'Return file enrollment/maintenance detail
records
If Left$(sRetLine, 1).ToString = "D" Or
Left$(sRetLine, 1).ToString = "R" Then
sEmpID = LTrim(Mid$(sRetLine, 2, 10))
EpayConnection.Open()
'Read lines and update transaction table
Select Case Left$(sRetLine, 1).ToString
Case "D"
Try
sEfundNum = (Mid$(sRetLine, 42,
13))
EFNum =
Convert.ToInt64(sEfundNum)
Dim strSQL As String
strSQL = "UPDATE
Epay_Transaction_Table SET EAcct_Num = " & EFNum & "
,Status_Ind=3, Enroll_Ind=3 , Error_Code = NULL, Error_Description =
NULL where EmployID='" & sEmpID & "' and Batch_Num= " &
BatchNum
Dim objComd = New
SqlCommand(strSQL, EpayConnection)
objComd.ExecuteNonquery()
EpayConnection.Close()
Catch err As Exception
MsgBox(err.Message)
End
End Try
Case "R"
Try
nErrorCode =
Convert.ToInt32(Mid$(sRetLine, 197, 3))
Dim strSQL As String
strSQL = "UPDATE
Epay_Transaction_Table SET Enroll_Ind=2, Status_Ind=2, Error_Code="
& nErrorCode & ", Error_Description=(SELECT Error_Description
FROM EPay_Error_Table WHERE error_Code=" & nErrorCode & ")
where EmployID='" & sEmpID & "' and Batch_Num=" &
BatchNum
Dim objComd = New
SqlCommand(strSQL, EpayConnection)
objComd.ExecuteNonquery()
EpayConnection.Close()
Catch err As Exception
MsgBox(err.Message)
End
End Try
End Select
End If
'Return file funding detail records
If Left$(sRetLine, 1) = "F" Then
End If
'Return file Trailer record, compare total return
records with total transaction records in the table
If Left$(sRetLine, 1) = "T" Then
Dim TotAcctOpened As Int32
Dim TotAcctRejected As Int32
Dim TotRetRecords As Int32
TotAcctOpened = Convert.ToInt32(Mid$(sRetLine,
2, 5))
TotAcctRejected =
Convert.ToInt32(Mid$(sRetLine, 7, 5))
TotRetRecords = TotAcctOpened +
TotAcctRejected
EpayConnection.Open()
Dim cmdTotRecord As New SqlCommand
Dim dtrTotRecord As SqlDataReader
With cmdTotRecord
.Connection = EpayConnection
.CommandText = "SELECT COUNT(*)As
TotBatchRecord FROM Epay_Transaction_Table WHERE Batch_Num =" &
BatchNum
End With
dtrTotRecord = cmdTotRecord.ExecuteReader()
dtrTotRecord.Read()
Dim strTotRecord As Int32
strTotRecord = dtrTotRecord("TotBatchRecord")
If TotRetRecords <> strTotRecord Then
MsgBox("Return file total records does not
match total records in database")
End If
EpayConnection.Close()
End If
End While
Catch err As Exception
MsgBox("File retrieve fail", err.Message)
End Try
End While
*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*