title: Retrieving Data from a query on multiple tables in C#
body: <p>Alrighty, so I have a C# program I'm working on and I'm running into a problem when trying to query my database.</p>
  
  <p>I have three tables (don't think about names, their generalized for the purpose of this question) - CUSTOMER, CUSTORD, &amp; ORDERS</p>
  
  <h3>CUSTOMER</h3>
  
  <ul>
  <li>Has a field called custID</li>
  </ul>
  
  <h3>ORDERS</h3>
  
  <ul>
  <li>Has a field called orderID</li>
  </ul>
  
  <h3>CUSTORD</h3>
  
  <ul>
  <li>Many to many relationship - One customer can have many orders and one order can have many customers</li>
  <li>Contains...
  <ul>
  <li>custID (linked to CUSTOMER.custID)</li>
  <li>orderID (linked to ORDERS.orderID)</li>
  </ul></li>
  </ul>
  
  <p>Basically I need a way to put in a custID and get a list of all orders that a customer has and display that in a dataGridView. </p>
  
  <h2>What I've Tried</h2>
  
  <p>I've tried adding queries to the different tableAdapters using the Query Configuration Wizard in the DataSet Designer. The main problem with this is that when I try to use JOIN it gives me the error "JOIN expression not supported." JOIN is the only way that I know how to relate the data using SQL. </p>
  
  <p>I've also tried adding the code manually but I can never get the information in the right format for being displayed in a dataGridView. If I can arrange all the orderID's that are related to the custID into an array maybe that is where I can find a solution. I can figure out how to loop through that info, querying according to each sibID, but how would I compile all that info into one tableAdapter to be displayed in the dataGridView?</p>
  
  <p>Honestly anything helps and if you need some more info, just let me know.</p>
  
  <h2>Specifications</h2>
  
  <ul>
  <li>IDE: Visual Studio Ultimate 2012 </li>
  <li>Database created using: Microsoft Access 2010</li>
  <li>Language: C#</li>
  </ul>
  
  <h1>Edit</h1>
  
  <p>So I went the route of putting everything into an array that I can parse later. still getting some errors but maybe it will shed some more light on what I'm trying to do. The error I'm getting is, "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign key constraints." I double checked and nothing was null and nothing is repeated. It might be the foreign key but how do I solve that? Here's the code...</p>
  
  <ol>
  <li>GetSelectedCustOrders takes in a custID and queries CUSTORD for matching orderID's
  <ul>
  <li>SELECT orderID FROM CUSTORD WHERE custID= selectedCust</li>
  </ul></li>
  </ol>
  
  <p><code>DBDataSetTableAdapters.CUSTORDTableAdapter CUSTORDTableAdapter = new DBDataSetTableAdapters.CUSTORDTableAdapter();</code></p>
  
  <pre><code>    string relationString = "-1";
      DataTable relationResult = new DataTable();
  
      relationResult = CUSTORDTableAdapter.GetSelectedCustOrders(Convert.ToInt16(custID));
  
      foreach (DataRow row in relationResult.Rows)
      {
          foreach (DataColumn column in relationResult.Columns)
          {
              relationString += Convert.ToString(row[column]);
              testLabel.Text = relationString;
          }
      }
  </code></pre>
  
  <h1>Edit 2</h1>
  
  <p>So the reason why I was getting the error above was because I was trying to change the schema of the table. If I change the SQL query statement to...</p>
  
  <p><code>SELECT * FROM CUSTORD WHERE custID= selectedCust</code> </p>
  
  <p>...then it worked perfectly! I implemented an index into my foreach so I could select only the orderID and appended those onto the relationString. Now just to parse the info, convert it an int and run the query on ORDERS for the orderID's.</p>