Multiple DataAdapters?

[Smithbr]

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 DA's and two DS's. Does anyone see what I need to do with my code to get this to work?

 

 

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 da's and 1 ds...it did not work but maybe I have it set up wrong. When I run it the error always comes at

dainvtotal.Fill(History, "InvoiceHistory")

Brent

[Mothra]

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...

 

It's been a little while since I've done much with complex SQL statements but, I'm certain it can be done that way. There's 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! ;)

[Smithbr]

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

[Nerseus]

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:

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