none
nested loops RRS feed

  • Question

  • I have written a function that searches a database table for records based on parameters in the function call. The code is: (this code works)

        Public Function GetPayInfo_n(ByVal ConnectionStr As String, ByVal lngMoYr As Long) As String
            Try
                Dim strData As String = ""
                Dim WWcn As New OleDbConnection(ConnectionStr)
                WWcn.Open()
                Dim strQuery As String = "SELECT * " _
                & "FROM Batches " _
                & "WHERE MonthYear = " & lngMoYr.ToString
    
                Dim cmd As New OleDbCommand(strQuery, WWcn)
                Dim rdr As OleDbDataReader
                rdr = cmd.ExecuteReader
                strData = ""
                Do While rdr.Read
                    strData = strData & "    " & rdr("Description").ToString.PadRight(20) & vbCrLf
                Loop
                WWcn.Close()
                GetPayInfo_n = strData
            Catch ex As Exception
                GetPayInfo_n = ex.Message.ToString
            End Try
        End Function
    

    I need to include some more information in the routine that is in another table in the same database. For each record found in the function above, I want to search another table in which the primary key in the record found is the foreign key in the second database. There are probably multiple records in the second table and I want to get the total of the amount field for those records. (I do this in VB6 with ADO recordsets) The following code is my effort but it gets errors because of what seems to be multiple errors.

        Public Function GetPayInfo_n(ByVal ConnectionStr As String, ByVal lngMoYr As Long) As String
            Try
                Dim strData As String = ""
                Dim dblTtl As Double
                'Dim strConn_n As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\ProgramData\lms\ArLhi20130411044111.mdb"
                Dim WWcn As New OleDbConnection(ConnectionStr)
                WWcn.Open()
                Dim strQuery As String = "SELECT * " _
                & "FROM Batches " _
                & "WHERE MonthYear = " & lngMoYr.ToString
    
                Dim cmd As New OleDbCommand(strQuery, WWcn)
                Dim rdr As OleDbDataReader
                rdr = cmd.ExecuteReader
    
                Dim cmd2 As New OleDbCommand(strQuery, WWcn)
                Dim rdr2 As OleDbDataReader
                strData = ""
                Do While rdr.Read
                    strQuery = "SELECT SUM(Amount) as BatchTtl " _
                    & "FROM Transactions " _
                    & "WHERE BatchID = " & rdr("BatchID")
                    cmd2.CommandText = strQuery
    
                    rdr2 = cmd2.ExecuteReader
                    dblTtl = 0
                    If rdr2.Read Then
                        dblTtl = rdr2("BatchTtl")
                    End If
                    rdr2.Close()
                    If dblTtl = 0 Then
                        'do nothing
                    Else
                        strData = strData & "    " & rdr("Description").ToString.PadRight(20) & Format(rdr2("BatchTtl"), "###,###,##0.00").ToString.PadLeft(20) & vbCrLf
                    End If
                Loop
                WWcn.Close()
                GetPayInfo_n = strData
            Catch ex As Exception
                GetPayInfo_n = ex.Message.ToString
            End Try
        End Function
    

    So I guess my question is: What is the best method to loop through Table2 based on each record found in Table1? Can I clear a DataReader and re-populate it with data from another query? If you could point me to an example it would be great!

    Thanks for your help!

    Thursday, May 23, 2013 8:17 PM

Answers