I have 2 tables
Table 1 is the master table where i am storing the Room Status, Room Number and housekeeping status.
select room_no,room_status,housekeeping_status from room_master
By running the above query i will get the the below result.
room_no room_status housekeeping_status
801 vacant Ready
802 Vacant Dirty
803 Occupied Dirty
804 vacant Ready
805 Occupied Clean
By using the above results i am writing the below code to assign the rooms to a list
adding the image,text and room no based on the status
strsql = "select room_no,room_status,housekeeping_status from room_master"
dt1 = ConnectionModule.HMSgetdataset(strsql)
Dim lst1() As ListViewItem
ReDim Preserve lst1(dt1.Rows.Count - 1)
For i = 0 To dt1.Rows.Count - 1
If dt1.Rows(i)(1) = "Vacant" And dt1.Rows(i)(2) = "Ready" Then
lst1(i) = New ListViewItem
lst1(i).Text = dt1.Rows(i)(0)
lst1(i).ImageIndex = 2
lst1(i).BackColor = Color.SpringGreen
lbl_vacantready.Text = CDbl(lbl_vacantready.Text) + 1
ElseIf dt1.Rows(i)(1) = "Vacant" And dt1.Rows(i)(2) <> "Ready" And dt1.Rows(i)(2) <> "OutInv" And dt1.Rows(i)(2) <> "OutOdr" Then
lst1(i) = New ListViewItem
lst1(i).Text = dt1.Rows(i)(0)
lst1(i).ImageIndex = 0
lst1(i).BackColor = Color.CadetBlue
lbl_vacantnotready.Text = CDbl(lbl_vacantnotready.Text) + 1
ElseIf dt1.Rows(i)(1) = "Occupied" Then
lst1(i) = New ListViewItem
lst1(i).Text = dt1.Rows(i)(0)
lst1(i).ImageIndex = 1
lst1(i).BackColor = Color.Aqua
lbl_occupied.Text = CDbl(lbl_occupied.Text) + 1
ElseIf dt1.Rows(i)(2) = "OutInv" Then
lst1(i) = New ListViewItem
lst1(i).Text = dt1.Rows(i)(0)
lst1(i).ImageIndex = 4
lst1(i).BackColor = Color.OrangeRed
lbl_OOI.Text = CDbl(lbl_OOI.Text) + 1
ElseIf dt1.Rows(i)(2) = "OutOdr" Then
lst1(i) = New ListViewItem
lst1(i).Text = dt1.Rows(i)(0)
lst1(i).ImageIndex = 5
lst1(i).BackColor = Color.Orange
lbl_OOR.Text = CDbl(lbl_OOR.Text) + 1
End If
Next
What I have tried:
Now the requirement is when there is a reservation with any room then i need to append
"(R)" beside the room no.
For checking the reservation i am using the below query.
strsql="select roomno from guestreservation where arrivaldate=cast('" & Format(currdate(), "dd-MMM-yyyy") & "' as smalldatetime)"
it will give the room numbers which has the reservation as today date.
room_no
801
802
so for room numbers 801 and 802 need to append the "(R)" beside the room number.
i have written the below line to append the "(R)". But it has to be disaplyed only when there is reservation with that room, for the remaining rooms the room number will be displayed as the same.
lst1(i).Text = dt1.Rows(i)(0).Tostring() + "R"
so i am assuming to append the "(R)" in the loop only.
"how can i join the 2 queries to get the room number as reserved"