Link to home
Start Free TrialLog in
Avatar of gary chiam
gary chiam

asked on

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 (checkin and checkout) among other things.

Here's the code used in the search function:
 provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        'Change the following to your access database location
        dataFile = "C:\Users\fongf\Desktop\FYP_Database\FYP.accdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString

        'the query:
        Dim cmd As String = "SELECT * FROM Reservation WHERE (checkin > " & "#" & DateTimePicker1.Text & "#" & " and checkin > " & "#" & DateTimePicker2.Text & "#" & ")" & " or " & "(checkOut < " & "#" & DateTimePicker2.Text & "#" & " and checkOut < " & "#" & DateTimePicker1.Text & "#" & ")"
        Dim dr As OleDbDataReader = cmd.ExecuteReader

        If reservation = True Then
            MsgBox("Sorry, this room was booked", MsgBoxStyle.OkOnly, "Invalid Login")
            MsgBox("all is ok")
        End If

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.
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

I see 2 things.

The first one is that your query (a string) is named cmd (probably for Command) but a string does not have a ExecuteReader method. A command has. Have a look at

The second thing is about the query. With all the concatenation you are doing, it is somewhat hard to read. Also the BETWEEN operator would make it clearer (at least for me):

        Dim sql As String = "SELECT * FROM Reservation WHERE (checkin BETWEEN #" + DateTimePicker1.Value.Tostring("yyyy/MM/dd") & "# and #" & DateTimePicker2.Value.Tostring("yyyy/MM/dd") & "# ) or (checkOut BETWEEN #" & DateTimePicker2.Value.Tostring("yyyy/MM/dd") & "# and #" & DateTimePicker1.Value.Tostring("yyyy/MM/dd") & "#)"

Open in new window

Avatar of gary chiam
gary chiam


Mr.Eric , can you show me some example about how to do the availability of hotel room with room number , checkIn (datetimepicker1)  and checkOut(datetimepicker2) ?
Your help i really  appreciated.
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How's your window form's interface looks like?

can you provide the table structures with sample data?
I received a Thumbs up on that comment from the asker