Solved

Date Range - check against existing dates already entered

Posted on 2004-08-11
4
1,077 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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