Databases
StackOverflow2013
Postsdbo
POhow to look up a a value from another datatable and display it in an unbound column of a bound datagridview?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to look up a a value from another datatable and display it in an unbound column of a bound datagridview?
    primarykey
    data
    text
    <p><strong>EDIT</strong></p> <p>I'm sorry, I tried to search for an answer but I'm having a really hard time translating the examples I've found into my own scenario.</p> <p>I have an MS Access Database that has an "Employees" table with the following columns:</p> <p>"Employee ID", "Name", "Middle", "Last", "Hire Date", "Termination Date".</p> <p>Another Table "Work" that has the columns:</p> <p>"Date", "Employee ID", "Activity", "Hours worked", "Department", "Shift", "Equipment Used",</p> <p>A datagridview is bound to the "Work" table. I call the FillbyDate() with a query made with the query editor at design time so only the entries for the specific date are loaded.</p> <p>I would like to create an unbound column to display the name (NAME, MIDDLE, AND LAST) of the employee from the "Employees Table" using the employee Id to find it, but only display the employees name if it hasn't been terminated.</p> <p>I'm completely new to coding with VB.Net, that might be reason why i don't understand or find the right examples. I would appreciate some sample code and the comments for each step to see how things are working.</p> <p>This is the code i have so far, but is not fully working. I'm able to look up the employee's name, but i´m NOT able to specify that i don't want the terminated employees to appear.</p> <pre><code> Private Sub PartePersonalDataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting 'This looks up the employee's full name using the employee number from the ID column (first column) Try Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex) If dgvr.Cells(0).Value IsNot Nothing AndAlso dgvr.Cells(0).Value IsNot DBNull.Value Then Dim empID As Integer = CInt(dgvr.Cells(0).Value) Dim qry = From dr As PersonalObraDataSet.PersonalObRow In PersonalObraDataSet.PersonalOb Where (dr.cdTrabajador = empID) 'This returns each part of the name and joins it in the the 2nd column (name column) DataGridView1.Rows(e.RowIndex).Cells(1).Value = (qry.First.Nombre1 &amp; " " &amp; qry.First.Nombre2 &amp; " " &amp; qry.First.Apellido1 &amp; " " &amp; qry.First.Apellido2) DataGridView1.Rows(e.RowIndex).DefaultCellStyle.BackColor = DefaultBackColor End If 'If there is an exemption like the employee doesn't exists this turns the background color red and instead 'of the name on the second column it shows "employee doesn't exist." Catch ex As Exception DataGridView1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.Red DataGridView1.Rows(e.RowIndex).Cells(1).Value = "ESTE EMPLEADO NO EXISTE" Exit Sub End Try End Sub </code></pre> <p>I already was able to get the "AND" part working, but the "OR" does not. </p> <p>Perhaps an example would make more sense:</p> <p>John has been terminated as of 01/01/2013. If i select the date 01/02/2013 or later on the DateTimePicker (called txtDate) I should not get this employee showing on the datagridview, but if I select 12/31/2012 or earlier John should still appear on the datagridview as he has not been terminated by this date.</p> <p>As I have the code right now I get only employees that have not been terminated regardless of their termination date. If I select 01/01/2013 and Carl was terminated in 07/01/2013 I still don't get his name to come up on the datagridview because he has a termination date, that is why I need the "OR" statement.</p> <p>This is what I have so far, and like I said it works up to the "AND" the "OR" does not.</p> <pre><code>Where (dr.cdTrabajador = empID) And ((dr.IsFSalidaNull) Or (txtDate.Value &lt;= dr.FSalida)) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBenjamin
    1. USDavid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COWhy not simply add a second condition in your where to see if the termination date is null or not? I.e., `IsDbNull(table.employeeterminationdate)`
      singulars
      1. POhow to look up a a value from another datatable and display it in an unbound column of a bound datagridview?
      1. USTim
    2. CO@Tim: Trying your suggestion I stumbled on half of the answer `Where (dr.cdTrabajador = empID) And (dr.IsFSalidaNull) Or (dr.FSalida <= txtDate.Value)` The problem is that I need to add the **"OR"** condition so the query works properly, but I can't seem to find the right way to write. "txtDate" is a datetimepicker that filters the datagridview.
      singulars
      1. POhow to look up a a value from another datatable and display it in an unbound column of a bound datagridview?
      1. USDavid
    3. COUse parentheses to group the two parts of the OR together - like this: `Where (dr.cdTrabajador = empID) And ((dr.IsFSalidaNull) Or (dr.FSalida <= txtDate.Value))`.
      singulars
      1. POhow to look up a a value from another datatable and display it in an unbound column of a bound datagridview?
      1. USTim
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload