August 26, 2010 at 8:13 am
The following code will only return "No birthday(s) Pending". Any advice on correcting code would be appreciated.
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
September 4, 2010 at 4:16 am
Found error.
September 4, 2010 at 7:40 am
kenkob (9/4/2010)
Found error.
Great!
Now, would you mind posting what therror was so others might benefit?
September 4, 2010 at 9:30 pm
kenkob (8/26/2010)
The following code will only return "No birthday(s) Pending". Any advice on correcting code would be appreciated.
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
Whether you fixed the code or not, the biggest problem is... embedded SQL.
The second biggest problem is... the embedded SQL calculation is totally wrong for the proper calculation of age in years.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2010 at 4:13 am
The following corrected code is used just to calculate the days to the next birthday only from dates within a SqlCe database, it is not being used to calculate the age in years. As I am using Sql Compact Edition I do not know of any other method to incorporate Sql except in code. I would therefore be pleased to see your updated version of my code without embedded Sql and exactly where the Sql would be called from. The code below now does what I expected it to do. It may not be the best way, certainly it is not the only way, it is my way and part of my learning curve.
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
Public strWarnings As StringBuilder
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 * FROM Persons ORDER BY Dob" 'Surname, P.Firstname, P.Dob, " &
PersonsCommand = New SqlCeCommand(Sql, cn)
PersonsAdapter = New SqlCeDataAdapter()
PersonsAdapter.SelectCommand = PersonsCommand
PersonsTable = New DataTable()
Dim ds As New DataSet
PersonsAdapter.Fill(ds)
dgv.DataSource = ds.Tables(0)
strWarnings = New StringBuilder
With tmrReminders
.Interval = 1000 * 60 * 20
End With
CheckRenewals()
End Sub
Private Sub CheckRenewals()
With tmrReminders
.Enabled = False
PersonsAdapter.Fill(PersonsTable)
'Get Birthday and Anniversary dates
Dim PersonDatesView As DataView = PersonsTable.DefaultView
With PersonDatesView
If .Count > 0 Then
Dim bFoundBirthdaysToRemember As Boolean = False
Dim sBirthdayReminders As String = vbNullString
For BirthdayCheckCounter As Integer = 0 To .Count - 1
With .Item(BirthdayCheckCounter)
Dim PersonBirthday As Date = CType(.Item("Dob"), Date)
Dim PersonBirthDate As Date = CType(PersonBirthday.Day & "/" & PersonBirthday.Month & "/" & Now.Year, Date)
Dim NoOfDays As Long = DateDiff(DateInterval.Day, Now, PersonBirthDate) + 1
'Test For Dob
If NoOfDays < 10 Then
If NoOfDays > -10 And NoOfDays < 0 Then
ElseIf NoOfDays > -1 Then
bFoundBirthdaysToRemember = True
If sBirthdayReminders <> vbNullString Then _
sBirthdayReminders &= vbCrLf
Dim DayString As String = IIf(NoOfDays = 1, " day", " days").ToString
sBirthdayReminders &= .Item("Firstname").ToString.Trim & " " & _
"'s Birthday due in : " & _
NoOfDays.ToString & DayString
End If
End If
End With
Next
If (bFoundBirthdaysToRemember) Then
With niReminders
.Visible = True
If bFoundBirthdaysToRemember Then
.BalloonTipIcon = ToolTipIcon.Info
.BalloonTipText = sBirthdayReminders
.BalloonTipTitle = "Cards Due for Action"
End If
.ShowBalloonTip(15)
End With
End If
End If
End With
.Enabled = True
End With
End Sub
End Class
September 6, 2010 at 1:49 pm
kenkob (9/5/2010)
It may not be the best way, certainly it is not the only way, it is my way and part of my learning curve.
I agree and no personal attack was intended. I intend to add to your "learning curve". 🙂
I'm not sure of everything that your code does but I do know you don't need embedded SQL to return a list of persons who's birthday comes up sometime in the next 10 days including today (you can easily change that in the BETWEEN in the code that follows).
The following code can easily be turned into a View, Stored Procedure, or Inline Table Valued Function for use by GUI code or other SQL objects... this particular example goes against the AdventureWorks database included with SQL Server 2005.
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
That will cause a table scan just like your code did. Someone else may be able to turn it into a "sargeable" statement but I just wanted you to see how simple things can be.
Each DATEADD in the code strips out the year element so the date occurs in 1900 which puts both dates on equal footing. Then, the DATEDIFF simply takes the number of days between the two and it's done.
Your GUI code would only need to open the result set, then. Of course, you'd need to add an ORDER BY for your code and I didn't here because I didn't want to confuse the matter with a JOIN. I just wanted you to concentrate on the date stuff.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2010 at 3:55 am
Jeff,
Thank you for your response, I was not offended by your comments, maybe I expressed my response badly. Thank you for the code sample.
My code was attempting to calculate and inform the user via a NotifyIcon when any Birthday would be due within 10 days. The display of other data was part of my learning curve.
I will now endeavor to rewrite my code using the sample you have shown without displaying unnecessary information.
By the way, what is a 'sargeable' statement.
September 7, 2010 at 12:27 pm
For extra credit, first define your Leap Day Baby rule, and then implement it correctly. For instance, the rule I most commonly use is Feb 29th birthdays fall on Feb 29th in years where it exists, and on Feb 28th otherwise. Another rule that's just as valid is use Feb 29th in years where it exists, and Mar 1st otherwise.
For credit, first implement it, and then figure out what Leap Day Baby rule you happened to implement, and document that.
September 7, 2010 at 12:47 pm
The correct way is to celebrate a Feb 29th birthday on the 28th, that's how the Roman (Catholic) calendar does it. In fact, it is not the 29th that is added on leap years: it is a second Feb 24th and the following four days of February are "bumped up."
September 7, 2010 at 12:50 pm
Revenant (9/7/2010)
The correct way is to celebrate a Feb 29th birthday on the 28th, that's how the Roman (Catholic) calendar does it. In fact, it is not the 29th that is added on leap years: it is a second Feb 24th and the following four days of February are "bumped up."
Just out of curiousity: any source available for that? (Never heard about it before...)
September 7, 2010 at 12:52 pm
Revenant (9/7/2010)
The correct way is to celebrate a Feb 29th birthday on the 28th, that's how the Roman (Catholic) calendar does it. In fact, it is not the 29th that is added on leap years: it is a second Feb 24th and the following four days of February are "bumped up."
The correct way to calculate age is to follow whatever regulations affect your industry or the operational definition of "age" specific to your business goal.
In the U.S., at least, different agencies calculate leap day baby ages differently.
September 7, 2010 at 1:24 pm
Of course. I should have said "In absence of any industry rules or guidelines, ..."
However, the Common Law still says that if a debt is due on February 24th of a leap year, it may be paid on 25th (the second, inserted 24th).
September 10, 2010 at 5:32 am
Jeff Hi,
No matter what I try, I cannot get your code snippet to work, is there any possibilty you could expand it to show its used with code and using northwind.sdf. If I take out the --Today shown in Green in your code and having assumed it may be a comment it tells me the function Year is not recognised by the Compact Edition. When left in it gives an error parsing the query at Line 1 position 166 error = ,
But I cannot get rid of the error.
September 10, 2010 at 5:35 am
Jeff Hi,
No matter what I try, I cannot get your code snippet to work, is there any possibilty you could expand it to show its used with code and using northwind.sdf. If I take out the --Today shown in Green in your code and having assumed it may be a comment it tells me the function Year is not recognised by the Compact Edition. When left in it gives an error parsing the query at Line 1 position 166 error = ,
But I cannot get rid of the error.
September 10, 2010 at 5:42 am
Sorry, Ken... I was a bit asleep at the switch and forgot this was for the compact edition (heh... "CE" actually stands for "Crippled Edition", doesn't it? :-P)
There's more than one way to do the same thing so, let's check...
What do you get in CE when you run each of these individually?
SELECT YEAR(GETDATE())
SELECT DATEPART(yy,GETDATE())
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply