query database with each object in Arraylist and databind to gridview?

I have a function that returns a list of account numbers as an Arraylist. I am trying to use each account as a command parameter in another sub routine to get more data about each account number. This only returns the data for the last account number in the arraylist. I need to use each account number, call the database, get the additional information and store ALL of the data into a Gridview (databind). Example: If I had 3 account numbers in my arraylist return 3 rows of data to the gridview. I am struggling with how to get ALL of the information for each value (account number) in the Arraylist. Can someone point me in the right direction?? I think this can be done but I am not certain if my approach is correct or not. Perhaps I need to create datatables that contain the additional information for each value passed via the arraylist....Any Ideas??

@开发者_C百科jwatts1980 thanks for the comment: I will try to clarify. I have an arraylist of account numbers (and maybe this is where I am off track) I am trying to use the values in this ArrayList as command parameters in another call to a different table/file that returns more info on those accounts. I will provide a portion of the code to help clarify what it is I am attempting to do:

Private Function ReturnMultAccts(ByVal strAcct) As ArrayList
Dim acctsDetail As New ArrayList
Dim dsn As String = ConfigurationManager.ConnectionStrings.ConnectionString
    Dim sql As String = "SELECT DISTINCT * FROM FILE WHERE ACCTNUM=?"
    Using conn As New OdbcConnection(dsn)
        Using cmd As New OdbcCommand(sql, conn)
            conn.Open()
            cmd.Parameters.Add("ACCTNUM", OdbcType.VarChar, 20).Value = strAcct
            Dim rdrUsers As OdbcDataReader = cmd.ExecuteReader()
            If rdrUsers.HasRows Then
                While rdrUsers.Read()
            acctsDetail.Add(Trim(rdrUsers.Item("ACCTNUM")))
                End While
            End If
            rdrUsers.Close()
            conn.Close()
        End Using
    End Using

This returns an Arraylist of Account Numbers (Lets say it is 3 acct numbers). I call this Function from another Sub:

Private Sub GetMoreAcctInfo(ByVal strAcct)
    'Create New ArrayList
    Dim MultAccts As New ArrayList
    'Pass strAcct to Function to get Multiples
    MultAccts = ReturnMultAccts(strAcct)
    'Create the variable BachNum for the loop
    Dim BachNum As String = MultAccts.Item(0)
    For Each BachNum In MultAccts
   'Get All of the necessary info from OtherFile based on the BachNum for BOS's
        Dim dsn As String = ConfigurationManager.ConnectionStrings.ConnectionString
        Dim sql As String = "SELECT ACCTNUM, BILSALCOD1, BILSALCOD2, BILSALCOD3, OTHACCTNUM FROM OtherFile WHERE OTHACCTNUM=?" 'Equal to the items in the arraylist
        Using conn As New OdbcConnection(dsn)
            Using cmd As New OdbcCommand(sql, conn)
                conn.Open()
                cmd.Parameters.Add("OTHACCTNUM", OdbcType.VarChar, 20).Value = BachNum
                Using adapter = New OdbcDataAdapter(cmd)
                    Dim DS As New DataSet()
                    adapter.Fill(DS)
                    GridView1.DataSource = DS
                    GridView1.DataBind()
               End Using
            End Using
        End Using
    Next
End Sub

Hopefully this clarifies what I am attempting to do...??

To elaborate on my suggestion, you will need a list of strongly typed objects. You can add those items to the list, then bind the list to the GridView.

I'll start at the beginning. You know what kind of data is coming from your database: ACCTNUM, BILSALCOD1, BILSALCOD2, BILSALCOD3, and OTHACCTNUM. So you can use those to create an object.

Friend Class AccountClass
    Private pACCTNUM As string = ""
    Private pBILSALCOD1 As string = ""
    Private pBILSALCOD2 As string = ""
    Private pBILSALCOD3 As string = ""
    Private pOTHACCTNUM As string = ""

    Public Property ACCTNUM() As string
        Get
            Return pACCTNUM
        End Get
        Set(ByVal value as string)
            Me.pACCTNUM = value
        End Set
    End Property

    Public Property BILSALCOD1() As string
        Get
            Return pBILSALCOD1
        End Get
        Set(ByVal value as string)
            Me.pBILSALCOD1 = value
        End Set
    End Property

    Public Property BILSALCOD2() As string
        Get
            Return pBILSALCOD2
        End Get
        Set(ByVal value as string)
            Me.pBILSALCOD2 = value
        End Set
    End Property

    Public Property BILSALCOD3() As string
        Get
            Return pBILSALCOD3
        End Get
        Set(ByVal value as string)
            Me.pBILSALCOD3 = value
        End Set
    End Property

    Public Property OTHACCTNUM() As string
        Get
            Return pOTHACCTNUM
        End Get
        Set(ByVal value as string)
            Me.pOTHACCTNUM = value
        End Set
    End Property

    Sub New(ByVal ACCTNUM As string, ByVal BILSALCOD1 As string, ByVal BILSALCOD2 As string, ByVal BILSALCOD3 As string, ByVal OTHACCTNUM As string)
        Me.ACCTNUM = ACCTNUM
        Me.BILSALCOD1 = BILSALCOD1
        Me.BILSALCOD2 = BILSALCOD2
        Me.BILSALCOD3 = BILSALCOD3
        Me.OTHACCTNUM = OTHACCTNUM
    End Sub
End Class

Then you rework the GetMoreAcctInfo() routine to use it.

Private Sub GetMoreAcctInfo(ByVal strAcct)
    'Create New ArrayList
    Dim MultAccts As ArrayList

    'Pass strAcct to Function to get Multiples
    MultAccts = ReturnMultAccts(strAcct)

    'Create the variable BachNum for the loop
    Dim BachNum As String

    'Create the list to bind to the grid
    Dim AcctInfo As New Generic.List(Of AccountClass)

    'create the dataset
    Dim DS As DataSet

    For Each BachNum In MultAccts
   'Get All of the necessary info from OtherFile based on the BachNum for BOS's
        Dim dsn As String = ConfigurationManager.ConnectionStrings.ConnectionString
        Dim sql As String = "SELECT ACCTNUM, BILSALCOD1, BILSALCOD2, BILSALCOD3, OTHACCTNUM FROM OtherFile WHERE OTHACCTNUM=?" 'Equal to the items in the arraylist
        Using conn As New OdbcConnection(dsn)
            Using cmd As New OdbcCommand(sql, conn)
                conn.Open()
                cmd.Parameters.Add("OTHACCTNUM", OdbcType.VarChar, 20).Value = BachNum
                Using adapter = New OdbcDataAdapter(cmd)
                    DS = New DataSet()
                    adapter.Fill(DS)

                    For Each t As DataTable In DS.Tables
                        For Each r As DataRow In t.Rows
                            AcctInfo.Add(new AccountClass(r("ACCTNUM"), r("BILSALCOD1"), r("BILSALCOD2"), r("BILSALCOD3"), r("OTHACCTNUM")))
                        Next
                    Next
               End Using
            End Using
        End Using
    Next

    GridView1.DataSource = AcctInfo
    GridView1.DataBind()
End Sub