gary chiam
asked on
vb.net
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.OL
'Change the following to your access database location
dataFile = "C:\Users\fongf\Desktop\FY
connString = provider & dataFile
myConnection.ConnectionStr
myConnection.Open()
'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")
Else
MsgBox("all is ok")
End If
myConnection.Close()
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.
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.OL
'Change the following to your access database location
dataFile = "C:\Users\fongf\Desktop\FY
connString = provider & dataFile
myConnection.ConnectionStr
myConnection.Open()
'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")
Else
MsgBox("all is ok")
End If
myConnection.Close()
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.
ASKER
ASKER
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 i really appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How's your window form's interface looks like?
can you provide the table structures with sample data?
can you provide the table structures with sample data?
I received a Thumbs up on that comment from the asker
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):
Open in new window