ASP code that will limit how many people can sign up for a particular appointment

[mkelly]

I am creating a dreamweaver asp form that will be published on our website. My intent is for students to look at a list of dates and times and sign up for one of them. Once a student has signed up for a date, that date will no longer appear on the form. THe data will dump into access. So far I have a form that Allows a person to sign up for a particular date, but I do not know how to make that date read the database and realize it is taken and no display it or atleast not allow someone else to sign up on the same date. I am very new at asp. So far I have done okay, but now I am lost. Thank you for any help!

Michelle

[jhardman]

first off, in your db set the date column to be unique (in other words, the db will not allow two entries with identical values in that field). It is easy then to catch an error and display "This date has been taken by someone else".

second, it might be easier to keep a list of open dates, and when one is selected, remove that date from the list. Otherwise, I would say you have to loop through the appointments already set in order at the same time you are looping through the dates you are displaying, and only display the dates that don't match. Does this make sense? Do you need help with the coding? If so let me know which method you want to use.

Jared

[mkelly]

@jhardman
Jared, I think that your first option sounds the easiest. I have selected the unique option in my Database and now it will not let you select a date that has already been taken. The problem is that if you do select a date it gives you an error that wouldn't make sense to the user. Can you help me with the ASP coding so that it will display "That date has been taken by someone else". Thank you so much for you help.
Michelle

[jhardman]

@mkelly
first you need to let the script engine know that you are prepared to handle errors (this needs to be above the line that generates the error):

on error resume next

then you need to detect whether an error occurred (this needs to be after the line that will cause the error):

if err.number <> 0 then

this will of course just detect ANY error, so might not be the best choice, but you could probably figure out what error number a duplicate appointment would make, right? just response.write the err.number. Anyway, the last thing you need to do is handle the error (this goes immediately after the if line above):

   response.write "This appointment date has already been chosen"
   error.clear 'resets the error object so you can use it again
end if

Let me know if this helps.

Jared

[mkelly]

@jhardman
Jared, thank you so much for you help. This was a quick fix to my problem. I would love to see the coding for looping through dates and only seeing the dates that don't match. This seems well out of my knowledge base. If you have time, I would appreciate seeing this code so that I can possibly use it for future projects. Again, thank you so much!!
Michelle

[jhardman]

OK, here is a simple code that uses nested loops to check the values of dates against each other. I simplified the code as much as possible, but it can be easily adjusted to check values in a db. Hope this helps.

<%
dim x, dateArray, i, apptDate
i = 0
dateArray = split("5/6/2010 11:00:00 AM,5/6/2010 2:00:00 PM,5/6/2010 5:00:00 PM",",")
for x = 0 to 23
   apptDate = dateadd("h", x, "5/6/2010 8:00:00 AM")
 
   do while cdate(dateArray(i)) < apptDate
      i = i + 1
      if i > ubound(dateArray) then 
         i = ubound(dateArray)
         exit do
      end if
   loop
 
   if apptDate = cdate(dateArray(i)) then
      response.write "Appointment taken<br>" & vbNewLine
   else
      response.write apptDate & "<br>" & vbNewLine
   end if
 
next
%>

[mkelly]

Jared, I have entered this code in just as it is in my form, but keep getting a cdate error. As I said in my first post. I have just started designed asp forms in the last month or so and have no knowledge or training. I am attaching my code to see if that will help. I can not thank you enough for your help.

</head>
<!--#include virtual="includes/protect.asp"-->
<!--#include file="../connections/dbcon.asp"-->
<%
  'this code block puts the data into the DB if the Submit button was clicked and the form validated.
  Submitted = False ' we'll use this below to determine if we display the form, or a message to the user.
  if request.form("Submit") <> "" then  'If the form was submitted, add the registration, otherwise all this code is skipped.
     set rsRegistration = server.CreateObject("ADODB.Recordset") 'create the recordset to add a record
     rsRegistration.activeconnection = con 'set up the connection, defined in dbcon.asp included above.
     rsRegistration.cursorlocation = 3  'always use these settings
     rsRegistration.cursortype = 3
     rsRegistration.locktype = 3  'only needed if DB is to be updated, otherwise skip it
     rsRegistration.source = "SELECT Top 1 * FROM Appointments" 'the query will bring in one record as a 'template' for the add.
     rsRegistration.open
     'response.write("recordcount = " & rsRegistration.recordcount)
     'if rsRegistration.recordcount > 0 then 'make sure we got at least one record.
     if rsRegistration.recordcount > 0 then 'make sure we got at least one record.
        rsRegistration.addnew 'add a new record
        for each obj in rsRegistration.fields  'this loops through all the fields in the record.
           if obj.name <> "ID" then  'since ID is autonumber, we can't assign a value to it.
               dim x, dateArray, i, apptDate
i = 0
dateArray = split("5/6/2010 11:00:00 AM,5/6/2010 2:00:00 PM,5/6/2010 5:00:00 PM")
for x = 0 to 23
   apptDate = dateadd("h", x, "5/6/2010 8:00:00 AM")
 
   do while cdate(dateArray(i)) < apptDate
      i = i + 1
      if i > ubound(dateArray) then 
         i = ubound(dateArray)
         exit do
      end if
   loop
 
      response.write "<!-- x = " & x & vbNewLine
      response.write "apptDate = " & apptDate & vbNewLine
      response.write "i = " & i & vbNewLine
      response.write "dateArray(i) = " & dateArray(i) & " -->" & vbNewLine
 
   if apptDate = cdate(dateArray(i)) then
      response.write "Appointment taken<br>" & vbNewLine
   else
      response.write apptDate & "<br>" & vbNewLine
   end if
   next
 
               rsRegistration(obj.name) = request.form(obj.name) 'assign the value of the form field to the recordset field
           end if
 
           Next
        rsRegistration.update 'sends the new record to the DB.
        Submitted = True
        end if
        rsRegistration.close  'close the connection to the data source - IMPORTANT to ALWAYS do this
     set rsRegistration = nothing  'remove the recordset from teh server's memory, also very important.
  end if
 
%>
 
<body>
<% IF NOT Submitted Then 'display the form if not submited %>
<div id="cform">
<form name="Appointments" method="post" action="Appointments.asp">
<fieldset>
 
    <legend>International Services Appointment</legend>
 
        <label for="UIN" class="required">UIN: <em>*</em></label>
 
      <span id="sprytextfield1">
      <input type="text" name="UIN" id="UIN" />
      <span class="textfieldRequiredMsg">UIN is required.</span><span class="textfieldInvalidFormatMsg">UIN is 9 numbers begining with 814</span></span><br />
      <label for="FirstName" class="required">First Name: <em>*</em></label>
      <span id="sprytextfield2">
      <input type="text" name="FirstName" id="FirstName" />
      <span class="textfieldRequiredMsg">First Name is required.</span></span><br />
      <label for="LastName" class="required">Last Name: <em>*</em></label>
      <span id="sprytextfield3">
      <input type="text" name="LastName" id="LastName" />
      <span class="textfieldRequiredMsg">Last Name is required.</span></span><br />
      <label for="AppointmentType" class="required">What do you wish to discuss at your appointment? <em>*</em></label><br />
    <span id="spryselect1">
    <select name="AppointmentType" id="AppointmentType">
      <option value="">please make a selection</option>
        <option value="Study Abroad">Study Abroad</option>
        <option value="OPT/CPT">OPT/CPT</option>
        <option value="Change of status">Change of status</option>
        <option value="Extension">Extension</option>
        <option value="Other immigration issues">Other immigration issues</option>
        <option value="Tax questions">Tax questions</option>
        <option value="Social Security Card">Social Security Card</option>
    </select>
      <span class="selectRequiredMsg">Please select an item.</span></span><br />
 
<label for="Appointment" class="required">Appointment Time: <em>*</em></label>
      <span id="spryselect2">      
      <select name="Appointment" id="Appointment">
        <option value="">Select an Appointment Time</option>
        <option value="5/6/2010 11:00:00 AM">5/6/2010 11:00:00 AM</option>
        <option value="5/6/2010 2:00:00 PM">5/6/2010 2:00:00 PM</option>
        <option value="5/6/2010 5:00:00 PM">5/6/2010 5:00:00 PM</option>
      </select>
      <span class="selectRequiredMsg">Please select an appointment time.</span></span><br /><br />
 
 
     <span class="style3 style1">*</span>
     <span id="sprycheckbox1">
        <input type="checkbox" name="InfoSession" value="Yes" > 
        <span class="checkboxRequiredMsg">Please check box.</span></span>If you wish to schedule an appointment concerning Study Abroad, OPT, COT or tax issues, you must first attend an information session.  By checking here, you are stating that you have already attended an information session on one of the above issues and still have questions. <br /><br />
 
 
 
 
 
 
 
</fieldset>   
<div align="center"><input name="Submit" type="submit" value="Submit Registration" /></div>     
</fieldset>       
</form>
</div>
<script type="text/javascript">
<!--
var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1", "custom", {pattern:"814000000", hint:"814######", useCharacterMasking:true, validateOn:["blur"]});
var sprytextfield2 = new Spry.Widget.ValidationTextField("sprytextfield2", "none", {validateOn:["blur"]});
var sprytextfield3 = new Spry.Widget.ValidationTextField("sprytextfield3", "none", {validateOn:["blur"]});
var spryselect1 = new Spry.Widget.ValidationSelect("spryselect1", {validateOn:["blur"]});
var spryselect2 = new Spry.Widget.ValidationSelect("spryselect2",{validateOn:["blur"]});
var sprycheckbox1 = new Spry.Widget.ValidationCheckbox("sprycheckbox1", {validateOn:["blur"]});
//-->
</script>
<% else 'display a message, send an email or whatever else if the form was submitted%>
 
 
<!--This is an HTML comment - They are visible to the end user, ASP comments are not.
    This is where I would send an email if you were going to do that-->
<% end if 'end of the if Not Submitted block %>
</body>
</html>

[jhardman]

hmm. I don't know why you are getting an error, mine worked just fine. Here is my test page

By the way, please use the code formatting button (#) when you post code, it makes it much easier to read.

Jared