Help with selecting dates BETWEEN in SQL
Hello Victor and hello all,
I have a problem with an ASP select Query I am trying to do have a look and please help. I am querying an access database to esablish what bookings are available for an accomodation.
Here is my sql command:
SELECT comence, finish FROM bookings2 WHERE (accomodation='"& accom &"') and (('" & startD &"' BETWEEN comence and finish) and ('" & finishD &"' BETWEEN comence and finish)) ;
The error I get is:
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression
Here is the whole function executing the SQL query:
(vars: startD and finishD are test vars, normally replaced with params dateFrom and dateTo) though still gives an error.
I live in the uk the format I choose to use is dd/mm/yyyy.
function checkDate (byVal dateFrom as string, byVal dateTo as string, byVal accom as string) as boolean
dim startD as date = "04/01/2003"
dim finishD as date = "04/01/2003"
dim booked as boolean = false
msgBox (dateFrom & "=DateFrom")
dim connectionString as string = ("SELECT comence, finish FROM bookings2 WHERE (accomodation='"& accom &"') and (('" & startD &"' BETWEEN comence and finish) and ('" & finishD &"' BETWEEN comence and finish)) ;")
dim dbconnection as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=booksys.mdb")
dim dbAdapter as new oleDbDataAdapter (connectionstring, dbConnection)
dim bookingDataset as new dataSet()
dBAdapter.Fill (bookingDataset,"bookings2")
'function Check date
dim bookingsView as new dataview (bookingDataset.tables("bookings2"))
Dim dataTable As DataTable = bookingDataSet.Tables(0)
'msgBox (available(accom))
msgBox (dataTable.rows.count & "=RowsCount")
if (dataTable.rows.count < available(accom)) then
booked = true
else
booked = false
end if
return booked
end function
Thanks for taking the time to read this.
scottie.uk
