rbkinsey
Posts: 42
|
| Posted: 05/28/2008, 3:55 PM |
|
Have Property and Reservation tables. Have selection form above property grid which allows Date In and Date Out. I'm using the SQL Select statement to count records with conflicting reservation records and then if count is zero the property is available for that period. In the Grid Query I've defined Property table and during each Show Row I'm attempting to spin through the Reservation table counting conflicting records. I'm using the following SQL:
SELECT COUNT(*) AS intTotal FROM Reservation where Reservation.[Property #] = Housing.[Record ID#] AND Reservation.[Date Out] >= #{s_Date_In}# OR Reservation.[Property #] = Housing.[Record ID#] AND Reservation.[Date In] <= #{s_Date_In}#
My problem: during Show Row Housing.[Record ID#] is undefined because it's used during the Grid building, and #{s_Date_In}# & #{s_Date_Out}# are undefined because they are entered in the select form, and finally not sure how to stop the grid line from displaying (count > 0 conflict), we everything is resolved?
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 05/29/2008, 5:08 AM |
|
@R
(pls use ctrl+lf in posting and [ code ] [ /code ], makes it more readable).
Could you post the Grid's SQL here...
What I think you need is a subselect, but in order to give a good answer I need to see the main SQL.
pointers:
Subselect
Expression in Where
BTW why use Housing recordID, it does have a unique ID key field, does it?
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/29/2008, 5:15 AM |
|
SELECT Housing.[ID #], ......some other fields ....
FROM Housing
WHERE
( SELECT COUNT(*) FROM Reservation
where Reservation.[Property #] = Housing.[ ID#]
AND ( Reservation.[Date Out] >= #{s_Date_In}#
AND Reservation.[Date In] <= #{s_Date_In}# )
) = 0
Just an untested idea
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
rbkinsey
Posts: 42
|
| Posted: 05/29/2008, 9:25 AM |
|
I liked your idea since it seems to solve all my problems. The fields should be defined since both files are refereced in the sql, and by doing the count as another WHERE clause it will not display a property row unless no conflicting reservation. So I changed things around a bit. I now am trying to do everything within the grid sql. I'm using the following:
SELECT *
FROM Housing,
Reservation
WHERE Housing.Address LIKE '%{s_Address}%'
AND Housing.[Housing Type] LIKE '%{s_Housing_Type}%'
AND Housing.[Size] >= {s_Size}
AND Housing.[Monthly Rent] <= '{s_Monthly_Rent}'
AND Housing.Bedrooms >= {s_Bedrooms}
AND Housing.Baths >= {s_Baths}
AND Housing.[Non-Smoking] = {s_Non_Smoking}
AND ( (SELECT COUNT(*)
FROM Reservation
where [Property #] = Housing.[Record ID#]
AND [Date Out] >= #{s_Date_In}#
AND [Date In] <= #{s_Date_Out}# ) = 0 )
It doesn't complain about field names anymore, but I am still getting an error. Now it's saying there is a "Syntax error in date in query expression". I can't see it.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/29/2008, 11:28 AM |
|
SELECT *
FROM Housing
WHERE ( (SELECT COUNT(*) from reservation where Reservation.Property=Housing.Id)=0)
;
Do you confirm that this snippet returns all houses that have NO reservations?
You have to notice that I not have a Housing.Record ID but have a primary index field Id in table Housing, depending on the record ID, is extremely dangerous!
Please confirm this one first, it proves you do not need Reservation in the FROM.
(Access 2007, purpose built example)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/29/2008, 11:32 AM |
|
SELECT *
FROM Housing
WHERE
(
Housing.Address LIKE '%{s_Address}%' AND
Housing.[Housing Type] LIKE '%{s_Housing_Type}%' AND
Housing.[Size] >= {s_Size} AND
Housing.[Monthly Rent] <= '{s_Monthly_Rent}' AND
Housing.Bedrooms >= {s_Bedrooms} AND
Housing.Baths >= {s_Baths} AND
Housing.[Non-Smoking] = {s_Non_Smoking
)
AND
(
(SELECT COUNT(*) from reservation where Reservation.Property=Housing.Id)=0
)
;
Next evolution, should do all the search parms and return houses with NO reservations.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/29/2008, 11:37 AM |
|
SELECT *
FROM Housing
WHERE
(
Housing.Address LIKE '%{s_Address}%' AND
Housing.[Housing Type] LIKE '%{s_Housing_Type}%' AND
Housing.[Size] >= {s_Size} AND
Housing.[Monthly Rent] <= '{s_Monthly_Rent}' AND
Housing.Bedrooms >= {s_Bedrooms} AND
Housing.Baths >= {s_Baths} AND
Housing.[Non-Smoking] = {s_Non_Smoking
)
AND
(
(SELECT COUNT(*) from reservation
where Reservation.Property=Housing.Id
AND [Date Out] >= #{s_Date_In}# AND [Date In] <= #{s_Date_Out}# )=0
)
;
Now I dont like the # signs around the search date fields, are you sure CCS needs this?
Let me know how you proceed......
In MySQL we would have been done .
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
rbkinsey
Posts: 42
|
| Posted: 05/29/2008, 12:08 PM |
|
Tried without the dates and it showed only those properties without any reservations like I wanted it to. Added the dates in and it error msg about date. Replaced #{s_Date_In}# with 05/01/2008 and worked. So now I know it's the selection dates fields that I'm not describing correctly. I've been using another screen which selected date in and out from the reservation file and that's where I got the #{s_Date_In}# format. Is there another way of describing a select field like SELECT.s_Date_In?
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/29/2008, 12:14 PM |
|
Just lose the #
I think CCS3and further handle Access correctly
Just a guess.
At least you're there right?
Post the solution if you have it and change title to [Resolved]
Going for a drink, read ya tamarraw
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
rbkinsey
Posts: 42
|
| Posted: 05/29/2008, 8:55 PM |
|
Tried taking out the # and it errored. I've tried everything and s_Date_In by itself gives me no errors, however when I click on show data button. It tells me I'm missing 1 or more parameters and won't compile. So I've tried the following:
First loaded the following for grid sql:
(SELECT COUNT(*) FROM Reservation
where [Property #] = Housing.[Record ID#]) = 0
Results all records without resv rcds were displayed which was what I expected.
Then loaded the following for grid sql:
(SELECT COUNT(*) FROM Reservation
where [Property #] = Housing.[Record ID#]
AND [Date Out] >= 04/01/2008
AND Reservation.[Date In] <= 04/30/2008) = 0
Results all records without resv rcds and one with resv in 05/06/08-05/26/08 which should have displayed. Showing it worked.
Loaded grid sql:
(SELECT COUNT(*) FROM Reservation
where [Property #] = Housing.[Record ID#]
AND [Date Out] >= 06/01/2008
AND Reservation.[Date In] <= 06/30/2008) = 0
Results were the same as above which was good, however I also loaded Reservation.[Date Out] as control source for an extra grid field to see if the record with resv rcds would contain the last records Date Out and it was blank, making me wonder if it's working at all.
When I load grid sql:
(SELECT COUNT(*) FROM Reservation
where [Property #] = Housing.[Record ID#]
AND [Date Out] >= 05/06/2008
AND Reservation.[Date In] <= 05/09/2008) = 0
Results show all properties are available, as if it's not reading in the Reservation data or not understanding the dates I'm entering.
I can't get the prompts to be excepted, perhaps for the same reason I can't get the dates to work correctly.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/30/2008, 2:02 AM |
|
[studying..weekend...]
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
rbkinsey
Posts: 42
|
| Posted: 06/04/2008, 7:27 PM |
|
Have been able to do it in ASP as follows:
HTML Part
<html>
<body>
<form method="Get" action="Avail.asp">
Date In: <input type ="date" name ="datein"/> " "
Date Out: <input type ="date" name ="dateout"/><br/>
<input type="Submit" value="Check for availibility"/>
</form>
</body>
</html>
ASP Part
<%
Dim date_in, date_out, intTotal
If IsDate(Request.QueryString("datein")) Then
date_in = CDate(Request.QueryString("datein"))
Else
If IsDate(Request.QueryString("day") & "/" & Request.QueryString("month") & "/" &
Request.QueryString("year")) Then
date_in = CDate(Request.QueryString("day") & "/" &
Request.QueryString("month") & "/" & Request.QueryString("year"))
Else
date_in = Date()
End If
End If
If IsDate(Request.QueryString("dateout")) Then
date_out = CDate(Request.QueryString("dateout"))
Else
If IsDate(Request.QueryString("day") & "/" & Request.QueryString("month") & "/" &
Request.QueryString("year")) Then
date_out = CDate(Request.QueryString("day") & "/" &
Request.QueryString("month") & "/" & Request.QueryString("year"))
Else
date_out = Date()
End If
End If
response.write("Avail Period - " & date_in & " thru " & date_out & " <br/><br/><br/>")
' define the database connection settings
strconnect="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
server.mappath("RentalMgt.mdb")
' open a connection object and actually connect to the database
set dbconn=server.createobject("adodb.connection")
dbconn.open strconnect
call db_avail(dbconn)
' close the db connection
dbconn.close
set dbconn=Nothing
%>
<%
function db_avail(dbconnection)
response.write "Properties Available <br/><br/>"
' open recordset connection
strSQL="SELECT * FROM Housing"
set xrs=server.createobject("adodb.recordset")
xrs.open strSQL, dbconnection
do while not xrs.eof
dim intTotal
intTotal = 0
strSQLa="SELECT * FROM Reservation WHERE [Property #] = " & xrs("Record
ID#")
set xrsa=server.createobject("adodb.recordset")
xrsa.open strSQLa, dbconnection
do while not xrsa.eof and intTotal=0
If xrsa("Date Out") >= date_in AND xrsa("Date In") <= date_out then
intTotal=intTotal+1
else
end if
xrsa.movenext
loop
If intTotal=0 then
response.write "Rcd#: " & xrs("Record ID#") & " " & xrs("Address") & " <br/>"
end if
xrs.movenext
loop
end function
%>
Works great, now to get it into CSS. I've tried many ways to do it in grid sql as discussed above, but seems the s_Date_In and s_Date_Out are not being defined correctly. Perhaps should be URLparameters and are not. Any ideas?
Regards,
Bob
|
|
|
|
|
