It is not working because your condition is never false. You're asking sdr for the rows. And it always has at least one row.
What you need to do is one of:
1. Add RoomNotAvailable as a field in the first query and set it to empty. Change if exists to WHERE clause and then do union with the empty row. (see SQL below) In the code, hold a variable with a check if there was at least one available room and ignore empty row if there was.
2. check if the sdr contains the field RoomNotAvailable instead of checking it hasRows.
For the first solution, SQL would go like this:
SELECT R.roomId, r.RoomTitle, RoomName, RoomPrice, RoomDescription, RoomNotAvailable = NULL
FROM[dbo].[Room] R
INNER JOIN Reservation Rv on R.RoomId = Rv.RoomId
WHERE
@Checkin between Rv.checkin and Rv.checkout
aND Rv.IsReserved = 1
and Rv.IsCanceled = 1
UNION ALL
select RoomId = 0, RoomTitle = NULL, RoomPrice = NULL, RoomDescription = NULL, RoomNotAvailable = 'Room is already Reserved'
ORDER BY RoomNotAvailable
Suggestion: don't use * in selects, it can cause all sorts of trouble as number of tables in joins increases and you're returning data (increasing traffic) that you don't need.
If this helps please take time to accept the solution. Thank you.