Problem In get dates between 2 date (vb.net | OLE)

Question

Hello ,,,

I have an data base (.mdb) and it has a column with dates (dd/mm/yy) , some one give me a code to get all the dates in database between 2 dates , the code was :

Select * from table where date between 'StartDate' and 'EndDate'

but after I use the code , an error occurs told me that the types of data is not the same

System.Data.OleDb.OleDbException was
 unhandled   ErrorCode=-2147217913  
 Message="عدم تطابق نوع البيانات في
 تعبير المعايير."   Source="Microsoft
 JET Database Engine"

although I convert the data type in the column of dates in database to (Date \ time) , and use OLE object to connect to data base

what is wrong , and what I have to do ?

Answer 1

You usually need to surround date/time types with # when working with Access, like so

#22/01/2009#

Answer 2

Instead of using dynamic SQL, instead use the Access Database Engine's CREATE PROCEDURE SQL DDL syntax to create a persisted object whose parameters have parameters strongly-typed as DATETIME with the NULL value as default. Handle the NULL value to use the DATETIME column's value instead e.g.

CREATE PROCEDURE GetStuff
(
 arg_start_date DATETIME = NULL, 
 arg_end_date DATETIME = NULL 
)
AS
SELECT lastvstart
  FROM tb 
 WHERE lastvstart 
          BETWEEN IIF(arg_start_date IS NULL, lastvstart, arg_start_date)
          AND IIF(arg_end_date IS NULL, lastvstart, arg_end_date);