By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,417 Members | 1,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,417 IT Pros & Developers. It's quick & easy.

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

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub PartePersonalDataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
  2.     'This looks up the employee's full name using the employee number from the ID column (first column) 
  3.     Try
  4.         Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
  5.         If dgvr.Cells(0).Value IsNot Nothing AndAlso dgvr.Cells(0).Value IsNot DBNull.Value Then
  6.             Dim empID As Integer = CInt(dgvr.Cells(0).Value)
  7.             Dim qry = From dr As PersonalObraDataSet.PersonalObRow In PersonalObraDataSet.PersonalOb
  8.             Where (dr.cdTrabajador = empID) And ((dr.FSalida = Nothing) Or (dr.FSalida <= txtDate.Text))
  9.             'This returns each part of the name and joins it in the the 2nd column (name column)
  10.             DataGridView1.Rows(e.RowIndex).Cells(1).Value = (qry.First.Nombre1 & " " & qry.First.Nombre2 & " " & qry.First.Apellido1 & " " & qry.First.Apellido2)
  11.             DataGridView1.Rows(e.RowIndex).DefaultCellStyle.BackColor = DefaultBackColor
  12.         End If
  13.         'If there is an exemption like the employee doesn't exists this turns the background color red and instead
  14.         'of the name on the second column it shows "employee doesn't exist."
  15.     Catch ex As Exception
  16.         DataGridView1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.Red
  17.         DataGridView1.Rows(e.RowIndex).Cells(1).Value = "ESTE EMPLEADO NO EXISTE"
  18.         Exit Sub
The part that is not working is after the "And":

Expand|Select|Wrap|Line Numbers
  1. Where (dr.cdTrabajador = empID) And ((dr.FSalida = Nothing) Or (dr.FSalida <= txtDate.Text))
Jul 30 '13 #1
Share this Question
Share on Google+
1 Reply

Expert 5K+
P: 8,434
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).
Jul 31 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.