Help with selecting dates BETWEEN in SQL

[scottie_uk]

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

[hellomadhu]

hey, ur sql is wrong.

it should be like what u see below

dim connectionString as string = ("SELECT comence, finish FROM bookings2 WHERE accomodation='"& accom &"' and
comence between '" & startD & "' and '" & finishD & "' and finish between '" & startD & "' and '" & finishD & "'")

[V. Lorenzo]

Hi:

Just a tip. Before coding into a CS or VB class method, test the query using the tools provided by the database managing software (e.g. SQL Query Analyser, in case of MS SQL server).

VLorz