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);
newbiewebSr. Software EngineerAsked:
Who is Participating?
 
-DJL-Connect With a Mentor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.