title: Plotting multiple chart series from columns across different DataTables within a DataSet
body: <p>New here and to vb.net.  I'm working on a windows form that will allow me to screen research results from an Access database. User selections determine the Access table to query for a base case and comparison case and the results are passed to a separate datagridview for each and plotted together on a combined XY chart with a series for each case.</p>
  
  <p>Currently, I have it working for each using separate approaches but requires 2 queries to the database for each case and for both datagrid view and charting.</p>
  
  <p>My specific question is whether I can query the data I need once for each case in the beginning and use it for both the datagridview and the chart series. I'm going to have to do some basic data manipulation based on a user input and I'd like to insert it after the query but ahead of the datagridview and charting. Having multiple queries to the database makes it a little more complicated. </p>
  
  <p><strong>Datagridview</strong></p>
  
  <p>First set of code queries the database for the correct Access table and passes the entire thing into a DataTable for each case and then binds the DataGridView to it:</p>
  
  <pre><code>Dim ds As New DataSet
  Dim da As New OleDb.OleDbDataAdapter
  Dim cmd As New OleDb.OleDbCommand("SELECT * FROM " &amp; resultsLabel1.Text, resultsConn)
  Dim baseTable As New DataTable
  Dim comp1Table As New DataTable
  
  resultsConn.Open()
  
  If baseFlag = True Then
      Try
          cmd.CommandText = "SELECT * FROM " &amp; resultsLabel1.Text 'Base Case table ID
          da.SelectCommand = cmd
          da.Fill(ds, "BaseResults")
          baseTable = ds.Tables("BaseResults")
          DataGridView2.DataSource = baseTable
      Catch ex As Exception
          MsgBox("Could not load Base Case data")
      End Try
      Else
          baseTable.Clear()
          DataGridView1.DataSource = baseTable
  End If
  
  If comp1Flag = True Then
      Try
          cmd.CommandText = "SELECT * FROM " &amp; resultsLabel2.Text 'Comparison 2 table ID
          da.SelectCommand = cmd
          da.Fill(ds, "comp1Results")
          comp1Table = ds.Tables("Comp1Results")
          DataGridView2.DataSource = comp1Table
      Catch ex As Exception
          MsgBox("Could not load Comparison 1 data")
      End Try
      Else
          comp1Table.Clear()
          DataGridView2.DataSource = comp1Table
  End If
  
  resultsConn.Close()
  </code></pre>
  
  <p><strong>Charting</strong></p>
  
  <p>For the charting, the same Access tables as above are queried for specific fields (based on user input) and then passed to the respective chart series to be plotted. I'd like to be able to just query the datatables from above rather than the database, but that's what I can't figure out:</p>
  
  <pre><code>If baseFlag = True Then
      Chart1.Series("Base Case").Points.Clear()
      Chart1.Series("Base Case").IsVisibleInLegend = True
      cmd = New OleDb.OleDbCommand("SELECT [" &amp; xAxisComboBox.SelectedItem &amp; "],[" &amp; yAxisComboBox.SelectedItem &amp; "] FROM " &amp; resultsLabel1.Text, resultsConn)
      Dim dr1 As OleDb.OleDbDataReader = cmd.ExecuteReader
      While dr1.Read
          Chart1.Series("Base Case").Points.AddXY(dr1(xAxisComboBox.SelectedItem).ToString, dr1(yAxisComboBox.SelectedItem).ToString)
      End While
      dr1.Close()
      cmd.Dispose()
  Else
      Chart1.Series("Base Case").IsVisibleInLegend = False
      Chart1.Series("Base Case").Points.Clear()
  End If
  
  If comp1Flag = True Then
      Chart1.Series("Comparison 1").Points.Clear()
      Chart1.Series("Comparison 1").IsVisibleInLegend = True
      cmd = New OleDb.OleDbCommand("SELECT [" &amp; xAxisComboBox.SelectedItem &amp; "],[" &amp; yAxisComboBox.SelectedItem &amp; "] " &amp; resultsLabel2.Text, resultsConn)
      Dim dr2 As OleDb.OleDbDataReader = cmd.ExecuteReader
      While dr2.Read
          Chart1.Series("Comparison 1").Points.AddXY(dr2(xAxisComboBox.SelectedItem).ToString, dr2(yAxisComboBox.SelectedItem).ToString)
      End While
      dr2.Close()
      cmd.Dispose()
  Else
      Chart1.Series("Comparison 1").IsVisibleInLegend = False
      Chart1.Series("Comparison 1").Points.Clear()
  End If
  </code></pre>
  
  <p><strong>Summary</strong></p>
  
  <p>Hopefully this all makes sense so far. So back to the original question, is there is a way to read the Access tables into their own DataTables, manipulate the fields I need to manipulate, and then query the needed column/field for the XY chart series? Thanks for the help.</p>