Need some help with an issue I'm having. This is the code:
Private Sub dtpStartDate_ValueChanged(sender As Object, e As EventArgs) Handles dtpStartDate.ValueChanged startDateChanged = 1 If endDateChanged = 1 Then cbLocation.Enabled = True cbLocation.Items.Clear() cbLocation.Items.Add(New ListViewItem("")) Dim unbookedLocationsSQL As String = "SELECT locationID FROM Locations WHERE NOT EXISTS (Select LocationID FROM Bookings WHERE @startDate <= bookingEndDate AND bookingStartDate <= @endDate)" Dim unbookedLocationsCommand = New OleDbCommand(unbookedLocationsSQL, globalVariables.objConnection) Dim unbookedLocationsAdapter As New OleDbDataAdapter(unbookedLocationsSQL, globalVariables.objConnection) Dim unbookedLocationsDataSet As New DataSet unbookedLocationsCommand.Parameters.AddWithValue("startDate", dtpStartDate.Value) unbookedLocationsCommand.Parameters.AddWithValue("endDate", dtpEndDate.Value) unbookedLocationsAdapter.Fill(unbookedLocationsDataSet, "Locations") With cbLocation .DataSource = unbookedLocationsDataSet.Tables("Locations") .DisplayMember = "locationID" .ValueMember = "locationID" End With End If End Sub
First things first, if you change the sql statement to "SELECT * from locations" the combo box just displayed all of locations.
What I am trying to achieve is this; when someone changes both date time pickers, the combo box is enabled and populated with a list of locations that aren't booked between those two dates, which is determined by the bookings table. I know the SQL statement is wrong. I've tried various combinations of things and tried isolating bit and pieces but I can't get any subquery to do what I want.
Any help would be appreciated.