ASP SQL query in a DO While Loop

[cozzy1984]

Hi I'm using ASP for a project that will allow user to search dates and find the timeslots that are available for an appointment.

I can run queries that bring back the slots that are booked when the user enters a date and can also get all the timeslots of the day (booked or unbooked) But what i really want is somehow to only output the timeslots if it doesnt appear in the booked query.


Im finding this hard to explain.

This query gets the date entered and outputs the timeslots that are booked on that day:

<%

DIm selecteddate
selecteddate = Request.Form("f_date")
Response.Write ("date = " & selecteddate & "<br/>")

DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../fpdb/Dentist_appointment_database - Antoine_Backup4_2010-01-12.mdb")

objConn.Open

DIM mySQL
mySQL = "SELECT tblappointmentdate.date, tblappointmenttime.time, tblappointment.[Appointed Dentist ID] FROM tblappointment INNER JOIN (tblappointmentdate INNER JOIN tblappointmenttime ON tblappointmentdate.appointmentref = tblappointmenttime.appointmentref) ON tblappointment.ApppointmentID = tblappointmentdate.appointmentid WHERE (((tblappointmentdate.date)=# " & selecteddate & " #))"
DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn

<table>

<% Do While not objRS.EOF
%>


<tr><td><% Response.Write objRS("date") %></td>
<td><% Response.Write objRS("time") %></td>
<td><% Response.Write objRS("Appointed Dentist ID") %></td></tr>

<% objRS.MoveNext
Loop %>


</table>


This one outputs the timeslots from a basic table with just all the times available
DIM mySQL1
mySQL1 = "SELECT * FROM tblslot"
DIM objRS1
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open mySQL1, objConn

<table>
<% Do While not objRS1.EOF

DIM timeslot
timeslot = objRS1("Timeslot")

mySQL4 = "SELECT * FROM tblslot"
DIM objRS4
Set objRS4 = Server.CreateObject("ADODB.Recordset")
objRS4.Open mySQL4, objConn
%>

<tr><td><% Response.Write objRS1("Timeslot") %></td></tr>
<% objRS1.MoveNext
Loop %>
</table>



I was hoping to add the first query into the Do While of outputting the timeslots so basically it would only return the slots if the time didnt appear in the returned query results, therefore the time would not be booked on that date.

I hope I've made at least some sense for someone to be able to shed so light on this as its driving me mad. Thanks

[cozzy1984]

Think Ive managed to somehow get it working by doing the following:

<%

DIm selecteddate
selecteddate = Request.Form("f_date")
Response.Write ("date = " & selecteddate & "<br/>")

DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../fpdb/Dentist_appointment_database - Antoine_Backup4_2010-01-12.mdb")

objConn.Open

DIM mySQL1
mySQL1 = "SELECT * FROM tblslot"
DIM objRS1
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open mySQL1, objConn

DIM mySQL
mySQL = "SELECT tblappointmentdate.date, tblappointmenttime.time, tblappointment.[Appointed Dentist ID] FROM tblappointment INNER JOIN (tblappointmentdate INNER JOIN tblappointmenttime ON tblappointmentdate.appointmentref = tblappointmenttime.appointmentref) ON tblappointment.ApppointmentID = tblappointmentdate.appointmentid WHERE (((tblappointmentdate.date)=# " & selecteddate & " #))"
DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn

%>
<table>
<% Do While not objRS1.EOF

DIM timeslot
timeslot = objRS1("Timeslot")

mySQL3 = "SELECT Count(*) AS mytotal FROM tblSlot INNER JOIN (tblappointment INNER JOIN (tblappointmentdate INNER JOIN tblappointmenttime ON tblappointmentdate.appointmentref = tblappointmenttime.appointmentref) ON tblappointment.ApppointmentID = tblappointmentdate.appointmentid) ON tblSlot.Timeslot = tblappointmenttime.time WHERE (((tblappointmentdate.date)=# " & selecteddate & " #) AND ((tblappointmenttime.time)=# " & timeslot & "#))"
DIM objRS3
Set objRS3 = Server.CreateObject("ADODB.Recordset")
objRS3.Open mySQL3, objConn

IF (objRS3("mytotal") = 0) THEN %>
<tr><td><% Response.Write objRS1("Timeslot")%></td></tr>
<% END IF %>
<% objRS1.MoveNext
Loop %>
</table>

[Old Pedant]

Do you want *only* the available timeslots to show up, or do you want a <table> that shows both available and filled slots??

Often, a table with both is easier to read, esp. if you do something like color code the slots.

But I really think you have goofed in your DB design.

I can't imagine any reason to have separate tables for appointment date and appointment time. Or even, for that matter, a separate table for actual appoinments.

In fact, I don't think you should even have the dates and times in separate *fields* (let alone tables).

That is, I'd have just three tables: DateSlots, TimeSlots and Appoinments.

And have them go something like this:

Table: DateSlots
apptDate
1/18/2010
1/19/2010
1/20/2010
1/21/2010
1/22/2010
1/25/2010
etc.

Table: TimeSlots
time :: weekdays
9:00 AM :: 235 (Monday, Tuesday, Thursday)
9:30 AM :: 235
...
1:00 PM :: 23456 (MTWTF)
1:30 PM :: 23456
...
6:00 PM :: 5 (open evenings on Thursday
6:30 PM :: 5

Table: Apppointments
when :: dentist :: patient
1/21/2010 6:00PM :: 3 :: Joe Jones

Now it's pretty easy to generate either an "all slots" calendar or an "open slots" calendar.

[Hookes]

ASP SQL query in a DO While Loop ASP ... development > ASP. ASP SQL query in a DO While Loop ... Thanked 0 Times in 0 Posts. ASP SQL query in a DO While Loop .

[Old Pedant]

Hookes is a spammer.

He is repeating nonsense in an attempt to slip in an advertisement of some sort, I think.