Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

VB.net, Access & date/time picker: writing sql query to find units available for reservation between 2 given dates

Greetings:

As mentioned, I'm working with VB.NET Windows Form and an Access DataBase. A table in the database stores room numbers and the reservation dates for these rooms (resArrivalDate & resDepartureDate) among other things.

I have, in the Form, 2 date/time picker controlls used to pick a start and an end dates to search for availability of the rooms between the two dates selected. The results should be displayed on a datagrid controll but for some reason my query doesn't seem to work; it returns all of the rooms regardless of their availability.

Here's the code used in the search function:

        Dim vacancySearchSQL As String = "SELECT * FROM ProMARS.tblReservations WHERE resDepartureDate < " &    dtpSrchStartDate.Text & " OR resArrivalDate > " & dtpSrchEndDate.Text
        Dim vacancySearchDataAdapter As New OleDbDataAdapter(vacancySearchSQL, gblConnection)
        Dim vacancySearchDataSet As New DataSet
        Try
            vacancySearchDataAdapter.Fill(vacancySearchDataSet, "tblReservations")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Try
            dgVacancyResults.SetDataBinding(vacancySearchDataSet, "tblReservations")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Sorry if the question seems silly, but I haven't had much experience dealing with databases and time. Your help or pointing me to a source with good information on this would be greatly appreciated. Thank you.
0
isrxl
Asked:
isrxl
  • 2
1 Solution
 
amit_gCommented:
Try this ...

        Dim vacancySearchSQL As String = "SELECT * FROM ProMARS.tblReservations WHERE (resArrivalDate > " & "#" & dtpSrchStartDate.Text & "#" & " and resArrivalDate > " & "#" & dtpSrchEndDate.Text & "#" & ")" & " or " & "(resDepartureDate < " & "#" & dtpSrchStartDate.Text & "#" & " and resDepartureDate < " & "#" & dtpSrchEndDate.Text & "#" & ")"
0
 
isrxlAuthor Commented:
Dear amit g, thank you so much, you saved the day for me.
0
 
amit_gCommented:
Glad to be of help :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now