• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Need to put multiple tables into an OleDbDataAdapter

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
newbieweb
Asked:
newbieweb
1 Solution
 
-DJL-Commented:
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
 
newbiewebSr. Software EngineerAuthor Commented:
I tried this:

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

and it worked great.

Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now