Multiple DataAdapters?

S

Smithbr

Member
Joined
Jun 3, 2003
Messages
15
Hello,
I am having a problem viewing data in a form from more than one table. I have read through a bunch of tutorials and MSDN files and whatnot but have not been able to nail down what i need to do.

Here is the code I have so far....it worked when I only needed to data from one table but now that I have introduced the second I have run into tons of problems. I am not sure if I need two DataAdapters and one DataSet or two DAs and two DSs. Does anyone see what I need to do with my code to get this to work?


Code: 
Private Sub cmdfind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdfind.Click
        

        write the query to get the data you want
        Dim strhistory As String = "Select Invoicenumber, InvoiceDate, Total, CustomerNumber, BrokerID, LineSEQNo, ProductLine, COMM, Terms, Description, DiscountPercentage FROM PendingCommissions WHERE  InvoiceNumber = " & txtinvnum.Text & " ORDER BY BrokerID, ProductLine DESC"
        Dim strinvtotal As String = "SELECT InvoiceNumber, SUM(Total) AS InvTotal FROM InvoiceHistory WHERE InvoiceNumber = " & txtinvnum.Text & " GROUP BY InvoiceNumber"

        Set up the data adapter and pass to it the sql statement and the connection
        Dim daHistory As OleDbDataAdapter = New OleDbDataAdapter(strhistory, OleDbConnection1)
        Dim dainvtotal As OleDbDataAdapter = New OleDbDataAdapter(strinvtotal, OleDbConnection1)

        set the data set and fill it with data
        Dim History As DataSet = New DataSet
        daHistory.Fill(History, "PendingCommissions")
        dainvtotal.Fill(History, "InvoiceHistory")

        set the datatable
        Dim dtH As DataTable = History.Tables("PendingCommissions")
        Dim dtIT As DataTable = History.Tables("InvoiceHistory")

        Finds the number of rows in a record and declares it
        Dim rowtotal As Integer
        rowtotal = dtH.Rows.Count
        Dim First As Integer
        Dim Last As Integer
        First = 0
        Last = rowtotal - 1

I tried here to use 2 das and 1 ds...it did not work but maybe I have it set up wrong. When I run it the error always comes at
Code: 
dainvtotal.Fill(History, "InvoiceHistory")
Brent
 
You could try getting the data from both tables and putting it into the same "table" in the DataSet using a Join in the SQL statement itself. Something like this...

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.Field2
FROM Table1 as a
INNER JOIN Table2 as b
WHERE blah, blah...

Its been a little while since Ive done much with complex SQL statements but, Im certain it can be done that way. Theres probably a better, easier way to do it, that someone with far more knowledge than I have (which is not hard to do by the way...) will post almost immediately after I post this! ;)
 
That was what I thought would be the easiest way but because Table1 contains the details for Table2 it can not sum (what I need done)...it would just return the contents of Table1. I can only get teh data from Table2 if it comes from its own table...I checked ways to query in Access and foudn that I must use 2 tables...Do you know how I could set it up with two tables?
Brent
 
Well, you could use one Query with a larger Group By clause. Add the SUM to your first query and add ALL of the columns of the first query as columns in the Group By clause.
Something like:
Code: 
Select Invoicenumber, 
    InvoiceDate, 
    Total, 
    CustomerNumber, 
    BrokerID, 
    LineSEQNo, 
    ProductLine, 
    COMM, 
    Terms, 
    Description, 
    DiscountPercentage, 
    SUM(Total) AS InvTotal 
FROM PendingCommissions 
WHERE  InvoiceNumber = 123
GROUP BY Invoicenumber, 
    InvoiceDate, 
    Total, 
    CustomerNumber, 
    BrokerID, 
    LineSEQNo, 
    ProductLine, 
    COMM, 
    Terms, 
    Description, 
    DiscountPercentage 
ORDER BY BrokerID, ProductLine DESC

If you want two tables you need only one DataAdapter. Issue both SELECTS in one call (you may have to separate by a semicolon). In your call to DataAdapter.Fill(...) the name you give is a default. The first query will be in a table with that name, the next table from your SELECT will be given the same name plus a number. Look at the Tables(1).Tablename to see the name after the call to Fill.

-Nerseus
 
Back
Top