1:  Dim whereSet As Boolean = False
   2:   
   3:      Public Function cbVal(ByVal value As String) As String
   4:          Dim vClass As String = "NotApplicable"
   5:          Dim val As Integer = CInt(value)
   6:          If val = 0 Then
   7:              vClass = "False"
   8:          ElseIf val = 1 Then
   9:              vClass = "True"
  10:          Else
  11:              Return ""
  12:          End If
  13:          Return "<img src=""Styles/images/" + vClass + ".png"" />"
  14:      End Function
  15:   
  16:      Protected Sub btnUpdateFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateFilter.Click
  17:          Try
  18:              adbSafetyCards.SelectCommand = "SELECT TOP 500 * FROM [tblSafetyCards] " + buildWhereClause()
  19:              lviewCards.DataBind()
  20:              Response.Write(adbSafetyCards.SelectCommand)
  21:              Response.Write("<br />Contains: " + lviewCards.Items.Count.ToString + " items.")
  22:          Catch ex As Exception
  23:              Response.Write("<br />" + adbSafetyCards.SelectCommand)
  24:              Response.Write("<br />" + ex.Message)
  25:          End Try
  26:      End Sub
  27:   
  28:      Private Function buildWhereClause() As String
  29:          Return getJobFilter() + getDateFilter() + getBooleanClause()
  30:      End Function
  31:   
  32:      Private Function conditionSeperator() As String
  33:          If whereSet Then
  34:              Return " AND "
  35:          Else
  36:              whereSet = True
  37:              Return " WHERE "
  38:          End If
  39:      End Function
  40:   
  41:      Private Function getBooleanClause() As String
  42:          Dim boolClause As String = ""
  43:          If Not (cbObservation.Checked And cbNearMiss.Checked And cbStopWorkAuthority.Checked And cbNonMMREmployees.Checked) Then
  44:              If cbObservation.Checked Then
  45:                  boolClause += conditionSeperator() + "Observation = TRUE "
  46:              End If
  47:              If cbNearMiss.Checked Then
  48:                  boolClause += conditionSeperator() + "NearMiss = TRUE "
  49:              End If
  50:              If cbStopWorkAuthority.Checked Then
  51:                  boolClause += conditionSeperator() + "StopWorkAuth = TRUE "
  52:              End If
  53:              If cbNonMMREmployees.Checked Then
  54:                  boolClause += conditionSeperator() + "NonMMREmployee = TRUE "
  55:              End If
  56:          End If
  57:          Return boolClause
  58:      End Function
  59:   
  60:      Private Function getDateFilter() As String
  61:          Dim filterDate As Boolean = False
  62:          Dim dateSpan As Boolean = False
  63:          Dim dateClause As String = ""
  64:   
  65:          If IsDate(txtDateBegin.Text) And IsDate(txtDateEnd.Text) Then
  66:              dateSpan = True
  67:          ElseIf IsDate(txtDateBegin.Text) Or IsDate(txtDateEnd.Text) Then
  68:              filterDate = True
  69:          Else
  70:              Return ""
  71:          End If
  72:          If dateSpan Then
  73:              dateClause = "CardDate >= #" + txtDateBegin.Text + " 00:00:000# AND CardDate <= #" + txtDateEnd.Text + " 00:00:000# "
  74:          ElseIf filterDate Then
  75:              dateClause = "CardDate = #" + txtDateBegin.Text + " 00:00:000# "
  76:          End If
  77:          Return conditionSeperator() + dateClause
  78:      End Function
  79:      Private Function getJobFilter() As String
  80:          Dim jobsClause As String = ""
  81:          Dim filterJobs As Boolean = False
  82:          If lbJobFilter.GetSelectedIndices.Length > 1 Then
  83:              filterJobs = True
  84:              jobsClause += "Job IN ("
  85:              For Each job As Integer In lbJobFilter.GetSelectedIndices()
  86:                  If job <> lbJobFilter.SelectedIndex Then
  87:                      jobsClause += ","
  88:                  End If
  89:                  jobsClause += "'" + lbJobFilter.Items(job).Value + "'"
  90:              Next
  91:              jobsClause += ")"
  92:          ElseIf lbJobFilter.GetSelectedIndices.Length = 1 Then
  93:              If lbJobFilter.SelectedValue <> "" Then
  94:                  filterJobs = True
  95:                  jobsClause += "Job = '" + lbJobFilter.SelectedValue + "'"
  96:              Else
  97:                  Return ""
  98:              End If
  99:          Else
 100:              Return ""
 101:          End If
 102:          Return conditionSeperator() + jobsClause
 103:      End Function