gary chiam
gary chiam used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

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


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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior .Net Consultant
Top Expert 2016
without having a database table structure, I would say that the query here above is good.

Your second link is showing you to use an OleDbCommand exactly like I told you.
Ryan ChongSoftware Team Lead

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

can you provide the table structures with sample data?
Éric MoreauSenior .Net Consultant
Top Expert 2016

I received a Thumbs up on that comment from the asker

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial