Imports System
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Text
Imports System.IO
Imports System.ComponentModel
Imports System.Windows.Forms

Public Class frmKSBCards
    Dim cn As SqlCeConnection
    Dim PersonsCommand As SqlCeCommand
    Dim PersonsTable As DataTable
    Dim PersonsAdapter As SqlCeDataAdapter
    Dim Persons As SqlCeDataAdapter
    Public strWarnings As StringBuilder
    Dim bFoundDatesToRemember As Boolean

    Private Sub frmKSBCards_Load(ByVal sender As System.Object,
            ByVal e As System.EventArgs) Handles MyBase.Load
        'connect to the database
        Try
            cn = New SqlCeConnection("Data Source=|DataDirectory|DAL\Contacts.sdf")
            cn.Open()
        Catch sqlex As SqlCeException
            Dim SqlError As SqlCeError
            For Each SqlError In sqlex.Errors
                MessageBox.Show(SqlError.Message)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        'Select/Retrieve(records)
        Dim Sql As String = "SELECT Surname, P.Firstname, P.Dob, " &
            "DATEDIFF(Year, P.Dob, GETDATE()) AS AgeNow " &
            "FROM Addresses " &
            "INNER JOIN Persons P " &
            "ON Addresses.AddressID = P.AddressID"
        PersonsCommand = New SqlCeCommand(Sql, cn)
        PersonsAdapter = New SqlCeDataAdapter()
        PersonsAdapter.SelectCommand = PersonsCommand
        PersonsTable = New DataTable()
        Dim ds As New DataSet
        PersonsAdapter.Fill(PersonsTable)
        PersonsAdapter.Fill(ds)
        dgv.DataSource = ds.Tables(0)
        strWarnings = New StringBuilder
        CheckRenewals()
    End Sub

    Private Sub btnRenewals_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRenewals.Click
        'Display renewal status if any is available
        If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then
            MessageBox.Show(strWarnings.ToString(), "Current Dates Due")
        Else
            MessageBox.Show("No pending Birthday(s) due", "No Dates Available")
        End If
    End Sub

    Private Sub CheckRenewals()
        'Get Birthday and Anniversary dates
        Try
            Dim DatesView As DataView = PersonsTable.DefaultView
            With DatesView
                Dim bFoundDatesToRemember As Boolean = False
                Dim sDateReminders As String = String.Empty
                For DateCheckCounter As Integer = 0 To .Count - 1
                    With .Item(DateCheckCounter)

                        'Get Date from database fields Dob
                        Dim CardDate1 As Date = CType(.Item("Dob"), Date)
                        'Days difference between two dates
                        Dim NumberOfDays1 As Long = DateDiff(DateInterval.Day, Now, CardDate1) + 1

                        'Test For Dob
                        If NumberOfDays1 < 10 Then
                            If NumberOfDays1 > -1 And NumberOfDays1 < 0 Then
                                Dim DayString As String = IIf(NumberOfDays1 = -1, " day", " days").ToString
                            ElseIf NumberOfDays1 > -1 Then
                                bFoundDatesToRemember = True
                                If sDateReminders <> String.Empty Then sDateReminders &= Environment.NewLine
                                Dim DayString As String = IIf(NumberOfDays1 = 1, " day", " days").ToString
                                sDateReminders &= .Item("P.Firstname").ToString.Trim & " Birthday due in " & _
                                    NumberOfDays1.ToString & DayString
                            End If
                        End If

                        If (Not String.IsNullOrEmpty(sDateReminders)) Then
                            strWarnings.Append(sDateReminders & Environment.NewLine)
                        End If
                    End With
                Next
            End With

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Errror - Notification")
            Exit Sub
        Finally
            tmrReminders.Enabled = False
            If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then
                With niReminders
                    .Visible = True
                    .BalloonTipIcon = ToolTipIcon.Warning
                    .BalloonTipText = "You have outstanding cards to action"
                    .BalloonTipTitle = "Cards Due for Action"
                    .ShowBalloonTip(5)
                End With
            End If
        End Try
    End Sub

  
End Class

 SELECT *
   FROM AdventureWorks.HumanResources.Employee
  WHERE DATEDIFF(dd,
                 DATEADD(yy, -(YEAR(GETDATE())-1900),GETDATE()), --Today
                 DATEADD(yy, -(YEAR(BirthDate)-1900),BirthDate)
                ) BETWEEN 1 AND 10