Hello All, I'm developing a room reservation calendar. I have written code which will prevent user from reserving a booked room for a one time event. However, they will have recurring events, and that's where I get stuck.
Example: User is trying to reserve Room A for Mondays & Wednesdays, at 2pm - 6pm, from 5/7/2012 - 6/7/2012, but there's already a meeting on 5/14/2012 at 3pm in Room A, and I'd like to return a message of "Room not available for dates & times requested."
Any help is appreciated. Please see code below.
Protected Sub lblChkAvail_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lblChkAvail.Click
If cbxRecur.Checked = True And txtEndDate.Text = "" Then
Err(Page, "Enter End Date.")
Exit Sub
End If
'Add the event to the database'
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("csINTRANET").ConnectionString)
Dim myCommand As New SqlCommand("uspRoom_ChkAvailability", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim myDataReader As SqlDataReader
'RECURRING EVENTS'
If cbxRecur.Checked = True Then
'used to keep incrementing days'
Dim currDate As Date
'store DayOfWeek value'
Dim aryDays As New ArrayList()
'' ''Trying to convert the arraylist to string.
' ''Dim strDays As String() = TryCast(aryDays.ToArray(GetType(String)), String())'
'store array of CalEvents structs '
Dim aryEvents As New ArrayList()
'TODO: properly parse date and time make time text boxes into drop down lists for user to select'
Dim startTime As String = txtStartTime.Text
Dim endTime As String = txtEndTime.Text
Dim startDateRange As DateTime = CDate(txtStartDate.Text).ToShortDateString
Dim endDateRange As DateTime = CDate(txtEndDate.Text).ToShortDateString
currDate = startDateRange.ToShortDateString
'store days of week event should be on'
For i As Integer = 0 To cbxRecurDay.Items.Count - 1
If cbxRecurDay.Items(i).Selected Then
Select Case cbxRecurDay.Items(i).Value
Case "Sunday"
aryDays.Add(DayOfWeek.Sunday)
Exit Select
Case "Monday"
aryDays.Add(DayOfWeek.Monday)
Exit Select
Case "Tuesday"
aryDays.Add(DayOfWeek.Tuesday)
Exit Select
Case "Wednesday"
aryDays.Add(DayOfWeek.Wednesday)
Exit Select
Case "Thursday"
aryDays.Add(DayOfWeek.Thursday)
Exit Select
Case "Friday"
aryDays.Add(DayOfWeek.Friday)
Exit Select
Case "Saturday"
aryDays.Add(DayOfWeek.Saturday)
Exit Select
Case Else
Exit Select
End Select
'Response.Write((cbxRecurDay.Items.Count - 1) & "<br />")
End If
Next
'TODO: add logic to check and make sure startDateRange is equal or greater than today's date
'Also check to make sure endDateRange is no more than 12 months out
While (currDate >= startDateRange) AndAlso (currDate <= endDateRange)
Dim evt As New CalEvents()
For i As Integer = 0 To aryDays.Count - 1
If currDate.DayOfWeek = DirectCast(aryDays(i), DayOfWeek) Then
evt.evRecurDay = aryDays(i).ToString
evt.evSDate = currDate.Date.ToShortDateString
evt.evEDate = txtEndDate.Text
evt.evSTime = startTime.ToLower
evt.evETime = endTime.ToLower
evt.evName = txtTitle.Text
evt.evLocation = ddnLocation.Text
evt.evDiv = ddnDiv.SelectedValue
evt.evStaff = txtStaff.Text
evt.evNotes = txtNotes.Text
evt.evRecurring = cbxRecur.Checked
evt.evRecurDay = DirectCast(aryDays(i), DayOfWeek).ToString
evt.evCreatedBy = Session("uID")
evt.evCreatedDate = txtCreatedOn.Text
aryEvents.Add(evt)
End If
'Response.Write(aryEvents)'
Next
currDate = currDate.AddDays(1)
End While
For i As Integer = 0 To aryEvents.Count - 1
Dim evt As New CalEvents()
evt = DirectCast(aryEvents(i), CalEvents)
myCommand.Parameters.Clear()
'debugging purpose only'
Response.Write(String.Format("{0} <br />", evt.evName & ", " & evt.evRecurDay & ", " & evt.evSDate))
Dim room As New SqlParameter("@ROOM", SqlDbType.NVarChar)
room.Value = ddnLocation.SelectedValue
myCommand.Parameters.Add(room)
Dim SDate As New SqlParameter("@STARTDATE", SqlDbType.DateTime, 8)
SDate.Value = txtStartDate.Text
myCommand.Parameters.Add(SDate)
Dim STime As New SqlParameter("@STARTTIME", SqlDbType.DateTime)
STime.Value = CDate("1900-1-1 " & txtStartTime.Text & ":00 " & ddnStartAMPM.SelectedValue)
myCommand.Parameters.Add(STime)
Dim ETime As New SqlParameter("@ENDTIME", SqlDbType.DateTime)
ETime.Value = CDate("1900-1-1 " & txtEndTime.Text & ":00 " & ddnEndAMPM.SelectedValue)
myCommand.Parameters.Add(ETime)
myConnection.Open()
myDataReader = myCommand.ExecuteReader
myDataReader.Read()
If myDataReader.HasRows Then
tblAvail.Visible = True
rowAvail.Style.Add("background-color", "Red")
rowAvail.Style.Add("color", "white")
lblAvailability.Text = "Room not available for date(s) and time(s) requested."
colRecur.Attributes.Add("class", "shown")
Exit Sub
Else
ddnLocation.Enabled = False
cbxRecur.Enabled = False
txtStartDate.Enabled = False
txtStartTime.Enabled = False
txtEndTime.Enabled = False
ddnStartAMPM.Enabled = False
ddnEndAMPM.Enabled = False
tblAvail.Visible = True
rowAvail.Style.Add("background-color", "Green")
rowAvail.Style.Add("color", "white")
lblAvailability.Text = "Room available. Proceed with reservation."
colRecur.Attributes.Add("class", "shown")
ddnDiv.Enabled = True
txtTitle.Enabled = True
txtStaff.Enabled = True
txtNotes.Enabled = True
End If
myDataReader.Close()
myConnection.Close()
Next
End If
End Sub
Mike Vytal
Member
122 Points
100 Posts
Prevent double booking...
May 04, 2012 12:03 AM|LINK
Hello All, I'm developing a room reservation calendar. I have written code which will prevent user from reserving a booked room for a one time event. However, they will have recurring events, and that's where I get stuck. Example: User is trying to reserve Room A for Mondays & Wednesdays, at 2pm - 6pm, from 5/7/2012 - 6/7/2012, but there's already a meeting on 5/14/2012 at 3pm in Room A, and I'd like to return a message of "Room not available for dates & times requested." Any help is appreciated. Please see code below.
Protected Sub lblChkAvail_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lblChkAvail.Click If cbxRecur.Checked = True And txtEndDate.Text = "" Then Err(Page, "Enter End Date.") Exit Sub End If 'Add the event to the database' Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("csINTRANET").ConnectionString) Dim myCommand As New SqlCommand("uspRoom_ChkAvailability", myConnection) myCommand.CommandType = CommandType.StoredProcedure Dim myDataReader As SqlDataReader 'RECURRING EVENTS' If cbxRecur.Checked = True Then 'used to keep incrementing days' Dim currDate As Date 'store DayOfWeek value' Dim aryDays As New ArrayList() '' ''Trying to convert the arraylist to string. ' ''Dim strDays As String() = TryCast(aryDays.ToArray(GetType(String)), String())' 'store array of CalEvents structs ' Dim aryEvents As New ArrayList() 'TODO: properly parse date and time make time text boxes into drop down lists for user to select' Dim startTime As String = txtStartTime.Text Dim endTime As String = txtEndTime.Text Dim startDateRange As DateTime = CDate(txtStartDate.Text).ToShortDateString Dim endDateRange As DateTime = CDate(txtEndDate.Text).ToShortDateString currDate = startDateRange.ToShortDateString 'store days of week event should be on' For i As Integer = 0 To cbxRecurDay.Items.Count - 1 If cbxRecurDay.Items(i).Selected Then Select Case cbxRecurDay.Items(i).Value Case "Sunday" aryDays.Add(DayOfWeek.Sunday) Exit Select Case "Monday" aryDays.Add(DayOfWeek.Monday) Exit Select Case "Tuesday" aryDays.Add(DayOfWeek.Tuesday) Exit Select Case "Wednesday" aryDays.Add(DayOfWeek.Wednesday) Exit Select Case "Thursday" aryDays.Add(DayOfWeek.Thursday) Exit Select Case "Friday" aryDays.Add(DayOfWeek.Friday) Exit Select Case "Saturday" aryDays.Add(DayOfWeek.Saturday) Exit Select Case Else Exit Select End Select 'Response.Write((cbxRecurDay.Items.Count - 1) & "<br />") End If Next 'TODO: add logic to check and make sure startDateRange is equal or greater than today's date 'Also check to make sure endDateRange is no more than 12 months out While (currDate >= startDateRange) AndAlso (currDate <= endDateRange) Dim evt As New CalEvents() For i As Integer = 0 To aryDays.Count - 1 If currDate.DayOfWeek = DirectCast(aryDays(i), DayOfWeek) Then evt.evRecurDay = aryDays(i).ToString evt.evSDate = currDate.Date.ToShortDateString evt.evEDate = txtEndDate.Text evt.evSTime = startTime.ToLower evt.evETime = endTime.ToLower evt.evName = txtTitle.Text evt.evLocation = ddnLocation.Text evt.evDiv = ddnDiv.SelectedValue evt.evStaff = txtStaff.Text evt.evNotes = txtNotes.Text evt.evRecurring = cbxRecur.Checked evt.evRecurDay = DirectCast(aryDays(i), DayOfWeek).ToString evt.evCreatedBy = Session("uID") evt.evCreatedDate = txtCreatedOn.Text aryEvents.Add(evt) End If 'Response.Write(aryEvents)' Next currDate = currDate.AddDays(1) End While For i As Integer = 0 To aryEvents.Count - 1 Dim evt As New CalEvents() evt = DirectCast(aryEvents(i), CalEvents) myCommand.Parameters.Clear() 'debugging purpose only' Response.Write(String.Format("{0} <br />", evt.evName & ", " & evt.evRecurDay & ", " & evt.evSDate)) Dim room As New SqlParameter("@ROOM", SqlDbType.NVarChar) room.Value = ddnLocation.SelectedValue myCommand.Parameters.Add(room) Dim SDate As New SqlParameter("@STARTDATE", SqlDbType.DateTime, 8) SDate.Value = txtStartDate.Text myCommand.Parameters.Add(SDate) Dim STime As New SqlParameter("@STARTTIME", SqlDbType.DateTime) STime.Value = CDate("1900-1-1 " & txtStartTime.Text & ":00 " & ddnStartAMPM.SelectedValue) myCommand.Parameters.Add(STime) Dim ETime As New SqlParameter("@ENDTIME", SqlDbType.DateTime) ETime.Value = CDate("1900-1-1 " & txtEndTime.Text & ":00 " & ddnEndAMPM.SelectedValue) myCommand.Parameters.Add(ETime) myConnection.Open() myDataReader = myCommand.ExecuteReader myDataReader.Read() If myDataReader.HasRows Then tblAvail.Visible = True rowAvail.Style.Add("background-color", "Red") rowAvail.Style.Add("color", "white") lblAvailability.Text = "Room not available for date(s) and time(s) requested." colRecur.Attributes.Add("class", "shown") Exit Sub Else ddnLocation.Enabled = False cbxRecur.Enabled = False txtStartDate.Enabled = False txtStartTime.Enabled = False txtEndTime.Enabled = False ddnStartAMPM.Enabled = False ddnEndAMPM.Enabled = False tblAvail.Visible = True rowAvail.Style.Add("background-color", "Green") rowAvail.Style.Add("color", "white") lblAvailability.Text = "Room available. Proceed with reservation." colRecur.Attributes.Add("class", "shown") ddnDiv.Enabled = True txtTitle.Enabled = True txtStaff.Enabled = True txtNotes.Enabled = True End If myDataReader.Close() myConnection.Close() Next End If End SubThanx,
M2
**Why curse the darkness, when you can light a candle...**
Qin Dian Tan...
All-Star
113532 Points
12480 Posts
Microsoft
Re: Prevent double booking...
May 08, 2012 08:10 AM|LINK
Hi,
I suggest you use SqlDataSource control with setting ConflictDetection:
http://www.asp.net/web-forms/tutorials/data-access/accessing-the-database-directly-from-an-aspnet-page/implementing-optimistic-concurrency-with-the-sqldatasource-vb
Thanks,
If you have any feedback about my replies, please contactmsdnmg@microsoft.com.
Microsoft One Code Framework