Sending Email Alerts/Reminders Automatically

[egstatus]

Hi all,
I have searched for this topic and I can't find anything that matches my request. And if this question has been asked before, I apologize.

I have a SQL Express DB with employee info. One of the fields is the DOB fields.

I would like to send an email to the employee on his/her birthday.

I am Using VB.NET 2008
I have the code that sends email already, so that part is taken care.

How do I go about checking the records in the database to see if the Employee birthday = today and if it is how to generate the email automatically?
I don't want to run a service, I like this to happens when the application is launched in the morning.

Once the mail has been sent, I like to mark that record with a EmailSent=True.

I am sure this must have been asked before, or is there any other way of doing this? A better or more efficient way perhaps

Thanks


Ed

 

[RiverGuy]

Something like this? Call this routine when your application starts (not tested):

    Sub GetBirthdays()
        Dim con As New SqlClient.SqlConnection("Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;")
        Dim dr As SqlClient.SqlDataReader
        Dim cmdSelect As New SqlClient.SqlCommand("SELECT * FROM EmployeeTable WHERE Birthday = @Birthday AND EmailSend = 0", con)
        cmdSelect.Parameters.AddWithValue("@BirthDay", DateTime.Today)
        Dim cmdUpdate As New SqlClient.SqlCommand("UPDATE EmployeeTable SET EmailSent = 1 WHERE EmployeeID = @EmployeeID", con)
        cmdUpdate.Parameters.Add("@EmployeeID", SqlDbType.Int)
        con.Open()
        dr = cmdSelect.ExecuteReader
        While dr.Read
            Dim EmployeeID As Integer = dr.Item("EmployeeID")
            Dim EmailAddress As String = dr.Item("EmailAddress")
            '*******************Call Send Email Routine Here (pass in Email Address)
            cmdUpdate.Parameters("@EmployeeID").Value = EmployeeID
            cmdUpdate.ExecuteScalar()
        End While
        dr.Close()
        dr = Nothing
        con.Close()
        con.Dispose()
        con = Nothing
        cmdSelect.Dispose()
        cmdSelect = Nothing
        cmdUpdate.Dispose()
        cmdUpdate = Nothing
    End Sub

 

[Andrzejek]

WOW, That's ready to copy/paste code.

I was going for something simple to trancslate into VB.NET code, like:

Select DOB, EmailSent Where UserName = 'UserLoggedIn'

If Month(DOB) = Month(Today) And _
    Day(DOB) = Day(Today) And _
    EmailSent = False Then
        SendAnEMail[green]
        'You can calculate the age: Year(Today) - Year(DOB)
[/green]        Update EmailSent To True
End If

Update EmailSent To False for all other Records that do not match today's B-Days

Have fun.

---- Andy

 

[egstatus]

I will try this tomorrow.

Thanks