Solved

Date Range - check against existing dates already entered

Posted on 2004-08-11
4
931 Views
Last Modified: 2008-01-09
Hi all

adding a record to items table, need to check that the item added does not overlap any date ranges already entered.
(See table records below)
At the moment I am not allowing the user enter any date i.e. 12/08/2004 for 7 nights
However the user is still allowed enter i.e 24/08/2004 for 7 nights and overlap item.id=3
How can i check that the user does not enter any date range already created??
*note -> fixdate() function converts date from (10/08/2004 to 10/Aug/2004) format

- items table -
id   motorid   itemdate     numnights
1    2        10/08/2004   7      
2    2        17/08/2004   7
3    2        27/08/2004   7

if itemdate="" then
    errormessage=errormessage & "You must specify a Date.<br>"
else
    'Check to see if itemdate already exists when adding.
if task="add" or task="update" then
Set objRS = Server.CreateObject("ADODB.Recordset")
sql = "("
For i=1 TO cInt(numnights)
     newDate = DateAdd("d",i,fixdate(itemdate))
     sql = sql & "('" & fixdate(newDate) & "' between itemdate AND DateAdd(day,numnights,itemdate)) OR "
Next
sql = LEFT(sql,Len(sql)-3) & ")"
if sql <> "" Then
     sql = "Select * From items WHERE motorid='" & motorid & "' AND " & sql
Else
     sql = "Select * From items WHERE motorid='" & motorid & "'"
End if
objRS.open sql, db, 3,1
response.write sql
if not objRS.EOF Then
   errormessage=errormessage & "You have already choosen a Holiday within this date range!"
End if
objRS.close
set objRS=nothing      
end if
0
Comment
Question by:fgict
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
sciber_dude earned 500 total points
ID: 11777079
> However the user is still allowed enter i.e 24/08/2004 for 7 nights and overlap item.id=3

Why is this so? what is the logic that the user can enter 24/8/2004 and not any other dates? If you can give me the answer for this, i will try to work this in.

if itemdate="" then
      errormessage=errormessage & "You must specify a Date.<br>"
else
      'Check to see if itemdate already exists when adding.
      if task="add" or task="update" then
            Set RsDates = Server.CreateObject("ADODB.Recordset")
      
            sqlDates = "Select * from items where motorid='" & motorid & "'"
            RsDates.open sql, db, 3,1

            While not (RsDates.EOF or RsDates.BOF)
                  sql = sql & "(itemdate > #" & RsDates("itemdate") & "#) AND (itemdate < #" & DateAdd ("d", RsDates("numnights"), RsDates("itemdate")) & "#)) OR "
            Wend

            RsDates.close
            set RsDates=nothing

            sql = "(" & LEFT(sql,Len(sql)-3) & ")"
            if sql <> "()" Then
                  sql = "Select * From items WHERE motorid='" & motorid & "' AND " & sql
            Else
                  sql = "Select * From items WHERE motorid='" & motorid & "'"
            End if

            Set objRS = Server.CreateObject("ADODB.Recordset")

            objRS.open sql, db, 3,1
            response.write sql & "<br>"

            if not objRS.EOF Then
                   errormessage=errormessage & "You have already choosen a Holiday within this date range!"
            End if
            response.write errormessage & "<br>"

            objRS.close
            set objRS=nothing
      end if                        ' << you were missing this.
end if


:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11777091
Btw, if you are not using MS Access Database, change the # (pound sign) to a ' (single quote) in this line

sql = sql & "(itemdate > #" & RsDates("itemdate") & "#) AND (itemdate < #" & DateAdd ("d", RsDates("numnights"), RsDates("itemdate")) & "#)) OR "
0
 
LVL 1

Author Comment

by:fgict
ID: 11782513
Hi SD

thanks for your response, i want to disallow the user i.e entering 24/08/2004 for 7 nights and overlap item.id=3
The user could enter 24/08/2004 for 3 nights but could not overlap  a date range already inputted i.e id=3 motorid=2 itemdate=27/08/2004 numnightd=7
Using SQL Server database

hope this explains :)
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11790004
Did u try the code i gave you?

Make sure you replace the "#" with a single quote.

:) SD
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now