DateTime Format in MS Access

Question

i developed a module and store data into an MS Access Database(mdb). The one Field uses DateTime Name(Date_of_Installation) for reference. The records stored fine. when I retrieve the Record using OleDBDataAdaptor to fill an DataSet for problem of datetime is changing format in Database to how can change the datetime format in DB. Example of the query

command.CommandText = "Select * from LicenseDetails where instr(1,"+ArgName+",'" + Value + "') and Date_of_Installation between #06/08/2009 1:31:10 PM# and #10/09/2009 2:54:57 PM#;

I am using 2005 visual studio. How can use Linq?

Answer 1

Hi, if I understand what you want to do, you want to populate a DataSet using the OleDb adapter to MS Access, then filter that DataSet with the query from above. Is that correct?

If so, then the date columns in your returned DataSet should contain DateTime types and you should be able to just query the DataSet using LINQ. Click here for the ADO.NET blog article on how to do this.

Your LINQ expression would look something like:

var query = from r in MyDataSet.Tables["LicenseDetails"].AsEnumerable()
    where r.Field<DateTime>("Date_of_Installation") >= new DateTime(6,8,2009,13,31,10) 
    && r.Field<DateTime>("Date_of_Installation") <= new DateTime(10,9,2009,14,54,57) 
    select r;

FYI instr(1,"+ArgName+",'" + Value + "') returns an int which is the position of Value in ArgName starting from position 1, or zero if it is not found. If those are given like that as string literals, it will just be returning zero, which Access is probably interpreting as a false in the Where clause and returning no results. If you want to get the position of a string inside another in C#, use string.IndexOf(), if you want to find out whether one string contains another, use string.Contains() Also note that between..and in Access is inclusive of the start/endpoints as is the LINQ above.