Retriving data from Ms access table in between two dates by using C#.net

Question

Dear all Here what i actually need is i want to display the data in between two dates in MS Access table by using OLEDB connection through datagridview.here table name in tokendetails and column name in software_1 createddate.Please help me to write code for this problem. i wrote code as bellow. string da.Fill(ds dataGridView1.DataSource = ds.Tables[ st = select * from tokendetails where software_1createddate between  + DateTime .Parse( DateTime .Today.AddDays(-10ToShortDateString()) +  and  + DateTime .Parse( DateTime .Today.AddDays(-1).ToShortDateString()) +  ; MessageBox .Show(st); OleDbDataAdapter da = new OleDbDataAdapter (st con); DataSet ds = new DataSet (); tokendetails ); tokendetails ]; In between two dates data available in Table. but i got error as Syntax error (missing operator) in query expression 'software_1createddate between 01/07/2011 00:00:00 and 16/07/2011 00:00:00'. for u r referece i send my application and database file in attachment.pls check at u r end and please suggest me the code. thanks in advance.

Satish Bhat · Answer

Try the below query SELECT * FROM tokendetails WHERE software_1createddate BETWEEN # + DateTime.Today.AddDays(-10).ToShortDateString() + # AND # + DateTime.Today.AddDays(-1).ToShortDateString() + # Since MS Access can't do auto conversion from char/text into date or date time in query use # e.g. for 8/5/2011 00:00:00 PM use #8/5/2011 00:00:00# Also check the date format in MS Access. i.e. DD/MM/YYYY HH:NN:SS or MM/DD/YYYY HH:NN:SS or YYYY-MM-DD HH:NN:SS etc. and pass the parameter accordingly.

Jiteendra Sampathirao · Answer

use this query: select * from tokendetails where software_1createddate between '  + DateTime.Parse(DateTime.Today.AddDays(-10ToShortDateString()) +  ' and '  + DateTime.Parse(DateTime.Today.AddDays(-1).ToShortDateString()) +  ' you should use single quotes before and after double quotes as i mentioned.....

jose alexander Molano · Answer

muchas gracias... it was solution to my problem!!! SELECT * FROM tokendetails WHERE software_1createddate BETWEEN # + DateTime.Today.AddDays(-10).ToShortDateString() + # AND # + DateTime.Today.AddDays(-1).ToShortDateString() + #

Satish Bhat · Answer

Hi Vishnu Checked your code The problem is with the Culture either change your computer Regional Settings or change the culture of your application. To change the Regional Settings ref: http://msdn.microsoft.com/en-us/goglobal/bb688124.aspx To change the application culture use the following code in form initialization code System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo(en-US); In this case you will have to format the display of the date field to DD/MM/YYYY. Open your Columns property of DataGrid go to DefaultCellStyle and put dd/MM/yyyy in Format field. Also change the datatype of software_1createddate & software_2createddate in db1DataSet.xsd to System.DateTime from System.String

vishnuvardhan diyyala · Answer

Dear. I checked like that also.but i didn't got correct output.now i send you my application.if u r free pls check ones and suggest me correct code.that wil contain database file and application. thanks in advance.

Jiteendra Sampathirao · Answer

run a simple query and see if there is an error

vishnuvardhan diyyala · Answer

Dear jitendra i checked like dat also.that time i got error as Data type mismatch in criteria expression.Please check onses at u end for this code.in table those column as data/time formate.i need as date time formate. please check and suggest.Thanks in advance.