retrieving records by some constraints !

[tuanyong]

i doing a simple system that can book rooms for users,
i have a form which requires user to enter some information and there are 2 fields which are the start time and end time whereby users cannot select the same time or anything within the range. i tried using some of the codes but they could not work.. can someone try to help ? Thanks in advance

<%
dim strSQL
dim adoCon, rsMain, rsDetails
dim sSalesAM, sDay, sMonth, sYear, sCustomer, sEquipment, sBrand, sStartTime, sEndTime

sSalesAm = Request.Form("sales_am")
sDay = Request.Form("day")
sMonth = Request.Form("month")
sYear = Request.Form("year")
sCustomer = Request.Form("customer")
sEquipment = Request.Form("equipment")
sBrand = Request.Form("brand")
sStartTime = Request.Form("start_time")
sEndTime = Request.Form("end_time")

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("demoRoom.mdb")

stryourinsertstatement = "INSERT INTO bookingDetails (sales_am, day, month, year, customer, equipment, brand, start_time, end_time ) VALUES('" & sSalesAM & "', '" & sDay & "', '" & sMonth & "', '" & sYear & "', '" & sCustomer & "', '" & sEquipment & "', '" & sBrand & "', '" & sStartTime & "', '" & sEndTime & "')"

adoCon.Execute stryourinsertstatement

Set rsblah = Server.CreateObject("ADODB.Recordset")
rsblah.Open stryourinsertstatement, adoCon

strSQL = "SELECT * FROM bookingDetails WHERE " & _
"day = '" & sDay & "' and " & _
"month = '" & sMonth & "' and " & _
"year = '" & sYear & "'"

rsMain.Open strSQL, adoCon
if not rsMain.EOF then
Do
if (isnull(rsMain("start_time")) and isnull(rsMain("end_time"))) or (rsMain("start_time") = "" and rsMain(end_time) = "") and (CInt(rsMain("end_time")) <= CInt(sStartTime)) and (CInt(rsMain("start_time")) > CInt(sStartTime) and CInt(rsMain("start_time")) >= CInt (sEndTime)) then
response.write rsMain("brand") & "<p>"
else
if (CInt(rsMain("start_time")) >= CInt(sStartTime) and CInt(rsMain("end_time")) >= CInt (sEndTime)) and (CInt(rsMain("start_time")) <= CInt(sStartTime) and CInt(rsMain("start_time")) <= CInt (sEndTime)) and (CInt(rsMain("start_time")) >= CInt(sStartTime) and CInt(rsMain("end_time")) >= CInt (sEndTime)) then
response.write rsMain("brand") & "<p>"

end if
end if
rsMain.movenext
Loop While Not rsMain.EOF
end if

rsMain.close
adoCon.close
set rsMain = nothing
set adoCon = nothing
%>

[magus]

Can you be more specific where the line that doesn't work?

I think you forgot using double quote on the red code
Do
if (isnull(rsMain("start_time")) and isnull(rsMain("end_time"))) or (rsMain("start_time") = "" and rsMain("end_time") = "") and (CInt(rsMain("end_time")) <= CInt(sStartTime)) and (CInt(rsMain("start_time")) > CInt(sStartTime) and CInt(rsMain("start_time")) >= CInt (sEndTime)) then
response.write rsMain("brand") & "<p>"
else
if (CInt(rsMain("start_time")) >= CInt(sStartTime) and CInt(rsMain("end_time")) >= CInt (sEndTime)) and (CInt(rsMain("start_time")) <= CInt(sStartTime) and CInt(rsMain("start_time")) <= CInt (sEndTime)) and (CInt(rsMain("start_time")) >= CInt(sStartTime) and CInt(rsMain("end_time")) >= CInt (sEndTime)) then
response.write rsMain("brand") & "<p>"

end if

[tuanyong]

i have modified the codes and there is no errors found but the result is not what i desired..
even if the start_time and end_time are empty, it will display else statement instead of the if statement...
can someone help please ?? thank you ....

strSQL = "SELECT * FROM bookingDetails WHERE " & _
"day = '" & sDay & "' and " & _
"month = '" & sMonth & "' and " & _
"year = '" & sYear & "'"

rsMain.Open strSQL, adoCon

if not rsMain.EOF then
Do
if (isnull(rsMain("start_time")) and isnull(rsMain("end_time"))) or (rsMain("start_time") = "" and rsMain("end_time") = "") or (CInt(rsMain("start_time")) > CInt(sStartTime) and CInt(rsMain("start_time")) >= CInt (sEndTime)) then

response.write ("Your request has been processed.") & "<br>"
response.write ("Thank You.")
else
if (CInt(rsMain("start_time")) >= CInt(sStartTime) and CInt(rsMain("end_time")) >= CInt (sEndTime)) or (CInt(rsMain("start_time")) <= CInt(sStartTime) and CInt(rsMain("start_time")) <= CInt (sEndTime)) or (CInt(rsMain("start_time")) >= CInt(sStartTime) and CInt(rsMain("end_time")) >= CInt (sEndTime)) then

response.write rsMain("sales_am") & "<p>"
response.write rsMain("start_time")
response.write (" to ")
response.write rsMain("end_time") & "<p>"
response.write ("Sorry, your requested time slot was booked by the following person. Please liaise with him/her or book a different time. Thank you.")
end if
end if
rsMain.movenext
Loop While Not rsMain.EOF
end if

rsMain.close
adoCon.close
set rsMain = nothing
set adoCon = nothing

[TBor]

The only thing I can suggest is to ensure that start_time and end_time are actually NULL, and not just empty strings (i.e. ""). Try changing your IsNull statements for start_time and end_time to:

if rsMain("start_time")="" and rsMain("end_time")="" then...

Hope this helps,
TBor

[tuanyong]

hmmm .. tried both ways but the results seemed to be the same.
in total loss now...