Databases
StackOverflow2013
Postsdbo
POHow to fix a SQL statement that is not working properly?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to fix a SQL statement that is not working properly?
    primarykey
    data
    text
    <p><strong>Updated</strong></p> <p>The winform has a datetimepicker 4 regular texboxes and 2 masked texboxes and a bound datagridview.</p> <p>I'm using the cellformatting event of the datagridview to lookup the employees' names from another datatable. To accomplish this I'm using a sql statement that for the most part is working. </p> <p>As it is right now it returns the employees names from different columns on the second datatable and sets them in the second column of the datagridview right after employee ID as intended.</p> <p>The part that is NOT working is looking at the termination date ("FSalida" column) and returning the employees name only if it doesn't have a termination date or the selected date is earlier than the termination date. In other words if the employee has been terminated it shouldn't appear after the termination date.</p> <p>The code that I have is this:</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) And (dr.FSalida = Nothing) Or (dr.FSalida &lt;= txtDate.Text) '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 </code></pre> <p>The part that is not working is after the "And":</p> <pre><code>Where (dr.cdTrabajador = empID) And (dr.FSalida = Nothing) Or (dr.FSalida &lt;= txtDate.Text) </code></pre> <p>This is a sample of the data on the look up table</p> <pre><code>ProjectID EmployeeID DepartmentID HIreDate TerminationDate Name Middle Last 3116 83 1 09/03/2012 27/04/2012 John Doe Doe2 3116 373 1 16/11/2012 Pedro John Ortiz 3116 1 1 01/01/2013 Jose Maria Applessed </code></pre> <p>Your help fixing this would be much appreciated.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDavid
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to fix a SQL statement that is not working properly?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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