How to fix a SQL statement that is not working properly?

[david12king]

The winform has a datetimepicker 4 regular texboxes and 2 masked texboxes and a bound datagridview.

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.

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.

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.

The code that I have is this:

 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 <= 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 & " " & qry.First.Nombre2 & " " & qry.First.Apellido1 & " " & 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

The part that is not working is after the "And":

Where (dr.cdTrabajador = empID) And ((dr.FSalida = Nothing) Or (dr.FSalida <= txtDate.Text))

[Killer42]

Could you be more specific as to how it's "not working"? Does it:

• Produce an error? (Please provide details)
• Return the wrong set of records?
• Fail to return any records?
• Return all records?

A couple of other details might also prove useful:

• The field type of FSalida;
• An example of the exact text in txtDate.

Not having a lot of experience in this area myself, I wonder whether Nothing is the correct test there - perhaps it should be Null? (I don't know, I'm just trying out ideas).