Member 12609377 Ответов: 2

Ошибка множественного текстового файла "файл уже открыт" (VB6)

Вот мой код, я попытался распечатать несколько текстовых файлов, соответствующих месяцам в базе данных ( например, если 1-> Январь.TXT, если 2-> февраля.TXT и так далее.. ). В таблице базы данных есть 3 строки, где выходными данными должны быть 3 файла с данными, один-январь.TXT, февраль.TXT и March.TXT.появилась ошибка "файл уже открыт".Он должен был напечатать 3 текстовых файла с данными, все 3 напечатаны, но только первый имеет данные, остальные два пусты, с ошибкой "файл уже открыт". Пожалуйста, помогите, спасибо.


vSQL = vSQL & " GROUP BY SOA_COMPNO, SOA_FAC, EEP_NAME, EEP_IC, EEP_SOCSONO, dtjoin, dtresign ORDER BY SOA_COMPNO, SOA_FAC, EEP_NAME "
    
    uSQL = "SELECT soa_month FROM DMF_SocsoArrears WHERE SOA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
        "SOA_PAYYEAR = " & Year(dtpSocsoDate.Value) & " AND (SOA_SOAMTEE+SOA_SOAMTER) > 0 "
    
    RS1.Open vSQL, Conn, adOpenStatic, adLockReadOnly
    RS2.Open uSQL, Conn, adOpenStatic, adLockReadOnly
    
    While Not RS2.EOF
    Select Case RS2!soa_month
        Case "1"
            tFileName = "January.TXT"
        Case "2"
            tFileName = "February.TXT"
        Case "3"
           tFileName = "March.TXT"
        Case "4"
           tFileName = "April.TXT"
        Case "5"
           tFileName = "May.TXT"
        Case "6"
           tFileName = "June.TXT"
        Case "7"
           tFileName = "July.TXT"
        Case "8"
           tFileName = "August.TXT"
        Case "9"
           tFileName = "September.TXT"
        Case "10"
           tFileName = "October.TXT"
        Case "11"
           tFileName = "November.TXT"
        Case "12"
           tFileName = "December.TXT"
    End Select
        
            intFileHandle2 = FreeFile
    
            Dim sPath As String
            sPath = txtSocsoLoc.Text & tFileName
            Open sPath For Output As #intFileHandle2
            If RS1.RecordCount > 0 Then
                While Not RS1.EOF
                    strJoin = IIf("" & RS1!dtjoin = "", "", Format("" & RS1!dtjoin, "ddMMyyyy"))
                    strResign = IIf("" & RS1!dtresign = "", "", Format("" & RS1!dtresign, "ddMMyyyy"))
                    
                    strJoinResign = Left(strJoin & Space(8), 8)
                    If strResign <> "" Then strJoinResign = strResign
                    
                    strStatus = IIf(strJoin <> "", "B", " ")
                    strStatus = IIf(strResign <> "", "H", strStatus)
                        
                
                    StrInput2 = ""
                    StrInput2 = Left(RS1!KOD & Space(12), 12) & Space(20) & Left(RS1!eep_ic & Space(12), 12) & Left(RS1!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS1!amount_socso, 14) & strJoinResign & strStatus
                    
                    
                    Print #intFileHandle2, StrInput2
                    
                    RS1.MoveNext
                Wend
            End If
            
            RS2.MoveNext
    Wend
    

    Close #intFileHandle2
    Set RS1 = Nothing
    Set RS2 = Nothing
    Call SDefault(Me)

    MsgBox "Monthly Socso Arrear Files have successfully saved.", vbInformation, Me.Caption


Что я уже пробовал:

I have tried to change the position of the open, and "close intfile"at many places, non worked.

2 Ответов

Рейтинг:
2

Member 12609377

Вот метод, который сработал


Dim RS1 As New ADODB.Recordset
Dim RS2 As New ADODB.Recordset
Dim RS3 As New ADODB.Recordset
Dim RS4 As New ADODB.Recordset
Dim RS5 As New ADODB.Recordset
Dim RS6 As New ADODB.Recordset
Dim RS7 As New ADODB.Recordset
Dim RS8 As New ADODB.Recordset
Dim RS9 As New ADODB.Recordset
Dim RS10 As New ADODB.Recordset
Dim RS11 As New ADODB.Recordset
Dim RS12 As New ADODB.Recordset
Dim RS13 As New ADODB.Recordset
Dim intFileHandle2 As Integer
Dim StrInput2 As String
Dim tFileName As String
Dim uSQL As String
Dim vSQL As String
Dim janSQL As String
Dim febSQL As String
Dim marSQL As String
Dim aprSQL As String
Dim maySQL As String
Dim junSQL As String
Dim julSQL As String
Dim augSQL As String
Dim sepSQL As String
Dim octSQL As String
Dim novSQL As String
Dim decSQL As String
Dim sPath As String
'Dim i As Integer

    vSQL = "SELECT ISNULL((select ead_Number from DMF_Address where ead_CompanyNo = eia_compno and ead_factory = eia_fac and ead_AddrID = '" & sAddr & "'), '') AS KOD, " & _
        "CAST((EIA_EISAMTER+EIA_EISAMTEE) * 100 AS INT) AS AMOUNT_EIS, " & _
        "CAST(EEP_NAME AS NVARCHAR(45)) AS EEP_NAME, " & _
        "CAST(EEP_IC AS NVARCHAR(12)) AS EEP_IC, " & _
        "CAST(EEP_EISNO AS NVARCHAR(9)) AS EEP_EISNO, dtjoin, dtresign " & _
        "FROM DMF_EISArrears (NOLOCK) RIGHT JOIN " & _
        "(SELECT EEP_SECURITYLVL, EEP_COMPANY, EEP_TYPE, EEP_NO, EEP_NAME, EEP_FACTORY, CASE WHEN EEP_EISIC = 0 THEN (CASE WHEN LEN(EEP_OLDIC) = 0 THEN '' WHEN LEFT(EEP_OLDIC,1) <> 'A' THEN 'A' + EEP_OLDIC ELSE EEP_OLDIC END) " & _
        "WHEN EEP_EISIC = 1 THEN REPLACE(EEP_NEWIC,'-','') " & _
        "WHEN EEP_EISIC = 2 THEN EEP_PASSPORT " & _
        "WHEN EEP_EISIC = 3 THEN EEP_PERMIT END " & _
        "AS EEP_IC, EEP_EISNO, CASE WHEN MONTH(eep_dtjoin)=" & Month(dtpSocsoDate.Value) & " AND YEAR(eep_dtjoin)=" & Year(dtpSocsoDate.Value) & " THEN eep_dtjoin END AS dtjoin, " & _
        "CASE WHEN MONTH(eep_dtresign)=" & Month(dtpSocsoDate.Value) & " AND YEAR(eep_dtresign)=" & Year(dtpSocsoDate.Value) & " THEN eep_dtresign END AS dtresign " & _
        "FROM DMF_EMPLOYEE (NOLOCK) WHERE EEP_EIS = 'Y') AS EMPLOYEE " & _
        "ON (EIA_EMPNO = EEP_NO) WHERE EIA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
        "EIA_PAYYEAR = " & Year(dtpSocsoDate.Value) & " "

     janSQL = vSQL & " AND " & " eia_month ='1' "
     febSQL = vSQL & " AND " & " eia_month = '2'  "
     marSQL = vSQL & " AND " & " eia_month = '3'  "
     aprSQL = vSQL & " AND " & " eia_month = '4'  "
     maySQL = vSQL & " AND " & " eia_month = '5'  "
     junSQL = vSQL & " AND " & " eia_month = '6'  "
     julSQL = vSQL & " AND " & " eia_month = '7'  "
     augSQL = vSQL & " AND " & " eia_month = '8'  "
     sepSQL = vSQL & " AND " & " eia_month = '9'  "
     octSQL = vSQL & " AND " & " eia_month = '10' "
     novSQL = vSQL & " AND " & " eia_month = '11' "
     decSQL = vSQL & " AND " & " eia_month = '12' "
    'If sCond <> "" Then
    '    vSQL = vSQL & sCond
    'End If
'    ssql = ssql & " ORDER BY EEP_NAME "
    'vSQL = vSQL & " GROUP BY SOA_COMPNO, SOA_FAC, EEP_NAME, EEP_IC, EEP_SOCSONO, dtjoin, dtresign ORDER BY SOA_COMPNO, SOA_FAC, EEP_NAME "
    
    uSQL = "SELECT eia_month FROM DMF_EISArrears WHERE EIA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
        "EIA_PAYYEAR = " & Year(dtpSocsoDate.Value) & ""
        
        
    RS1.Open janSQL, Conn, adOpenStatic, adLockReadOnly
    RS2.Open febSQL, Conn, adOpenStatic, adLockReadOnly
    RS3.Open marSQL, Conn, adOpenStatic, adLockReadOnly
    RS4.Open aprSQL, Conn, adOpenStatic, adLockReadOnly
    RS5.Open maySQL, Conn, adOpenStatic, adLockReadOnly
    RS6.Open junSQL, Conn, adOpenStatic, adLockReadOnly
    RS7.Open julSQL, Conn, adOpenStatic, adLockReadOnly
    RS8.Open augSQL, Conn, adOpenStatic, adLockReadOnly
    RS9.Open sepSQL, Conn, adOpenStatic, adLockReadOnly
    RS10.Open octSQL, Conn, adOpenStatic, adLockReadOnly
    RS11.Open novSQL, Conn, adOpenStatic, adLockReadOnly
    RS12.Open decSQL, Conn, adOpenStatic, adLockReadOnly
    RS13.Open uSQL, Conn, adOpenStatic, adLockReadOnly
    
    If RS13.RecordCount > 0 Then
    While Not RS13.EOF
    
    
            intFileHandle2 = FreeFile
            sPath = txtSocsoLoc.Text & tFileName
            If RS13!EIA_MONTH = 1 Then
            tFileName = "BRG8A_EIS(Januari).TXT"
            intFileHandle2 = FreeFile
            sPath = txtSocsoLoc.Text & tFileName
            If RS1.RecordCount > 0 Then
            Open sPath For Append Shared As #intFileHandle2
                While Not RS1.EOF
                    strJoin = IIf("" & RS1!dtjoin = "", "", Format("" & RS1!dtjoin, "ddMMyyyy"))
                    strResign = IIf("" & RS1!dtresign = "", "", Format("" & RS1!dtresign, "ddMMyyyy"))
                    
                    strJoinResign = Left(strJoin & Space(8), 8)
                    If strResign <> "" Then strJoinResign = strResign
                    
                    strStatus = IIf(strJoin <> "", "B", " ")
                    strStatus = IIf(strResign <> "", "H", strStatus)
                        
                    StrInput2 = ""
                    StrInput2 = Left(RS1!KOD & Space(12), 12) & Space(20) & Left(RS1!eep_ic & Space(12), 12) & Left(RS1!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS1!amount_eis, 14) & strJoinResign & strStatus
                    'Open sPath For Append Shared As #intFileHandle2
                    Print #intFileHandle2, StrInput2
                    'Close #intFileHandle2
                    StrInput2 = ""
                    RS1.MoveNext
                Wend
                'RS1.Close
           Close #intFileHandle2
           End If
           End If
              
           If RS13!EIA_MONTH = 2 Then
            tFileName = "BRG8A_EIS(Febuari).TXT"
            intFileHandle2 = FreeFile
            sPath = txtSocsoLoc.Text & tFileName
           If RS2.RecordCount > 0 Then
            Open sPath For Append Shared As #intFileHandle2
                While Not RS2.EOF
                    strJoin = IIf("" & RS2!dtjoin = "", "", Format("" & RS2!dtjoin, "ddMMyyyy"))
                    strResign = IIf("" & RS2!dtresign = "", "", Format("" & RS2!dtresign, "ddMMyyyy"))
                    
                    strJoinResign = Left(strJoin & Space(8), 8)
                    If strResign <> "" Then strJoinResign = strResign
                    
                    strStatus = IIf(strJoin <> "", "B", " ")
                    strStatus = IIf(strResign <> "", "H", strStatus)
                        
                    StrInput2 = ""
                    StrInput2 = Left(RS2!KOD & Space(12), 12) & Space(20) & Left(RS2!eep_ic & Space(12), 12) & Left(RS2!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS2!amount_eis, 14) & strJoinResign & strStatus
                    'Open sPath For Append Shared As #intFileHandle2
                    Print #intFileHandle2, StrInput2
                    'Close #intFileHandle2
                    StrInput2 = ""
                    RS2.MoveNext
                Wend
                'RS1.Close
           Close #intFileHandle2
           End If
           End If
            If RS13!EIA_MONTH = 3 Then
            tFileName = "BRG8A_EIS(Mac).TXT"
            intFileHandle2 = FreeFile
            sPath = txtSocsoLoc.Text & tFileName
           If RS3.RecordCount > 0 Then
            Open sPath For Append Shared As #intFileHandle2
                While Not RS3.EOF
                    strJoin = IIf("" & RS3!dtjoin = "", "", Format("" & RS3!dtjoin, "ddMMyyyy"))
                    strResign = IIf("" & RS3!dtresign = "", "", Format("" & RS3!dtresign, "ddMMyyyy"))
                    
                    strJoinResign = Left(strJoin & Space(8), 8)
                    If strResign <> "" Then strJoinResign = strResign
                    
                    strStatus = IIf(strJoin <> "", "B", " ")
                    strStatus = IIf(strResign <> "", "H", strStatus)
                        
                    StrInput2 = ""
                    StrInput2 = Left(RS3!KOD & Space(12), 12) & Space(20) & Left(RS3!eep_ic & Space(12), 12) & Left(RS3!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS3!amount_eis, 14) & strJoinResign & strStatus
                    'Open sPath For Append Shared As #intFileHandle2
                    Print #intFileHandle2, StrInput2
                    'Close #intFileHandle2
                    StrInput2 = ""
                    RS3.MoveNext
                Wend
                'RS1.Close
           Close #intFileHandle2
           End If
           End If
           If RS13!EIA_MONTH = 4 Then
           tFileName = "BRG8A_EIS(April).TXT"
           intFileHandle2 = FreeFile
            sPath = txtSocsoLoc.Text & tFileName
           If RS4.RecordCount > 0 Then
            Open sPath For Append Shared As #intFileHandle2
                While Not RS4.EOF
                    strJoin = IIf("" & RS4!dtjoin = "", "", Format("" & RS4!dtjoin, "ddMMyyyy"))
                    strResign = IIf("" & RS4!dtresign = "", "", Format("" & RS4!dtresign, "ddMMyyyy"))
                    
                    strJoinResign = Left(strJoin & Space(8), 8)
                    If strResign <> "" Then strJ
Рейтинг:
0

Patrice T

Отступы здесь предназначены для того, чтобы помочь понять структуру вашего кода, поэтому тщательное использование является обязательным:

vSQL = vSQL & " GROUP BY SOA_COMPNO, SOA_FAC, EEP_NAME, EEP_IC, EEP_SOCSONO, dtjoin, dtresign ORDER BY SOA_COMPNO, SOA_FAC, EEP_NAME "

uSQL = "SELECT soa_month FROM DMF_SocsoArrears WHERE SOA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
"SOA_PAYYEAR = " & Year(dtpSocsoDate.Value) & " AND (SOA_SOAMTEE+SOA_SOAMTER) > 0 "

RS1.Open vSQL, Conn, adOpenStatic, adLockReadOnly
RS2.Open uSQL, Conn, adOpenStatic, adLockReadOnly

While Not RS2.EOF
  Select Case RS2!soa_month
    Case "1"
    tFileName = "January.TXT"
    Case "2"
    tFileName = "February.TXT"
    Case "3"
    tFileName = "March.TXT"
    Case "4"
    tFileName = "April.TXT"
    Case "5"
    tFileName = "May.TXT"
    Case "6"
    tFileName = "June.TXT"
    Case "7"
    tFileName = "July.TXT"
    Case "8"
    tFileName = "August.TXT"
    Case "9"
    tFileName = "September.TXT"
    Case "10"
    tFileName = "October.TXT"
    Case "11"
    tFileName = "November.TXT"
    Case "12"
    tFileName = "December.TXT"
  End Select

  intFileHandle2 = FreeFile

  Dim sPath As String
  sPath = txtSocsoLoc.Text & tFileName
  Open sPath For Output As #intFileHandle2
  If RS1.RecordCount > 0 Then
    While Not RS1.EOF
      strJoin = IIf("" & RS1!dtjoin = "", "", Format("" & RS1!dtjoin, "ddMMyyyy"))
      strResign = IIf("" & RS1!dtresign = "", "", Format("" & RS1!dtresign, "ddMMyyyy"))

      strJoinResign = Left(strJoin & Space(8), 8)
      If strResign <> "" Then strJoinResign = strResign

      strStatus = IIf(strJoin <> "", "B", " ")
      strStatus = IIf(strResign <> "", "H", strStatus)


      StrInput2 = ""
      StrInput2 = Left(RS1!KOD & Space(12), 12) & Space(20) & Left(RS1!eep_ic & Space(12), 12) & Left(RS1!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS1!amount_socso, 14) & strJoinResign & strStatus


      Print #intFileHandle2, StrInput2

      RS1.MoveNext
    Wend
  End If

  Close #intFileHandle2

  RS2.MoveNext
Wend


Close #intFileHandle2
Set RS1 = Nothing
Set RS2 = Nothing
Call SDefault(Me)

MsgBox "Monthly Socso Arrear Files have successfully saved.", vbInformation, Me.Caption

Обратите внимание на изменение, которое я сделал в конце команды "закрыть".
"Открытое" находится в цикле, "закрытое" тоже должно быть.

Member 12609377

Привет! большое спасибо за ответ, я уже пробовал это, и я попробовал еще раз только сейчас, все 3 выхода пусты, каким-то образом "close #intFileHandle2" мешает записывать данные в файл. Что-то еще где-то не так...:'(
Пожалуйста, помогите, спасибо.

Patrice T

Используйте отладчик, чтобы увидеть, что именно сделано. Убедитесь, что ваш код действительно пытается что-то напечатать.

Member 12609377

Спасибо! Я нашел проблему, все данные вставлены в один файл, следовательно, остальные файлы оказались пустыми, следовательно, пришлось переключиться на другой метод.