Display only rooms in combobox where guest status is checkin

Question

Hi to all.. I have table of guests. In that table is composed of all the list of guests checked in and out. In that table, it has the field of RoomNo and Status. Status are "checkin" and "checkout". When I'm dropdown the combobox, it has white spaces, maybe because there isn't any room in that row because the guest has been checked out. I want only to view the rooms in that combobox where Status = "checkin". The code I'd used is this:

        Dim qry1 = From dr1 As Database1DataSet.tblGuestRow In Database1DataSet.tblGuest Select dr1.RoomNo Distinct Order By RoomNo

        For Each RoomNo As String In qry1
            cboRoomNo.Items.Add(RoomNo)
        Next

I can't insert Status after WHERE clause because it only display RoomNo.

Thanks in advance...

---

iamjheyb

Answer 1

If you want to use the status field in your where clause, it needs to be in your select statement.

--
Mike

Answer 2

1. you can do the select query directly from the database:

Private table As DataTable
Private Sub GetCheckIns()
	Using conn As New SqlConnection("connString")
		Dim query As String = "SELECT RoomNo FROM Guests WHERE Status = @param ORDER BY RoomNo"
		Using da As New SqlDataAdapter(query, conn)
			da.SelectCommand.Parameters.AddWithValue("@param", "checkin")
			da.Fill(table)
		End Using
	End Using
	If table.Rows.Count > 0 Then
		MessageBox.Show("There is no room with checkin status!")
	Else
		comboBox1.DataSource = table
		comboBox1.DisplayMember = "RoomNo"
	End If
End Sub

2. you can get all the rooms into datatable, and later do the filtering, by using Select() method od DataTable class. Do you wanna to this?

---

Mitja

Answer 3

And here is your 2. point to use Select method:

Dim table As DataTable
'main table with all the rooms (not yet filtered)!
'....
Dim rooms As DataRow() = table.[Select]("RoomNo = 'checkin'")
Dim filteredTable As DataTable = table.Clone()
'to create columns:
For i As Integer = 0 To rooms.Length - 1
	filteredTable.ImportRow(rooms(i))
Next

---

Mitja

Answer 4

Great it works! I change table.rows.count < 0 and it display room numbers in the table guest where the status is checkin.. Thank you again :)

---

iamjheyb

Answer 5

Great it works! I change table.rows.count < 0 and it display room numbers in the table guest where the status is checkin.. Thank you again :)

---

iamjheyb

Better do "... <= 0" (because I dount the number will be lower then zero), or even "...== 0"

---

Mitja