I have several records in a database that have Start and End Dates
09/15/2011 - 09/30/2011 10/15/2011 - 10/22/2011 11/01/2011 - 11/15/2011
When user stores a record, I need to make sure dates don't overlap. My simple code checks date ranges within a specific record (e.g. user enters 9/16/2011 or 10/21/2011, I throw an exception.)
But, on the slim chance a user gets creative (e.g. 10/14/2011 - 10/23/2011 or even 10/14/2011 to 11/16/2011), now they have circumvented my check.
BTW, the user could enter 10/14/2011 to 10/23/2011 if they were editing the record that contained values 10/15/2011 - 10/22/2011.
So, I'm trying to solve this riddle with a linq query. However, what I have isn't working exactly right.<hr />
<strong>UPDATE</strong> Nevermind about code not working. While trying to provide an example to expand on Miika's repsonse, I found my answer. So, giving credit to Miika for pointing me in the right direction and posting my working code below:
Here's my code:
Private Sub CheckForOverlap(myMonth As Messages.MyMonth) Dim am As New MyMonth() Dim amCollection As Messages.MyMonthCollection Dim overlappingMyMonthDate As Boolean = False Dim sErrorMsg As String = "" '...non-applicable code omitted Dim query = From s In amCollection _ Let s1 As MyMonth = CType(s, MyMonth) _ Where s1.AttendanceMonthID <> attendanceMonth.AttendanceMonthID And _ (CDate(attendanceMonth.StartDate) < CDate(s1.StartDate) And CDate(attendanceMonth.EndDate) > CDate(s1.EndDate)) _ Select s1 If query.Count > 0 Then sErrorMsg = "Dates entered surround another entry" End If If overlappingMyMonthDate Then Throw New Exception(sErrorMsg) End If End Sub End Class
It all came down a LINQ query.Answer1:
Do you need to do it in code or would SQL be an option? If the data is in a database, you could use the following query to check for overlaps.
SELECT COUNT(*) FROM Table1 WHERE Table1.StartDate < 'endCheckDate' AND Table1.EndDate > 'startCheckDate'
This will return a count of the number of overlaps found. 'endCheckDate' and 'startCheckDate' are your new query values (in date format). If your data is in a object collection in memory, then you could use LINQ. If you need help with a LINQ statement, let me know.