2. ASPRunnerPro General questions
  3. Check if specific Record exists in a Date Range
This topic is locked

Check if specific Record exists in a Date Range

 		11/12/2010 8:10:43 AM
ASPRunnerPro General questions
S
 sunnynayak author

Hi,
I'm using version ASPRunner Pro 6.2 Built 5242.
At ADD page, Before record added, how do I check for specific records exists? Basically, I want to prevent a user from booking a Room when someone else already added the same booking date range. The simple scripting I can think of is:
** Check if specific record exists ****

dim rsExists, rsExists1, rsExists2

set dal_table=dal.Table("batchD")

set rsExists = dal_table.Query("startDate>='startDate'","")

set rsExists1 = dal_table.Query("endDate>='endDate'","")

set rsExists2 = dal_table.Query("trRoom='trRoom'","")

if not rsExists.eof and not rsExists1.eof and not rsExists2.eof then

' if record exists do something
else

' if dont exist do something else
end if

rsExists.Close : set rsExists = Nothing

rsExists1.Close : set rsExists1 = Nothing

rsExists2.Close : set rsExists2 = Nothing
Please help to provide a sample code to achieve this.
The table is "batchD" and the column names are "startDate" "endDate" and "trRoom".
Thank you.
Sergey Kornilov admin 11/12/2010

Try something like this:

set dal_table=dal.Table("batchD")

set rsExists = dal_table.Query(" (startDate<='" & values("startDate") & "' and endDate>='" & values("startDate") & "' or startDate<='" & values("endDate") & "' and endDate>='" & values("endDate") & "' or startDate>='" & values("startDate") & "' and endDate<='" & values("startDate") & "') and trRoom=" & values("trRoom"))

if not rsExists.eof then

' if record exists do something
else

' if dont exist do something else
end if

rsExists.Close : set rsExists = Nothing


I assume trRoom is a numeric field and no single quotes are required around room number.
SQL query looks complicated because we need to cover the following situations:

  • entered start date is between start date and end date of one of existing records
  • entered end date is between start date and end date of one of existing records
  • entered date interval overlaps with date interval one of existing records
    Given the complexity of this method I'd suggest to provide a visual way to select date range. Just imagine someone entering dates over and over again and not being able to see what's already booked.