Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, so i have a table in my database called open_hours, containing the open time and closing time (saved as type time) of a venue for each day of the week. Im making a booking system so i want to be able to check if the booking times entered by the user are within the open hours.
VB
Try
           mysqlconn.Open()
           query = "SELECT* FROM open_hours WHERE day='" & Datetimepicker1.Value.DayOfWeek.ToString & "'"
           MsgBox(query)
           command = New MySqlCommand(query, mysqlconn)
           reader = command.ExecuteReader
           While reader.Read()
               If start_time.value < reader.GetDateTime("open_time") Or end_time.value > reader.GetDateTime("close_time") Then
                   errors.Enqueue("The booking must be within the opening times")
               End If
           End While
           mysqlconn.Close()
       Catch ex As Exception
           MsgBox(ex.Message)
       Finally
           mysqlconn.Dispose()
       End Try


So this is what i have so far however, when i click the button to check the bookings availabliity, i get an error message say "Unable to convert MYSQL date/time value to system.datetime".
Now, i've googled this error message and the general solution if to add Convert Zero Datetime=True to the connection string.

VB
mysqlconn.ConnectionString = "Server=localhost;userid=root;password=root;database=comp4;Convert Zero Datetime=True"


When i do this, i do get a value for the open_time, but its just 00:00:00.
None of my open times are 00:00:00 so i dont understand whats going on.
Please help, this is driving me crazy!!
Posted
Updated 7-Mar-15 5:15am
v2

1 solution

Maybe you should consider that what is returned is more specific of a TimeSpan rather of a DateTime?

What if you use:
VB
If start_time.value < reader.GetTimeSpan("open_time") Or end_time.value > reader.GetTimeSpan("close_time") Then
   errors.Enqueue("The booking must be within the opening times")
End If

?

Hope this helps. Good luck :)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900