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.
DatabasesVisual Basic.NET

Avatar of undefined
Last Comment
Éric Moreau

8/22/2022 - Mon
Éric Moreau

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 https://msdn.microsoft.com/en-us/library/979byfca(v=vs.110).aspx

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

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.
Your help has saved me hundreds of hours of internet surfing.
Éric Moreau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ryan Chong

How's your window form's interface looks like?

can you provide the table structures with sample data?
Éric Moreau

I received a Thumbs up on that comment from the asker