Hi,

I have a calendar w/data stored in SQL Server. People can reserve multiple dates for an event. Before a reservation is confirmed, I want to be able to see if the dates have already been reserved.

I first query the dates for the unconfirmed reservation. I then want to query the database for all other records matching those dates. However, when I reference the dates from my first query, my output only shows one of the dates. For example:

<cfquery name = "newreserve" datasource = "mydata"
select reservedate
from mytable
where url.id = reserveid
</cfquery>
<cfoutput query = "newreserve">
<cfquery name = "duplicates" datasource = "mydata">
select *
from mytable
where reservedate = #newreserve.reservedate#
</cfquery>
</cfoutput>
<cfoutput query = "duplicates">
#reservedate#
</cfoutput>

I know I'm doing something very wrong, but don't know what it is.

Thank you!

From what I can gleam from your sql, I am not even sure that you know that you want this code to work. The name reserveid screams primary key to me, and you shouldn't have to loop over the results of a query that should only give you one record in the first place. I will just tell you the problem with your current code since your db schema seems a little wacky. Your problem is that you seem to have the code that handles each iteration of the loop over "newreserve" AFTER your loop ends. Hence your code outputting reservedate only has the data for the last iteration of the first loop. I believe this is more what you are looking for.


PS: Take out the Select * from your cfqueries. Makes the footprint of this code bigger.

I am not sure of the logic of what you are trying to do. You indicate below there is an url.id variable, which indicates you have inserted the date. If this is the case, it should be encased as and should come after "reserveid" in your query - like where

That said, this to me indicates you have already written the request into the database. As only one date can be associated with on record (I assume), then you only have one date associated with the first query. I for one wouldn't write the data into the database until I had already checked for duplicates.

Assuming you have written the data into the database for some reason, now you can check for this same date in other records - however, from what you have written, you will always at the least find the record you just inserted (when in fact there may be no duplicates). To avoid this, I would try the following for the second query (I can't remember if sql server is , so you would have to check the sql equivalent for "not equals"):


Finally, with sql server, a date value can be inserted as a timestamp? So, the time aspect of the record may make the date aspect of the record unique. You really should be searching for and inserting date values using the function, and when you insert the record, make the timestamp aspect set to 00:00:00. Ideally, you should use a datatype in the database that is date only.

Hope this helps.

D