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&quot" in the code.
Brent

 

[PankajBanga]

TRY SOMETHING LIKE THIS...

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"

Dim invHistoryDA As OleDbDataAdapter = New OleDbDataAdapter("SHAPE {" & strHistory & "}" & " APPEND ({" & strInvTotal & "} AS InvoiceHist " & " RELATE InvoiceNumber TO InvoiceNumber)", yourConnectionString)

Dim invDS As New DataSet()
invHistoryDA.Fill(invDS, "Invoices&quot
Dim historyRow, invRow As DataRow
Dim InvHist As DataRow()
Dim sline As String
Dim i As Integer

historyRow = invDS.Tables("Invoices&quot.Rows(0)
InvHist = historyRow.GetChildRows("InvHist&quot

For more details visit the following link

http://support.microsoft.com/default.aspx?scid=kb;en-us;309487

 

[smithbr]

I tried that but I still get the error at
invHistoryDA.Fill(invDS, "Invoices&quot, the error message is: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Every time it is the DataAdapter...what do I need to do to get the DataAdapter to work?

 

[PankajBanga]

can i have a look at your Connection String

 

[smithbr]

I set up my OleDbConnection at design time, this was the only part of the project that I have not doen pragramatically. Here is the connection string info copied out the properties.


Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Jet OLEDBatabase Password=;Data Source="C:\Documents and Settings\Brent\My Documents\Commissions System.mdb";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider="Microsoft.Jet.OLEDB.4.0";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False

 

[PankajBanga]

'Initialize connection string.
Dim strConn as String = "Provider=MSDataShape;Data Provider=SQLOLEDB;" & "Data Source=" & Application.StartupPath & "\System.mdb"

'Create an oleDbConnection object, and then pass in the ConnectionString to the constructor.
Dim conn As OleDbConnection = New OleDbConnection(strConn)

Dim invHistoryDA As OleDbDataAdapter = New OleDbDataAdapter("SHAPE {" & strHistory & "}" & " APPEND ({" & strInvTotal & "} AS InvoiceHist " & " RELATE InvoiceNumber TO InvoiceNumber)", conn)


Make sure that your .mdb is placed in the same folder as your application (.exe). The above is suppler, you are not hard coding the DataSource path. You just have to insure that .mdb file is placed in the same folder as your application.

Never set your Connections at design time.

 

[smithbr]

If I do it this way, when I fill the dataset, don't I have to define what table I am pulling from? I need data from two different tables, which one do I define or do I put both?

Dainvhistory.Fill(InvHistory, "PendingCommissions", "InvoiceHistory&quot
or
Dainvhistory.Fill(InvHistory, PendingCommissions&quot
or
Dainvhistory.Fill(InvHistory,"InvoiceHistory&quot


Brent

 

[PankajBanga]

Dainvhistory.Fill(InvHistory, "PendingCommissions&quot

 

[christywarner]

I'm trying to connect to a .csv file and input it into a dataset using the .fill dataadapter technique...here is my code, but I keep getting a system error:

If ext.Text = "csv" Then
str = "SELECT * FROM " & thefilename.Text
conn2 = New Microsoft.Data.Odbc.OdbcConnection("driver={Microsoft Text Driver (.txt, .csv)};" & _
"driverid=27;format=csvdelimited;defaultdir=c:/automated auditors/testdata;&quot
conn2.Open()
da2 = New Microsoft.Data.Odbc.OdbcDataAdapter(str, conn2)
dt2 = ds2.Tables.Add("invoices&quot
da2.Fill(dt2)
conn2.Close()
End If

I also tried:

If ext.Text = "csv" Then
str = "SELECT * FROM [Sheet1$]"
conn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & thefilename.Text & "; Extended Properties=Text;&quot
End If

Any suggestions? thank you!
Christy.