title: How to Ommit DataGridViewRow from DataSource Based on Another Table's Data
body: <p>I have a DataGridView filled with Bills from a DataTable (which is set as it's DataSource). These Bills are shown to my engineers so that they can build a Bill of Materials. </p>
  
  <p>This is the SQL query which gets all my Bills:</p>
  
  <pre><code>SELECT * 
  FROM Bills
  ORDER BY Case IsNumeric(JobNumber) 
  WHEN 1 THEN Replicate('0', 50 - Len(JobNumber)) + JobNumber ELSE JobNumber END
  </code></pre>
  
  <p><em>This sorts the bills by job number (some are numbers, some are strings and some are a mix of integers and characters).</em></p>
  
  <hr>
  
  <p><strong>Features</strong></p>
  
  <p>They can open the bill to view it (<strong>Read only</strong>) or to <strong>edit</strong> it. </p>
  
  <p><strong>Further explanation</strong></p>
  
  <p>If an engineer opens a Bill to edit it, it will be placed in a table named "OpenBills". I do this so that no other engineer can open the same bill and edit it while being worked on. The point of the OpenBills table is to determine which bill is opened. Once I know which bill is opened, I don't want it to be provided in their DataGridView when they go to edit a bill.</p>
  
  <p>On the other hand, when the engineer searches for a bill to open as read-only, they should ALL be displayed (<em>even the ones currently opened</em>). The difference is I simply put a light blue color as the background of the row to notify the user that it is currently opened.</p>
  
  <p><strong>My current '<em>solution</em>'</strong></p>
  
  <p>I am currently using a <code>CellFormatting</code> event on my DataGridview. In short, I verify if the current Bill being added is in my OpenBills table <code>lst_OpenBills</code>. If it is, then I verify if I am viewing the DataGridView as read-only or for editting mode. If it's in edit mode, I will hide the row. If it is in read-only, I will change it's back color.</p>
  
  <pre><code>Private Sub dgvBills_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvBills.CellFormatting
      Dim drv As DataRowView
      If e.RowIndex &gt;= 0 Then
          If e.RowIndex &lt;= dsBOM.Tables("dtBills").Rows.Count - 1 Then
              drv = dsBOM.Tables("dtBills").DefaultView.Item(e.RowIndex)
              Dim c As Color
              If lst_OpenBills.ContainsKey(drv.Item("PK_Bill").ToString) Then
                  If int_EntryPoint = 0 Then
                      'Read Only
                      c = Color.LightBlue
                      e.CellStyle.BackColor = c
                  Else
                      'Edit mode
                      dgvBills.Rows(e.RowIndex).Visible = False
                  End If
              End If
          End If
      End If
  End Sub
  </code></pre>
  
  <hr>
  
  <h2>Problem with current solution</h2>
  
  <p>I use AlternatingRowStyles to make it easier to read. As you can see in the following comparisons, the edit mode picture should not display the alternating row styles like that. How could I ommit the DataGridViewRow from the DataTable ? Should I just re-apply the AlternatingRowStyles?</p>
  
  <p><strong><em>Read Only  results</em></strong></p>
  
  <p><img src="https://i.stack.imgur.com/GQUf7.png" alt="Read only"></p>
  
  <p><strong><em>Edit mode</em></strong></p>
  
  <p><img src="https://i.stack.imgur.com/cX8jJ.png" alt="Edit mode"></p>
  
  <p>Does anyone have any solutions? Perhaps my query to get all my bills isn't appropriate ... I use the same form for both situations (read-only &amp; edit mode). I just differentiate them using an Enum <code>enum_EntryPoint</code>. Any helps or suggestions is appreciated.</p>