www.webdeveloper.com
Results 1 to 7 of 7

Thread: restrict insert based on range of dates

  1. #1
    Join Date
    May 2005
    Posts
    242

    restrict insert based on range of dates

    ok, see if you can follow me

    have a reservation form that asks for start and ending dates. both are seperate textfields and database fields. i need to be able to restrict users from submitting starting and ending dates if these dates already exist in database. that part is easy enough...what i can't figure out is how to restrict starting/ending dates that fall on the interim dates of existing reservations...example:

    user1: start date to end date - 7/1/2005 to 7/10/2005
    user2: start date to end date - 7/2/2005 to 7/9/2005

    user2 should fail since his days fall inbetween user1's reservations. make sense??

    i've tried using between or not between - no success. even tried subtracting the start date from end date and then create an array using a for/next loop to build the interim dates by adding the num of days back to the start date as it loops thru. kinda seemed like i was onto something but hit a dead end and started confusing myself !!

    thanks in advance, kelly

  2. #2
    Join Date
    Apr 2005
    Location
    kali
    Posts
    308
    insert (column1,column2) values (value1,value2) where not (value1 between column1 and column2) and not (value2 between column1 and column2)

    off hand thats what i used before. it should work. if not, post back your exact query and ill look around to find the query that i used before.

  3. #3
    Join Date
    May 2005
    Posts
    242
    thanks, the code makes sense to me but how would i implement it? how do return an error to the user if the values can't be inserted? i've been using COUNT(ID) AS BLAH to see if a record exists and firing an if/then statement based on its existence or non-existence...

    if BLAH = 1 then "tell user to fix something", if BLAH = 0 then insert the reservation and all is well.

    here is what i've done with your code:
    Code:
    sql666="insert into marquee (date_requested,end_date) values ('" & date_requested & "','" & end_date & "')"
    sql666= sql666 + "where not ('" & date_requested & "' between date_requested AND end_date) and not ('" & end_date & "' between date_requested AND end_date)"
    Last edited by kredd; 06-01-2005 at 05:12 PM. Reason: your modified code added

  4. #4
    Join Date
    May 2005
    Posts
    242
    oh yeah, the important part....i submitted starting and ending dates that fall between two existing dates and unfortunatley it successfully inserted the reservation. so something's not working right...probably my modification

  5. #5
    Join Date
    Apr 2005
    Location
    kali
    Posts
    308
    uhhhh -- nevermind. wasnt paying enough attention earlier. inserts dont take the where clause. use that between setup in a select statement that will return no rows if you are clear to insert or it will return rows if its not clear. handle the error/routing from there.

  6. #6
    Join Date
    May 2005
    Posts
    242
    i didn't think they did, just thot it was something i hadn't heard of before.

    be a lot cooler if it did thnks

  7. #7
    Join Date
    May 2005
    Posts
    242
    just finished the project...the 'between code' fit perfectly into my existing checking statements and worked great...thanks for the help

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles