Solved

Need to put multiple tables into an OleDbDataAdapter

Posted on 2007-11-22
2
229 Views
Last Modified: 2013-12-16
I have this code working fine.  But I need to access the ZipCode, contained in the ZipCodes table, from the ordersDataSet.  Can I do that?  I had the impression that the SQL comand was creating one big long record by adding the columns from various tables, when the CustomerID and ZipCode match between the tables.  But how do I reference the ZipCode?  The exception I get says the ZipCode is not part of the OrderHeaders table.


Thanks,
newbieweb


                connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source=" + DataSource + ";Jet OLEDB:Database";

                sql = "SELECT * FROM OrderHeaders, CustomerFiles, ZipCodes WHERE ";
                sql += "OrderHeaders.CustomerID=CustomerFiles.CustomerID AND ";
                sql += "CustomerFiles.ZipCode=ZipCodes.ZipCode";

                if (orderDataAdapter == null || orderDataSet == null)
                {
                    orderDataAdapter = new OleDbDataAdapter(sql, connectString);
                    myOrderHistory = new MyOrderHistory(Handles, this);
                    orderDataSet = new DataSet();
                    orderDataAdapter.Fill(orderDataSet, "OrderHeaders");
                }
                else
                {
                    orderDataSet.Clear();
                    orderDataAdapter.Fill(orderDataSet, "OrderHeaders");
                }
                int numRows = orderDataSet.Tables["OrderHeaders"].Rows.Count;
                int tables = orderDataSet.Tables.Count;
                myOrderHistory.CreateNewOrders(orderDataSet);
0
Comment
Question by:newbieweb
2 Comments
 
LVL 6

Accepted Solution

by:
-DJL- earned 500 total points
ID: 20336671
Try the SQL statement below.

You need to use the JOIN command if you want the output to be one big table
SELECT * FROM OrderHeaders INNER JOIN CustomerFiles ON (OrderHeaders.CustomerID=CustomerFiles.CustomerID) INNER JOIN ZipCodes ON (CustomerFiles.ZipCode=ZipCodes.ZipCode)

Open in new window

0
 

Author Comment

by:newbieweb
ID: 20336681
I tried this:

orderDataAdapter.Fill(orderDataSet, "OrderHeaders, CustomerFiles, ZipCodes");

and it worked great.

Thanks.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now