Counter Confusion

[callydata]

What is wrong with my counter code. If there are 8 records to read, it counts 7 and just drops the first one. And it always drops the record with the lowest ID number assigned to it. It doesn’t make any sense to me. Thanks.

fillstring.Value = "9999999999"
        fillstring2.Value = "9999"
        fullfill.Value = fillstring.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring2.Value
        fullfill3.Value = fullfill.Value & fillstring.Value & fillstring.Value & fillstring.Value & fillstring2.Value
        hashcount = 0
 
        achtotal = 0
        counter = 1
        zst.Value = "000000000000000"
        'Date enter box, this way any date could be accessed
        achdate = CDate(InputBox("Enter Effective Date to Generate ACH file for, MM/DD/YYYY"))
        'Call the Read method of the DataReader to loop thru the records while the connection is open
        Dim oFile As System.IO.File
        Dim oWrite As System.IO.StreamWriter
        'First Loop
        While drAchGen.Read
            oWrite = oFile.CreateText("C:\" & achdate.ToString("yyyyMMdd") & ".ach")
 
   
            Do While drAchGen.Read
 
                'ENTRY DETAIL HEADER - if its greened out, its probably part of the copy to another database system
                '1
                ed1.Value = "6"
                '2 This is like the strName, strBankers, strAmounts anything with achm is a field deal     USed to have .Value at the very end
                ed2.Value = Trim(drAchGen("ACHCode"))
                '3 & 4  USed to have .Value at the very end
                ed34.Value = drAchGen("RoutingNumber")
                ed4 = ""
                '5  USed to have .Value at the very end
                ed5 = drAchGen("AccountNumber")
                'Third Loop
                Do While Len(ed5) > 0
                    If IsNumeric(Microsoft.VisualBasic.Strings.Left(Trim(ed5), 1)) Then
                        ed56 = Str(CDbl(Microsoft.VisualBasic.Strings.Left(Trim(ed5), 1)))
                        ed4 = Trim(ed4) & Trim(ed56)
                        ed5 = Microsoft.VisualBasic.Strings.Right(Trim(ed5), Len(Trim(ed5)) - 1)
                    Else
                        ed5 = Microsoft.VisualBasic.Strings.Right(Trim(ed5), Len(Trim(ed5)) - 1)
                    End If
                    System.Diagnostics.Debug.WriteLine(ed5)
                    System.Diagnostics.Debug.WriteLine(ed56)
                    System.Diagnostics.Debug.WriteLine(ed4)
                    'End Third Loop
                Loop
                ed55.Value = ed4
                '6 this is the amount, there is an issue of 5 possible values for this. USed to have .Value before the *
                Dim dates = Date.Now
                If drAchGen("Starting") <= dates And drAchGen("Ending") >= dates = True Then
                    ed6.Value = Str(System.Math.Round(drAchGen("Amount") * 100))
                    ed6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(ed6.Value))) & Trim(ed6.Value)
                ElseIf drAchGen("Starting1") <= dates And drAchGen("Ending1") >= dates = True Then
                    ed6.Value = Str(System.Math.Round(drAchGen("Amount1") * 100))
                    ed6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(ed6.Value))) & Trim(ed6.Value)
                ElseIf drAchGen("Starting2") <= dates And drAchGen("Ending2") >= dates = True Then
                    ed6.Value = Str(System.Math.Round(drAchGen("Amount2") * 100))
                    ed6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(ed6.Value))) & Trim(ed6.Value)
                ElseIf drAchGen("Starting3") <= dates And drAchGen("Ending3") >= dates = True Then
                    ed6.Value = Str(System.Math.Round(drAchGen("Amount3") * 100))
                    ed6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(ed6.Value))) & Trim(ed6.Value)
                ElseIf drAchGen("Starting4") <= dates And drAchGen("Ending4") >= dates = True Then
                    ed6.Value = Str(System.Math.Round(drAchGen("Amount4") * 100))
                    ed6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(ed6.Value))) & Trim(ed6.Value)
                End If
                '7 Used to be Str(achm.Fields("mortgagenumber").Value)
                ed7.Value = "NMC00001       "
                '8 USed to have .Value at the ends of )
                ed8.Value = drAchGen("LastName") + "," + " " + drAchGen("FirstName")
                '9
                ed9.Value = "  "
                '10
                ed10.Value = "0"
                '11 Trace number is "07500005" and a counter type to keep track of entries
                ed11.Value = "07500005" & Microsoft.VisualBasic.Strings.Left(zst.Value, 7 - Len(Trim(Str(counter)))) & Trim(Str(counter))
                entdetheadr.Value = ed1.Value & ed2.Value & ed34.Value & ed55.Value & ed6.Value & ed7.Value & ed8.Value & ed9.Value & ed10.Value & ed11.Value
                oWrite.WriteLine(entdetheadr.Value)
                hashcount = hashcount + Val(Microsoft.VisualBasic.Strings.Left(drAchGen("RoutingNumber"), 8))
                achtotal = achtotal + (drAchGen("Amount") * 100)
                counter = counter + 1
                'End Second Loop
            Loop
 
            'NMB ENTRY DETAIL HEADER
            '1
            ed1.Value = "6"
            '2
            ed2.Value = "22"
            '3 & 4
            ed34.Value = "075000051"
            ed4 = ""
            '5
            ed5 = "22332662"
            ed55.Value = ed5
            '6
            ed6.Value = Str(System.Math.Round(achtotal, 0))
            ed6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(ed6.Value))) & Trim(ed6.Value)
            '7
            ed7.Value = "NMB SERVICES       "
            '8
            ed8.Value = "DRNMB SERVICES"
            '9
            ed9.Value = "  "
            '10
            ed10.Value = "0"
            '11
            ed11.Value = "07500005" & Microsoft.VisualBasic.Strings.Left(zst.Value, 7 - Len(Trim(Str(counter)))) & Trim(Str(counter))
            entdetheadr.Value = ed1.Value & ed2.Value & ed34.Value & ed55.Value & ed6.Value & ed7.Value & ed8.Value & ed9.Value & ed10.Value & ed11.Value
            oWrite.WriteLine(entdetheadr.Value)
 
            'COMPANY BATCH CONTROL
            hashcount = hashcount + 75000051
            blockfill = 10 - ((counter + 4) - (Int((counter + 4) / 10) * 10))
            fillblock = blockfill
            totalblocks = (counter + 4 + blockfill) / 10
            '1 & 2
            cc12.Value = "8200"
            '3
            cc3.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 6 - Len(Trim(Str(counter)))) & Trim(Str(counter))
            '4
            cc4.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 10 - Len(Trim(Str(hashcount)))) & Microsoft.VisualBasic.Strings.Right(Str(hashcount), Len(Trim(Str(hashcount))))
            '5 I know this b/c of the lenght requirment
            cc5.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 12 - Len(Trim(Str(achtotal)))) & Trim(Str(achtotal))
            '6
            cc6.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 12 - Len(Trim(Str(achtotal)))) & Trim(Str(achtotal))
            '7, 8, 9, 10, & 11
            cc7891011.Value = "1394444498                         075033350000001"
            cpnyctrlheadr.Value = cc12.Value & cc3.Value & cc4.Value & cc5.Value & cc6.Value & cc7891011.Value
            oWrite.WriteLine(cpnyctrlheadr.Value)
 
            'FILE CONTROL 
            '1 & 2
            fc12.Value = "9000001"
            '3
            fc3.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 6 - Len(Trim(Str(totalblocks)))) & Trim(Str(totalblocks))
            '4
            fc4.Value = Microsoft.VisualBasic.Strings.Left(zst.Value, 8 - Len(Trim(Str(counter)))) & Trim(Str(counter))
            '5
            fc5.Value = cc4.Value
            '6
            fc6.Value = cc5.Value
            '7
            fc7.Value = cc6.Value
            '8 IS blank b/c reserved for future use
            fc8.Value = "                                       "
            flctrlheadr.Value = fc12.Value & fc3.Value & fc4.Value & fc5.Value & fc6.Value & fc7.Value & fc8.Value
            oWrite.WriteLine(flctrlheadr.Value)
            While fillblock > 0
                oWrite.WriteLine(fullfill.Value)
                fillblock = fillblock - 1
            End While
            oWrite.Flush()
            oWrite.Close()
            'End First Loop
        End While

[maged]

the problem is that you are nesting 2 loops

you are using while statament to read thru drAchGen and then you are using inside the loop another do while to read from the same drAchGen.

of course your problem is that the counter is located inside the inner loop so the outer loop overpasses the first count and then resume the next in the inner loop.

you should do only one loop, no need for nested loops on the same reader

rgds

[callydata]

Now, I am only looping once, but I am encountering errors with my data.
It broke at this code:

ed2.Value = Trim(drAchGen("ACHCode"))

Heres the error:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'String' is not valid.

What does this error mean?

[maged]

this means that you are trying to assign a null value to a string variable,

you can check first if the line is null before trying to put it into the string variable

i think you can use try and catch for this specifc line

rgds